InnoDB cannot find a row - possible index corruption?

looking for Your data - Your columns is VARCHAR, but not date, this is make query non working with any non readable character in data

by run UPDATES:

UPDATE mytable SET Start='2018-02-04 11:31' where id=1;
UPDATE mytable SET Start='2018-02-04 11:30' where id=1;

You are fix this situation

Proper way for work with dates - have datetime type for date time columns

in this case Your data would look like:

+----+---------------------+---------------------+
| Id |      Start          |       End           |
+----+---------------------+---------------------+
|  1 | 2018-02-04 11:30:00 | 2018-02-05 12:00:00 |
|  2 | 2018-02-04 15:40:00 | 2018-02-05 08:00:00 |
|  3 | 2018-02-05 10:00:00 | 2018-02-06 09:00:00 |


update column without auto timestamp update

When you do your update, set the lastUpdate column equal to itself, that way it won't default in the current timestamp:

 update productsStock set productName="HP PRINTER 20000", lastUpdate = lastUpdate where product_reference="001";


According to this Microsoft Docs page, nesting of IF statements is dependent on available memory.

On my test workstation, I was able to write and execute this, which is 189 levels deep:

declare @teste int;
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
print 1

189 levels of nesting seems pretty bizarre to me, but then this is a bizarro world, lately.


You can kind of do this from MySQL 5.6 and onwards using information_schema.  I have an example of finding the ideal buffer pool size on my blog here: http://www.tocker.ca/2013/05/31/estimating-mysqls-working-set-with-information_schema.html

The caveat is that you may need to either restart or lower the buffer pool size first.  Inactive pages will just stay in memory if there is no need to make free space - which could skew your result on a server that's been running for a while, yet has plenty of memory.


Can't login as root and can't change root password

Remove skip-name-resolve in your my.ini, which prevents localhost to be resolved, restart mysql, login and change your root account to 'root'@'127.0.0.1' or 'root'@'::1' then turn on skip-name-resolve again and restart.
See MySQL --skip-name-resolve
Also have a look here How can I run with root@localhost privileges in MySQL?


MySQL JOIN query takes too much time to get the result

Your query looks like a different approach will be better than simply using more and more LEFT JOINs.

Instead, move to using subqueries in the SELECT clause for most of these "added tables". You shouldn't need the DISTINCT anymore, either, if that was included just to avoid the explosion of rows from so many JOINs.

SELECT
c.`user_name`,
e.`event_name`,
e.`event_code`,
e.`event_id`,
(
SELECT COUNT(*)
FROM `event_participant` ep
WHERE ep.event_id = e.event_id
) as participants,
(
SELECT COUNT(*)
FROM `program_material` pm
WHERE pm.event_id = e.event_id
) as material_count,
(
SELECT COUNT(*)
FROM `event_news` ev
WHERE ev.event_id = e.event_id
) as news_count ,
(
SELECT COUNT(*)
FROM `socialwall` sw
WHERE sw.event_id = e.event_id
) as socail_wall_count,
(
SELECT COUNT(*)
FROM `event_speaker` es
WHERE es.event_id = e.event_id
) as speaker_count,
(
SELECT COUNT(*)
FROM `event_program` epr
WHERE epr.event_id = e.event_id
)  as program_count
FROM `event` e
LEFT JOIN `event_customer` ec ON e.`event_id` = ec.`event_id`
LEFT JOIN `customer` c ON ec.`customer_id` = c.`user_id`
GROUP BY e.`event_id`
ORDER BY participants DESC
LIMIT 0,10

Give this a try and see if it is returning the correct results (I had to make some assumptions based on the limited information I have available), and is performing well enough or not.


mysql: Is mysqladmin -p create equivalent to create database?

Yes and with the  -p argument it should prompt you for a password to authenticate to perform the correlated command operation.

  mysqladmin
  supports the following commands. Some of the commands take an argument
  following the command name.

  create db_name

  Create a new database named db_name.

  --password[=password], -p[password]

  The password to use when connecting to the server. If you use the
  short option form (-p), you cannot have a space between the option
  and the password. If you omit the password value following the
  --password or -p option on the command line,
  mysqladmin
  prompts for one.

  source


Why would a SELECT INTO cause an arithmetic overflow?

It sounds to me like you have an implicit conversion to smalldatetime, probably in a computed column. If you look at the Estimated Execution Plan, you'll probably see Compute Scalar operations in there. Hit F4 to see the properties, and you should be able to see the calculations that are being done, including any implicit conversions that are being used.


How mysql database performance can impact by different types of disk[hard disks]

Short answer: It's 2020, the case for spinning rust is super-weak.

Long answer: It depends on how much memory you have and what your read:write ratio is.

If the workload is mostly reads, and you have enough buffer pool to cache all of the even remotely hot data, disk speed won't matter as much because once the buffer pool is warmed up, not much data will be coming off the disk.

For writes, every transaction is going to require at a minimum 2-4 disk IOPS with InnoDB.

1) Once into the transaction log

2) Once into the target tablespace

3) Once again into the transaction log (unless innodb_doublewrite=0)

4) Once into the binlog (if log_bin and sync_logbin=1)

A typical 7200rpm disk can sustain about 120 IOPS (that's how many times per second the required sector comes around to meet the heads).

So if you don't need to sustain more than about 30 transactions per second (note: transactions per second is not the same as queries per second), you may be able to scrape by with a spinning disk.

There are various things you can do to speed this up, if you can afford to lose transactions in a system crash scenario, e.g.:

innodb_doublewrite=0
innodb_flush_logs_at_trx_commit=0

or even better, if you are on ZFS, zfs set sync=disabled on the relevant volume, and enjoy the fact that on ZFS innodb_doublewrite=0 is safe and there is no need to set innodb_flush_logs_at_trx_commit=0 if you set sync=disabled.

That will make the workload a lot more sequential and thus the IOPS limitation will be less severe. But really, if you have to resort to this sort of thing to squeeze more performance out of the storage, you really should be buying an SSD instead.

NVMe is usually 4x faster on throughput and much faster on latency than SATA SSD.

So you will have to decide in the context of your workload what your performance requirements are and what you can afford, and plot a compromise through those parameters.


Change oracle port from 1523 to 1521 when something else is on 1521

Before even posting I found the answer to my question.

As it turns out there should only be one of those processes running.

The first thing I did was try to find out more about this process on port 1521. I took the PID and searched for it.

ps -ef | grep 119935
oracle   119935      1  0 May11 ?        00:00:04 {Old install path}/bin/tnslsnr LISTENER -inherit

I noticed that the path shown here is to a directory that doesn't exist. I had previously installed oracle to this location and then uninstalled and reinstalled (I had to do this twice...). I had even "rm -rf"ed the whole directory. So this process must be from the old install, still running in memory. There's probably a bunch of stuff running that shouldn't be from my hacking so I rebooted the machine.

Now processes are gone:

$netstat -tulpn | grep tnslsnr
nothing here

With port 1521 free I can change the listeners port


Just a few possibilities with not 100% accurate explanation.

Let's say you have 100+ GB SGA (with 100 GB buffer cache) and 20 GB PGA.

Operations performing a sort (queries with ORDER BY) or a hash (HASH JOIN) use PGA memory to do that.
A single session can typically use up to 5% percent of target PGA for these operations, if it requires more memory, then it switches to the temp tablespace, where it continues its work, but instead of memory, it performs its on disk.
So if your query needs to sort some bigger but not-even-close-to-the-available-memory amount of data (1 GB+ data), it will sort it on disk.
Big tables (=segments) are not cached when read with a full scan. By default, a big table (=segment) is typically a table (=segment) bigger than 2% of the buffer cache.
This behaviour can be controlled by setting the "_small_table_threshold" parameter, that specifies the threshold in database blocks for deciding if a segment should be cached.
Similar example: if you have a table that is over 2 GB, and your query reads it with a full scan, the database will not even try to cache it.
If you execute this query multiple times, the database will read the table from disk over and over again.
LOB columns are stored in seperate segments. LOB segments are not cached by default (if not specified explicitly at the time of creation or later), so they will be read from disk over and over again.
The caching can be enabled as:

alter table table1 modify lob (lob_column1) (cache); or (cache reads) to cache reads only.

+1. You have a bigger SGA configured than your database. (Hopefully you configured it at instance level as well).
You can force full database caching as:

ALTER DATABASE FORCE FULL DATABASE CACHING;

+2. In-Memory is an extra cost option for the database that needs to be licensed. Use it only if you have the appropriate license for it.


SQL Server splits A  B into A  B, yielding strange results if B is non-deterministic

Is SQL Server allowed to evaluate A <> B as A < B OR A > B, even if one of the expressions is non-deterministic?

This is a somewhat controversial point, and the answer is a qualified "yes".

The best discussion I am aware of was given in answer to Itzik Ben-Gan's Connect bug report Bug with NEWID and Table Expressions, which was closed as won't fix. Connect has since been retired, so the link there is to a web archive. Sadly, a lot of useful material was lost (or made harder to find) by the demise of Connect. Anyway, the most useful quotes from Jim Hogg of Microsoft there are:

  This hits to the very heart of the issue - is optimization allowed to change a program's semantics? Ie: if a program yields certain answers, but runs slowly, is it legitimate for a Query Optimizer make that program run faster, yet also change the results given?

  Before shouting "NO!" (my own personal inclination too :-), consider: the good news is that, in 99% of cases, the answers ARE the same. So Query Optimization is a clear win. The bad news is that, if the query contains side-effecting code, then different plans CAN indeed yield different results. And NEWID() is one such side-effecting (non-deterministic) 'function' that exposes the difference. [Actually, if you experiment, you can devise others - for example, short-circuit evaluation of AND clauses: make the second clause throw an arithmetic divide-by-zero - different optimizations may execute that second clause BEFORE the first clause] This reflects Craig's explanation, elsewhere in this thread, that SqlServer does not guarantee when scalar operators are executed.

  So, we have a choice: if we want to guarantee a certain behavior in the presence of non-deterministic (side-effecting) code - so that results of JOINs, for example, follow the semantics of a nested-loop execution - then we can use appropriate OPTIONs to force that behavior - as UC points out. But the resulting code will run slow - that's the cost of, in effect, hobbling the Query Optimizer.

  All that said, we are moving the Query Optimizer in the direction of "as expected" behavior for NEWID() - trading off performance for "results as expected".

One example of the changing of behaviour in this regard over time is NULLIF works incorrectly with non-deterministic functions such as RAND(). There are also other similar cases using e.g. COALESCE with a subquery that can produce unexpected results, and which are also being addressed gradually.

Jim continues:

  Closing the loop . . . I've discussed this question with the Dev team. And eventually we have decided not to change current behavior, for the following reasons:

  1) The optimizer does not guarantee timing or number of executions of scalar functions. This is a long-estabilished tenet. It's the fundamental 'leeway' tha allows the optimizer enough freedom to gain significant improvements in query-plan execution.

  2) This "once-per-row behavior" is not a new issue, although it's not widely discussed. We started to tweak its behavior back in the Yukon release. But it's quite hard to pin down precisely, in all cases, exactly what it means! For example, does it a apply to interim rows calculated 'on the way' to the final result? - in which case it clearly depends on the plan chosen. Or does it apply only to the rows that will eventually appear in the completed result? - there's a nasty recursion going on here, as I'm sure you'll agree!

  3) As I mentioned earlier, we default to "optimize performance" - which is good for 99% of cases. The 1% of cases where it might change results are fairly easy to spot - side-effecting 'functions' such as NEWID - and easy to 'fix' (trading perf, as a consequence). This default to "optimize performance" again, is long-established, and accepted. (Yes, it's not the stance chosen by compilers for conventional programming languages, but so be it).

  So, our recommendations are:

  a) Avoid reliance on non-guaranteed timing and number-of-executions semantics.
  b) Avoid using NEWID() deep in table expressions.
  c) Use OPTION to force a particular behavior (trading perf)

  Hope this explanation helps clarify our reasons for closing this bug as "won't fix".

  Interestingly, AND NOT (s_guid = NEWID()) yields the same execution plan

This is a consequence of normalization, which happens very early during query compilation. Both expressions compile to exactly the same normalized form, so the same execution plan is produced.


Ms Access queries equivalent in Sql Server

A query in Access is a view in SQL Server. I am not sure whether the typical migration tools will bring those along and change them to views for you.

Be careful with the code you're using in your queries, as functions like FIRST(), IIF(), ISNULL() etc. either don't exist in SQL Server at all, or work differently.


Where My_ID = ID0001, ID0002, ID0003…
IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1<10,"ID000" & Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1,
IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1<100,"ID00" & Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1,
IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1<1000,"ID0" & Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1,
"ID" & Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1
)))

With this, you will be good for 9,999 unique ID numbers before going from a 6 character ID to 7 characters.

The formula display has extra carrage returns for visual effect which can be removed to provide a continuous string.

If you wish to use VBA, you might consider the following:

    Dim IDNum as Integer
    Dim NewID as String

    IDNum = Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1

    IF IDNum< 10 then
        NewID  = "ID000" & IDNum
    ElseIF IDNum< 100 then
        NewID  = "ID00" & IDNum
    ElseIF IDNum< 1000 then
        NewID  = "ID0" & IDNum
    Else
        NewID  = "ID" & IDNum
    EndIf

    Me.My_ID = NewID

I hope that this is helpful.


ORDER BY is not allowed on VIEWS unless you set a TOP() clause.

The wizard adds it automatically just to avoid the error.

Have a look at CREATE VIEW on MS Docs.

  The SELECT clauses in a view definition cannot include the following:

  An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement

    Important:
    The ORDER BY clause is used only to determine the rows that are returned by the
    TOP or OFFSET clause in the view definition. The ORDER BY clause does not
    guarantee ordered results when the view is queried, unless ORDER BY is also
    specified in the query itself.

  The INTO keyword
  The OPTION clause
  A reference to a temporary table or a table variable.

You should add ORDER BY clause in your query:

SELECT   F1, F2, F3, F4
FROM     YOUR_VIEW
ORDER BY F1, F2


SQL Server 2008 log shipping via JungleDisk

I would like to answer my own question - Jungle disk can successfully be used as transport for logs to be delivered to off-site location. I have it working with SQL Server 2008.

Before following steps I outlined below I recommend familiarizing yourself with log shipping by first setting it up within the same LAN - I found that wizard works very well for that. This way you will know what to expect when log shipping works (or doesn't work) properly.

These are the steps (if you also want primary database to be mirrored it is best to set up mirroring first):

1) Take full database backup and log backup on your primary database. Bring these 2 files offsite and restore them using "RESTORE WITH STANDBY" mode.

2) Configure log shipping on primary only without a witness server (witness may work but in my case off-site location is not connected to primary via VPN, so secondary server would not be routable and witness server wouldn't be able to see it). Set logs to be saved to network share on your LAN. This share should point to "real" hard drive, not to Jungle Disk directly (even though this will work too but may slow things down).

3) In out setup in primary location Jungle disk is installed and mounted as a drive letter on the server that hosts file share for logs to be saved. In Jungle Disc Configuration go to "Network Drive/Sync Folders" and setup sync for local folder corresponding the share to be synced with folder on Jungle disk. I use "All Files in this folder" for "What to Sync" and "Make Network drive match this folder" for "How to Sync"

4) Wait a little to make sure log backups for primary database appear in folder on Jungle disk.

5) On off-site computer hosting SQL Server mount Jungle Disk as a drive letter (it is preferable to have a separate server for that  and exposed folder with logs via file share but I am describing configuration I have up and running). Make sure that log backups are visible and accessible on Jungle disk

6) Configure log shipping on the secondary.

The way I did this was to temporarily expose secondary via firewall/external IP (you can use SQL Server Auth to connect). Then you can use log shipping wizard in Database Properties to configure secondary (make sure to specify correct folder for log backup destination - this would be local folder on secondary server) but DO NOT PRESS OK to actually finish configuration and create jobs on secondary - if you do it will try to use network share from primary location as a source for log backups and it will not work. Instead select "Script Configuration to new Query window". This script will have several parts: one that should be run on primary - delete that one because primary is already configured; another is for secondary - it will have this text in the beginning:

-- Execute the following statements at the Secondary to configure Log Shipping
-- for the database [$PublicIP\$InstanceName].[$DatabaseName],
-- the script needs to be run at the Secondary in the context of the [msdb] database.

Copy/paste this part to new query window on secondary server. Search this script for @backup_source_directory - it will be set to file share in primary location. You need to modify it to point to the folder on Jungle Disk that contains your log backup

Run the script and it should create 3 jobs on the secondary server - LSAlert*, LSCopy* and LSRestore*

You log shipping now should start working on secondary. To be sure check job history.

If you ever need to modify log shipping parameters such as restore delay, threshold, history retention on secondary server - you need to modify these parameters directly in system databases (as opposed to using log shipping wizard because you log shipping configuration on primary from which you would ordinarily use wizard will not have secondary listed at all). Tables you are interested in are in msdb database, everything that starts from "dbo.log_shipping_" - the one I had to use so far is [msdb].[dbo].[log_shipping_monitor_secondary] for alert settings and [msdb].[dbo].[log_shipping_secondary] for some configuration settings (backup source/destination, file retention period)

Hope this helps.


I broke replication. Could this have been fixed?

Because you ran the mysql_upgrade on both master and slave, the ALTER TABLE was happening twice on slave side. The changes on master was propagated to slave, trying to add columns which you already added with manual mysql_upgrade.

I don't think reset slave has anything to with the problem, unless you were looking to skip the entire block of statements done by mysql_upgrade, which IMO is very dangerous.


Knowing this is 64-bit SQL Server 2008 Enterprise simplifies the discussion, so thanks for that.  The first thing to say is that it is almost never right to leave the max server memory configuration option at the default of 2147483647.

The max server memory option only limits the size of the buffer pool (used for all single-8KB allocations pre-2012).  SQL Server can also require significant amounts of non-buffer pool memory (allocations > 8KB).  Other instances, applications, and the OS also need room to work (and 2GB is not always a good rule of thumb there).  Amit Banerjee, an Escalation Engineer with Microsoft PSS has three excellent posts on this topic, which I encourage you to read.

You will generally want to set max server memory to a fairly conservative maximum value to start with, monitor the actual amount of free memory on the server over a full load cycle, preferably over a decent period of time, and gradually increase it as appropriate and necessary.

The exact level to start at depends on how much non-buffer pool memory SQL Server might be expected to require, and how much memory will be needed by other instances, applications and the OS.  On a server dedicated to a single instance of SQL Server, I might start max server memory around 160GB with 192GB installed.  Your circumstances might dictate that a much lower starting point is indicated.

I also recommend you consider using the Lock Pages in Memory (LPIM) option (that link explains why in great detail).  This will be enabled by default if the SQL Server service is running as LocalSystem (which is not a best practice).  See this CSS article for details including how to tell if SQL Server is currently using LPIM or not.

Memory used via this mechanism is not visible to Task Manager; it is better to monitor SQL Server memory usage via Performance Monitor counetrs and/or the sys.dm_os_sys_memory DMV.


Are their any default privilege in Oracle for users?

You can create a role and grant privileges to that role and in turn grant that role to a user.

To grant dba privileges to a user grant dba to someuser

You can explore default/current roles that gives you an idea.

select grantee role,privilege
from dba_sys_privs
where grantee not in (select username from dba_users)
-- and grantee in('DBA','CONNECT','RESOURCE')
group by grantee,privilege
order by 1

or

 -- dba_roles data dictionay lists all roles existing/created in Oracle
    select grantee role,privilege
    from dba_sys_privs
    where grantee in (select role from dba_roles)
    group by grantee,privilege
    order by 1

Keep in mind you have to be very careful before giving blanket privileges.


Efficient pagination for big amount of data

If the 50,000 records are scattered all over the place, and it takes 4ms to fetch each one (15,000 rpm hard drive), it would take 200 seconds.  So 25 seconds is reasonable, since it is unlikely they will be scattered completely at random.

You can CLUSTER users on index_name which will reorder the table to be in the same order as the string_id.  But it won't automatically stay clustered as you do updates and more insertions.

You can add an index on (string_id, customer_id) and see if an index-only scan will solve your problem.


SQL Server lock_escalation flag on ALTER TABLE -- can I really restructure without a table lock?

You say the documentation is thin, but the answers are all there.
Lock Escalation
You really need to read that whole page, but essentially lock escalation only applies to 'regular' locks (e.g. shared, exclusive). Operations that change the structure of an object take much more restrictive schema locks (e.g. Sch-M) which prevent all concurrent access to the object being modified. As such, less restrictive 'ordinary' locks are not required. Ultimately, the question you ask is moot, because ALTER TABLE does not take row or page level locks that might be escalated to a partition or object level lock.
Regarding ALTER_TABLE (in the Remarks section):

Locks and ALTER TABLE
The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end. In an ALTER TABLE…SWITCH operation, the lock is acquired on both the source and target tables. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or, on some editions of SQL Server, adding a NOT NULL column with a default value, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows.

and:

Adding NOT NULL Columns as an Online Operation
Starting with SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table. This is because the existing rows in the table are not updated during the operation; instead, the default value is stored only in the metadata of the table and the value is looked up as needed in queries that access these rows. This behavior is automatic; no additional syntax is required to implement the online operation beyond the ADD COLUMN syntax.

If you were using Enterprise Edition, the change you propose could be an instant, metadata only change (though any subsequent changes to data in the new column would require real allocations, data movement, and logging, of course).
As it is, adding this column will be an expensive, long-running, fully-logged, single transaction, size-of-data operation. There is no way to 'chunk' this change without changing the definition of the column (a nullable column can be added as a metadata-only change, then updated in chunks).


How do I create a bounded range type in the SQL Server?

Besides SQLCLR, you could also store both values in either XML or JSON (if you are using SQL Server 2016 or newer) and retrieve them as discrete values. Similarly you could simply convert them to VARCHAR and place them, separated by a comma, in a single column (I don't recommend this at all, but it is functionally similar to the other approaches I just mentioned). The SQLCLR approach makes it easy to get the discrete values back out, and in their native datatypes, but you could just as easily write a few helper UDFs to do the same with those values shoved into XML or JSON.

In all of these cases, while you could accomplish the goal of storing two values in a single column, I don't see what you gain by doing this given that each approach has some amount of overhead yet the usage doesn't seem to justify it. The main reason for creating your own type isn't to merely associated related data points, but to have the ability to act upon them. The internal CLR types not only store the discrete data points, but also offer quite a few methods to do interesting things with those data points. I am not sure what you can do operationally with just beginning and ending numbers that don't have commonly used algorithms for making use of them. It seems that you will be doing more work to get the values back out (and most likely decrease performance while doing so) than you will be gaining by putting them in such a structure.

Unless you can clearly see a guaranteed benefit from doing this, I suggest you keep them as discrete data points. And if my memory serves me correctly, you should be able to get away with using TINYINT, assuming no verse # goes above 255 (and they certainly wouldn't be negative or NULL). Looking here ( New Testament Statistics ) and here ( Old Testament Statistics - New American Bible ) it seems that the highest verse number is 80 for the New Testament (Luke chapter 1), and 176 for the Hebrew Bible — "Old Testament" in Christian terminology — (Pslam 119).


After manually fail over resume button is disabled and query doesn't resumes

Did you also check on the SQL errors? What does it says...?

One thing you can try is to restart your endpoint mirroring on both the primary and secondary instance to see if it helps.

(Stop the endpoint mirroring first)

ALTER ENDPOINT  STATE=STOPPED

(Then start it back up)

ALTER ENDPOINT  STATE=STARTED


Weird error on creating table and selecting rows from table

Your problem arises from suffering metadata corruption, in particular, a difference between MySQL/filesystem contents and InnoDB internal data dictionary, which are stored separately.

I cannot give you concrete advice without knowing more about the filesystem, MySQL and InnoDB point of view, and also looking at your error log, but a general "catch all" solution I have most successfully applied in the past is to move/backup all the good data away* (e.g. dump, rename to a different database, backup orphan files), *and then drop the database, which will sync the InnoDB data dictionary and MySQL/filesystem state (to non-existence), then recreate and move back/reimport the original tables. The RENAME TABLE should be almost an instant operation, so it should not take a lot of time. Any operation will not work on the problematic table- that is likely to be lost unless the .ibd file still exists can be backup up before dropping the database.

To prevent this from happening in the future, you probably want to upgrade to MySQL 8, where data dictionary is unified on InnoDB storage, and this duality should be more rare.


Pivoting rows into columns dynamically in Oracle

Column value is a string type (CHAR, VARCHAR2).
You can not SUM strings. Use another aggregate function, e.g. MIN. And you do not need COALESCE.

select
   ID,
    min(case when Field = 'name' then value end) as "Name",
    min(case when Field = 'age' then value end) as "Age",
    min(case when Field = 'gender' then value end) as "Gender",
    min(case when Field = 'status' then value end) as "Status"
 from "TABLE_1"
group by ID;

        ID Name  Age   Gender Status
---------- ----- ----- ------ --------
         1 Bob   30    male
         2 Susan              married


How to create a SELECT statement involving a subtype

How you do the select depends on your table design.  Your EER depicts the entities, relationships and attributes nicely, but doesn't say anything about table design.  (That's as it should be, IMO).  A relational schematic would give those details.

If you are interested in how to design tables for a subtype (or subclass) situation here are two alternative techniques for you to explore:

http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html
http://martinfowler.com/eaaCatalog/classTableInheritance.html

And there is a technique often used when tables have a one to one relationship to each other.  You can see it here:

https://stackoverflow.com/questions/tagged/shared-primary-key


Extend mysql 5.5 tablespace to another drive on windows

mysql have "working dir" where all the schemas' dirs are stored. You have to:

stop your mysql
move the dir with desired schema to the new huge drive

mv D:\path\to\workingdir\dbschema F:\dbschema

make the link from the new location to the old working dir:

mklink \D F:\dbschema D:\path\to\workingdir\dbschema

start your mysql

Test that approach on some noncritical schema, indeed.


OK I found the solution, and I am writing it here not just to help others that encounter a similar problem, but it is also something worth for everyone that works with indexed views.

First some points on indexed views.

Indexed Views

An index view although it is also an "index" it is also a "view", thus it might benefit when the system is updating the indexed view from non-clustered indexes on the base table (and even covering indexes and statistics).

(To prove that point I have added indexes on base tables in a database that wasn't used besides getting replication data, and still the dm_db_index_usage_stats system view showed user seeks, and the only one use it was the indexed view.)

And it appears that the best indexes to add for the benefit of an index view would be for seeking a single value or a small range of values, since this is what the indexed view is basically doing when being used locally or on a subscriber to transactional replication.
Although conditional joins are not prohibited by the indexed view restrictions, still it is a good idea to avoid them (just as in regular queries).

Consider the following join:

FROM a
JOIN b ON b.id = a.id
JOIN c ON c.id = a.id
JOIN final ON final.id = b.FinalID OR (b.finalID IS NULL AND final.id = c.FinalID)

This can cause performance bottlenecks, instead it is better split it up into two extra indexed views and then have a view that combines both using "UNION ALL".
While and indexed view is also a "view" it is also an "index" which in other words that inserts/updates/deletes also has to update the indexed view, thus if the view has performance problems the update will take longer, and in the case of replication if each transaction takes to long then they might pile up after each other, and eventually bring replication to an halt

Please also take in mind when working with replication, that reinitializing the subscriber causes all of the indexes and statistics that have been added at the client to be dropped (if pre_creation_cmd has been set to drop which is the default), and only indexes that are part of the replication itself will be recreated, and one has to recreate all of the subscriber specific indexes manually.

So in my situation I have first added new indexed views that turned out to need better non-clustered indexes, and also some that used conditional joins, but after I reinitialized I didn't bother recreating first the base tables non-clustered indexes and first tried to create the indexed views which caused bottlenecks for the replication process.


SSIS convert date in mmddyyyy format to date format using SSIS expression

You have to use substring to get the parts needed for year monh and so on
(DT_DATE)(SUBSTRING(yourcolumn,5,4) + "-" + SUBSTRING(yourcolumn,1,2) + "-" + SUBSTRING(yourcolumn,3,2))


Which schema is better for a shopping project?

You are much better off using the EAV (entity-attribute-value) approach for a product feature catalog.  The approach is transportable to any SQL platform as it doesn't rely on a particular feature of Postgresql (JSON columns).

All of the usual arguments against EAV don't apply to a product feature catalog, since product feature values are generally only regurgitated into a list or worst case into a comparison table.

Using a JSON column type takes your ability to enforce any data constraints out of the database and forces it into your application logic.  Also, using one attributes table for every category has the following disadvantages:

It doesn't scale well if you eventually have hundreds of categories (or more).
If you change the allowable attributes on a category you have to change a table definition instead of just adding or removing rows in category_fields
You may still end up with sparsely populated tables if the product has many potential features only a small subset of which are known.

It is not especially difficult to retrieve data about a product with specific features.  It is arguably easier to create a dynamic SQL using the EAV model than it would be using the table-per-category model.  In table-per-category, you need reflection (or your JSON) to find out what the feature column names are.  Then you can build a list of items for a where clause.  In the EAV model, the WHERE X AND Y AND Z becomes INNER JOIN X INNER JOIN Y INNER JOIN Z, so the query is a little more complicated, but the logic to build the query is still totally table-driven and it will be more than scalable enough if you have the proper indexes built.

There are a lot of reasons not to use EAV as a general approach.  Those reasons don't apply to a product feature catalog so there is nothing wrong with EAV in this specific application.


How can I identify non deterministic updates occurring in my SQL Server?

SQL Server does not warn you of this when using its proprietary UPDATE syntax. The MERGE statement would throw an error "The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row."

The execution plan for your example is

update t1  set t1.col = t2.col  from @t1 t1 join @t2 t2           on t1.id = t2.id
  |--Table Update(OBJECT:(@t1 AS [t1]), SET:(@t1.[col] as [t1].[col] = @t2.[col] as [t2].[col]))
       |--Stream Aggregate(GROUP BY:([Bmk1000]) DEFINE:([t2].[col]=ANY(@t2.[col] as [t2].[col])))
            |--Nested Loops(Inner Join, WHERE:(@t2.[id] as [t2].[id]=@t1.[id] as [t1].[id]))
                 |--Table Scan(OBJECT:(@t1 AS [t1]))
                 |--Table Scan(OBJECT:(@t2 AS [t2]))

The Bmk column is output from the scan on @t1 to act as a unique row identifier. The, potentially multiple, joining rows from @t2 are then added and collapsed down to an arbitrary one per Bmk using the ANY aggregate. The plan may in some circumstances replace the ANY aggregate with a DISTINCT SORT

So one way would be to search the plan cache for all execution plans containing update operators that have a descendant operator using either ANY or DISTINCT SORT as this may be a sign that SQL Server was arbitrarily removing the duplicates before doing the update.

This is far from fool proof as they may appear in the plans for other reasons but should give some viable candidates to review.

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT st.text
FROM   sys.dm_exec_cached_plans cp
       CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
       CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE  EXISTS(SELECT 1
              FROM   qp.query_plan.nodes('//Update') upd(n)
              WHERE  1 IN ( n.exist('//Aggregate[@AggType eq "ANY"]'),
                            n.exist('//RelOp[@LogicalOp eq "Distinct Sort"]') ))


Selecting the first purchase item of each customer

select cid,
       pdate,
       itemcode,
       itemname
from (
  select sales.*,
         rank() over (partition by cid order by pdate) as rnk
  from sales
) t
where rnk = 1
order by cid;

Alternatively

select s1.cid,
       s1.pdate,
       s1.itemcode,
       s1.itemname
from sales s1
where pdate = (select min(s2.pdate)
               from sales s2
               where s1.cid = s2.cid)
order by cid;


From MSDN - Full Text Search Overview:

"Full-text queries perform linguistic searches against text data in full-text indexes by operating on words and phrases based on rules of a particular language such as English or Japanese. Full-text queries can include simple words and phrases or multiple forms of a word or phrase.

Full-text search is applicable in a wide range of business scenarios such as e-businesses—searching for items on a web site; law firms—searching for case histories in a legal-data repository; or human resources departments—matching job descriptions with stored resumes. The basic administrative and development tasks of full-text search are equivalent regardless of business scenarios. However, in a given business scenario, full-text index and queries can be honed to meet business goals. For example, for an e-business maximizing performance might be more important than ranking of results, recall accuracy (how many of the existing matches are actually returned by a full-text query), or supporting multiple languages. For a law firm, returning every possible hit (total recall of information) might be the most important consideration."

Comparison of LIKE to Full-Text Search

"In contrast to full-text search, the LIKE Transact-SQL predicate works on character patterns only. Also, you cannot use the LIKE predicate to query formatted binary data. Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data. A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned."

So the main difference is the type of search that they do: full text is about word search, while LIKE is about character search. I would personally use full text search on tables where I have big amounts of human text - usually big columns (big descriptions, exceeding the standard 8k characters) in big tables (millions of rows). Need to keep in mind the added administration for FTS (create catalogs, indexes, update them when needed).


Interrupting query sends wrong thread ID

Write a bug report!  http://bugs.mysql.com.
  26542310314 MOD 2^32 = 772506538

That is, the 64-bit connection_id was chopped to 32 bits to send the KILL, thereby sending it to the wrong id.
Not many users get past 4 billion connections.  How long has your server been up?  I hope the code is not reconnecting for every query.
Change logs

----- 2014-09-25 5.7.5 Milestone 15 -- Bugs Fixed -- -----
Connection IDs are 32-bit unsigned integers, beginning at 1. When the server assigns connection IDs and reaches the top of the 32-bit range, it rolls the value over to begin at 1 again. It was possible that the server would assign a connection ID to a new thread while that ID was still in use by an existing thread, if the old thread was particularly long running. For such cases, reference to the ID becomes ambiguous. For example, it cannot reliably be determined for KILL connection_id which thread to kill, which could lead to undefined behavior. This behavior has been corrected so that in-use IDs are not reused.
Connection IDs are 32-bit unsigned integers, beginning at 1. When the server assigns connection IDs and reaches the top of the 32-bit range, it rolls the value over to begin at 1 again. It was possible that the server would assign a connection ID to a new thread while that ID was still in use by an existing thread, if the old thread was particularly long running. For such cases, reference to the ID becomes ambiguous. For example, it cannot reliably be determined for KILL connection_id which thread to kill, which could lead to undefined behavior. This behavior has been corrected so that in-use IDs are not reused.
----- 2012-12-11 5.6.9 Release Candidate -- Bugs Fixed -- Incompatible Change -----
Connection ID (thread ID) values greater than 32 bits can occur on
some systems (such as busy or long-running 64-bit systems), causing
these problems:
Connection IDs written to the general query log and slow query log
were incorrect. This was true for logging to both files and tables.
The
CONNECTION_ID()
function could return a value with a data type too small for values
larger than 32 bits.
The
mysql_thread_id()
and
mysql_kill()
C API functions did not handle ID values larger than 32 bits. This
could result in killing the wrong thread; for example, if you invoked
mysql_kill(mysql_thread_id()).
Connection IDs now are permitted to be 64-bit values when the server
supports them (when built with 64-bit data types), which has these
effects:
Connection IDs are logged correctly to the general query log and slow
query log.
Note This change involves a modification to the log tables, so after
upgrading to this release, you must run mysql_upgrade and restart the
server.
CONNECTION_ID()
returns a data type appropriate for values larger than 32 bits.
mysql_thread_id()
is unchanged; the client/server protocal has only 4 bytes for the ID
value. This function returns an incorrect (truncated) value for
connection IDs larger than 32 bits and should be avoided.
mysql_kill()
still cannot handle values larger than 32 bits, but to guard against
killing the wrong thread now returns an error in these cases:
If given an ID larger than 32 bits,
mysql_kill()
returns a
CR_INVALID_CONN_HANDLE
error.
After the server's internal thread ID counter reaches a value larger
than 32 bits, it returns an
ER_DATA_OUT_OF_RANGE
error for any
mysql_kill()
invocation and
mysql_kill()
fails.
To avoid problems with
mysql_thread_id()
and
mysql_kill(),
do not use them. To get the connection ID, execute a SELECT
CONNECTION_ID() query and retrieve the result. To kill a thread,
execute a KILL statement.
(Bug #19806, Bug #11745768, Bug #65715, Bug #14236124, Bug #44728, Bug #11753308)

(Small bug numbers can be found at bugs.mysql.com; large bug numbers are inaccessible - internal to Oracle.)
So, recommend you upgrade to 5.6.9+ or 5.7.5+.  Since you are on 5.6.27, there is a puzzle.  Maybe some detail in that lengthy Changelog says something useful.


Is DEFAULT NULL and 'nullable value without default' completely the same?

Not providing any default value or explicitly providing NULL is exactly the same thing for base data types.

Adding a column with a NULL default value, does not require rewriting the entire table. It is essentially only a metadata update for the table.

Quote from the 9.6 manual

  If there is no DEFAULT clause, this is merely a metadata change and does not require any immediate update of the table's data; the added NULL values are supplied on readout, instead

Note that this has changed with Postgres 11.

When you add a column with a static default (e.g. default 42) the table won't be re-written either. Only if you provide a "volatile" default (e.g. current_timestamp) the table will be re-written.


I think you'd be better off using the text version of the explain plan output rather than XML, as it gives you a pre-formatted readable ascii representation of the query plan.

eg:

SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','#schema_short_code#/#query_id#','ALL'));

Failing that, if you have the means to do so server-side, you could use Oracle SQL Developer to give you a graphical representation of the query plan - Though this is really just the same as the ASCII version with a few icons.

Also, take a look at $ORACLE_HOME/rdbms/xml/orarep/xplan/xplSch.xsd , $ORACLE_HOME/rdbms/xml/orarep/xplan/xplHtml.xsl and $ORACLE_HOME/rdbms/xml/xsl/kuplan.xsl - They may help you format your XML output to prettier HTML.


Filter rows, but only select most recent row for each Id

One thing you can do is create another table which will store the current status of every task. So whenever you are updating your task_overview you will also update this new table keeping only the latest status. This will make table task_overview more or less like a log.


If Exists - Select Prevent Repeating Code

Unfortunately I'm afraid by trying to prevent repeating code you might end up in writing more code and making the query less readable.
But I believe this can be advantageous for you - take into account situations where in the test you would only have to search in a one or two tables, where in order to retrieve all the details you will have to join to several more. In those situations you can simplify the test query thus also making them quicker to execute.

And while we are at it, you might also consider changing your WHERE statement. Using functions in the predicate makes them not SARGable and prevent using indexes in those cases.
A better approach in this example would be to check

WHERE OrderNumber LIKE 'ABC%'

as the optimizer will perform conversion to something along the lines of

OrderNumber >= 'ABC' AND OrderNumber < 'ABD'

which supports the use of indexes.
So this would be Index Scan (using left()) versus Index Seek (using LIKE).


In a two-server dataguard setup, can I store the recovery catalog in the same database?

If I correctly understand your question as: "I want to create a standby database and later on take backups on the primary like if there would be no standby present"

In this case, you do not need a recovery catalog: quote "An RMAN recovery catalog is required so that backups taken on one database server can be restored to another database server. It is not sufficient to use only the control file as the RMAN repository because the primary database will have no knowledge of backups taken on the standby database." See http://download.oracle.com/docs/cd/E11882_01/server.112/e25608/rman.htm#BAJEDHFB


How to track SSIS memory and CPU performance?

Have you looked into RedGate's reflector? It sounds like exactly what you're looking.

Note: I have no affiliation with RedGate. Web searches solve problems!


SQL Server 2008 user and group permissions behavior weirdness

I recreated your situation. I created the following items:

Database TEST
SQL Login DOMAIN\USER
SQL Login DOMAIN\GROUP
Database User USER (linked to SQL Login DOMAIN\USER)
Database User GROUP (linked to SQL Login DOMAIN\GROUP)
Assigned Database User USER no database roles
Assinged Database User GROUP the database roles db_datareader and db_datawriter

I then queried the TEST database with the Windows login DOMAIN\USER. Because the DOMAIN\USER is also memmber of DOMAIN\GROUP the user is allowed to SELECT from all tables in the TEST database.

You are writing that the DOMAIN\USER is unable to select any data. Correct?

Do you have any orphaned users on the database level? Follow these steps to check.

Open SQL Server Query Window
Change database to YOUR database
Run the command exec sp_change_users_login 'Report'
Check the output. You might find that one of your database users is not properly linked to a SQL Server Login.

To fix:

In the same query window for YOUR database execute the following command

exec sp_change_users_login 'Update_one', '<DATABASE USER>', '<SQL LOGIN>'

E.g. if your database is YOUR_DB, the SQL Login (SQL Server level) is YOUR_SQL_LOGIN and the database user you are relinking is YOUR_DB_USR, then the command would be:

USE YOUR_DB
GO
exec sp_change_users_login 'Update_one', 'YOUR_DB_USR', 'YOUR_SQL_LOGIN'

If everything is ok and you don't have any orphaned users, then try checking the permissions of the database user in question.

Does that user have an explicit DENY set somewhere?
Is the account disabled at the SQL Server Login level?
Do you have any error messages?

I hope this gives you some ideas on what to check.


Is it safe to rely on the order of an INSERT's OUTPUT clause?

Can I rely on the returned identity values from the dbo.Target table
  insert to be returned in the order they existed in the 1) VALUES
  clause and 2) #Target table, so that I can correlate them by their
  position in the output rowset back to the original input?

No, you can't rely on anything to be guaranteed without an actual documented guarantee. The documentation explicitly states there is no such guarantee.

  SQL Server does not guarantee the order in which rows are processed
  and returned by DML statements using the OUTPUT clause. It is up to
  the application to include an appropriate WHERE clause that can
  guarantee the desired semantics, or understand that when multiple rows
  may qualify for the DML operation, there is no guaranteed order.

This would rely on a lot of undocumented assumptions

The order the rows are output from the constant scan is in the same order as the values clause (I've never seen them differ but AFAIK this is not guaranteed).
The order the rows are inserted will be the same as the order they are output from the constant scan (definitely not always the case).
If using a "wide" (per index) execution plan the values from the output clause will be pulled from the clustered index update operator and not that of any secondary indexes.
That the order is guaranteed to be preserved thereafter - e.g. when packaging rows up for transmission over the network.
That even if the order appears predictable now implementation changes to features such as parallel insert won't change the order in the future (currently if the OUTPUT clause is specified in the INSERT…SELECT statement to return results to the client, then parallel plans are disabled in general, including INSERTs)

An example of point two failing (assuming clustered PK of (Color, Action)) can be seen if you add 600 rows to the VALUES clause. Then the plan has a sort operator before the insert so losing your original order in the VALUES clause.

There is a documented way of achieving your goal though and this is to add a numbering to the source and use MERGE instead of INSERT

MERGE dbo.Target
USING (VALUES (1, 'Blue', 'New', 1234),
              (2, 'Blue', 'Cancel', 4567),
              (3, 'Red', 'New', 5678) ) t (SourceId, Color, Action, Code)
ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT (Color,
          Action,
          Code)
  VALUES (Color,
          Action,
          Code)
OUTPUT t.SourceId,
       inserted.TargetId;

@a_horse_with_no_name

  Is the merge really necessary? Couldn't you just do an insert into ...
  select ... from (values (..)) t (...) order by sourceid?

Yes you could. Ordering guarantees in SQL Server… states that

  INSERT queries that use SELECT with ORDER BY to populate rows
  guarantees how identity values are computed but not the order in which
  the rows are inserted

So you could use

INSERT dbo.Target (Color, Action, Code)
OUTPUT inserted.TargetId
SELECT t.Color, t.Action, t.Code
FROM
(VALUES (1, 'Blue', 'New', 1234),
        (2, 'Blue', 'Cancel', 4567),
        (3, 'Red', 'New', 5678) ) t (SourceId, Color, Action, Code)
ORDER BY t.SourceId

This would guarantee that the identity values are assigned in order of t.SourceId but not that they are output in any particular order or that the identity column values assigned have no gaps (e.g. if a concurrent insert is attempted).


Before concluding whether you should or should not enable BPE, its good to analyse the current usage of memory on the SQL server.

You can do this with help of system health session, various DMV's and using perfmon counters:

Using system Health session you can refer here
Various DMV's you can consider for monitoring memory usage:

select * from sys.dm_os_memory_clerks

select * from sys.dm_os_memory_objects
select * from sys.dm_os_memory_pools
select * from sys.dm_os_memory_nodes
select * from sys.dm_os_memory_cache_entries
select * from sys.dm_os_memory_cache_hash_tables

Refer here on how to use above dmv's and analyzing for memory usage.

For perfmon you can start analyzing with below counters:

Memory – Available MBytes

Paging File – % Usage

Physical Disk – Avg. Disk sec/Read

Physical Disk – Avg. Disk sec/Write

Physical Disk – Disk Reads/sec

Physical Disk – Disk Writes/sec

Processor – % Processor Time

SQLServer: General Statistics – User Connections

SQLServer: Memory Manager – Memory Grants Pending

SQLServer: SQL Statistics – Batch Requests/sec

SQLServer: SQL Statistics – Compilations/sec

SQLServer: SQL Statistics – Recompilations/sec

On how to use them , please refer here.

Now, once you have analyse the proper memory usage along with how much stays out there on Buffer pool as well and how much you think it lacks, can help you in deciding should you go for enabling BPE or not.

Note: In addition to above , make sure the MAX server memory settings are properly configured. Adding below info in case you want to go with BPE

  Buffer Pool Extensions are available for the SQL Server 2014
  Enterprise, Business Intelligence and Standard x64 editions. The size
  of the buffer pool extension can be up to 32 times the value of
  max_server_memory for the SQL Server Enterprise edition and up to 4
  times the max_server_memory value for Business Intelligence and
  Standard editions. Microsoft recommends a ratio between the size of
  the physical memory (max_server_memory) and the size of the buffer
  pool extension of 1:16 or less. A good starting point is 1:4.

A suggested read on using BPE along with its Best practices  Buffer Pool Extension


Is there a good reason to create an additional table to realize a 1:N relation?

even though I would not need it.

No. If you add it without any expectation of needing it, then it only serves to:

Add overhead when querying the data
Add overhead when adding or updating data
Cause confusion for other people working on the DB
Be a potential source of inconsistent data and other bugs

On the other hand if you think it is likely that there will be a need for the relationships to become many-to-many in future, for instance a line item being on multiple invoices due to partial payments or refunds, then there may be a case for adding the extra structure now to save on refactoring effort later but I would be wary of adding it from the start because of the above reasons unless I was very sure it was going to be needed at some point soon.
If you do add the extra structure but don't want to allow many-to-many to be a thing just yet, you have extra work to define constraints that enforce the desired one-to-many behaviour until it is no longer needed.


Retrieving page contents from SQL, Postgresql?

pageinspect module

Using PostgreSQL, there is an extension that does all of this. Your question is vague so I won't even try to address the particulars here, nor is it reasonable to enumerate everything this extension does, perhaps it's enough to say that it has 17 functions and as far as I know includes all of the mechanisms available to end users to introspect pages (not saying it does everything you want).

You may want to read Database Page Layout


Which database to use when you have a 6 billion of rows inside and need to query rows with from list of IDs?

My assumption here is using FLOAT in place of the actual composite key is slowing the query engine's ability to create the proper estimates.
So instead of using the "clever" column, just use a composite primary key:
CREATE TABLE Country
(
  CountryCd  CHAR(3)      NOT NULL  --ISO Country Code
 ,Name       VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_Country PRIMARY KEY (CountryCd)
 ,CONSTRAINT AK_Country UNIQUE (Name)
)
;
CREATE TABLE Subdivision  --State/province/region/county etc.
(
  CountryCd      CHAR(3)      NOT NULL
 ,SubdivisionCd  CHAR(3)      NOT NULL  --ISO subdivision code
 ,Name           VARCHAR(50)  NOT NULL
 ,CONSTRAINT FK_Subdivision_Division_Of_Country FOREIGN KEY (CountryCd) REFERENCES Country (CountryCd)
 ,CONSTRAINT PK_Subdivision PRIMARY KEY (CountryCd, SubdivisionCd)
 ,CONSTRAINT AK_Subdivision UNIQUE (CountryCd, Name)
)
;
CREATE TABLE City
(
  CityId         INT          NOT NULL
 ,CountryCd      CHAR(3)      NOT NULL
 ,SubdivisionCd  CHAR(3)      NOT NULL
 ,Name           VARCHAR(50)  NOT NULL
 ,CONSTRAINT FK_City_Located_In_Subdivision FOREIGN KEY (CountryCd, SubdivisionCd) REFERENCES Subdivision (CountryCd, SubdivisionCd)
 ,CONSTRAINT PK_City PRIMARY KEY (CityId)
 ,CONSTRAINT AK_City UNIQUE (CountryCd, SubdivisionCd, Name)
)
;
CREATE TABLE CityRoadDistance
(
  SourceCityId       INT  NOT NULL
 ,DestinationCityId  INT  NOT NULL
 ,RoadDistance       INT  NOT NULL
 ,CONSTRAINT FK_CityDistance_From_City FOREIGN KEY (SourceCityId) REFERENCES City (CityId)
 ,CONSTRAINT FK_CityDistance_To_City FOREIGN KEY (DestinationCityId) REFERENCES City (CityId)
 ,CONSTRAINT PK_CityDistance PRIMARY KEY (SourceCityId, DestinationCityId)
 ,CONSTRAINT CK_CityDistance_SourceCityId_LT_DestinationCityId CHECK (SourceCityId < DestinationCityId) --Enforces only one record per city pair
)
;

Easy enough to find the single distance:
SELECT
  RoadDistance
FROM
  CityDistance
WHERE
  SourceCityId = 7
    AND DestinationCityId = 235

But as you said the secondary requirement is to return 1,000 in less than 50 ms.
You won't be able to use IN, so you will need to build a list for the WHERE clause (e.g. (SourceCityId = 7 AND DestinationCityId = 235) OR (SourceCityId = 1345 AND DestinationCityId = 2934)...).
This should allow the query engine to look at things and say: "Oh, I only need to perform 1,000 seeks" and return your data relatively quickly.  However for 50 ms or less you're going to need some very fast SSDs or the pages already cached in memory.
Alternatives would be something like an in-memory key-value store.  This would cut down on compilation and seek time somewhat, but I'd try using an actual composite key and seeing what happens.


How to model a relational database for a sales business domain?

I would establish 2 hierarchies:

region:
    region_id (PK)
    region_name
    ...

area:
    area_id (PK)
    region_id (FK to region)
    area_name
    ...

store:
    store_id (PK)
    area_id (FK to area)
    store_name
    ...

and

division:
    division_id (PK)
    division_name
    ...

department:
    department_id (PK)
    division_id (FK to division)
    department_name
    ...

subdepartment:
    subdepartment_id (PK)
    department_id (FK to department)
    subdepartment_name
    ...

Since store is tied to area and area to region, you don't need to directly tie store to region. Using ID values instead of embedding the area or region names directly simply allows those things to be renamed (and they will be). Same logical holds true for subdepartment.

I would build the sales_history table with the following columns:

store_id
subdepartment_id
sales_year - You may run your sales year from January through December, but this allows other options, without having to calculate the sales year off the date.
week_no - Again, this is something that could probably be calculated off the date you record, but having it explicitly simplifies searching, comparisons, and allows it to remain correct for past years, even if the calculation for a future year is different.
sales_date - Probably the first date in the week.
sales_amount
and, any other fields you need.

This will allow you to compare 2016 weeks 1-13 with 2017 weeks 1-13 at any level (store and department; region and subdepartment; whatever). You can also easily compare 2015, 2016, and 2017 (not as easy if storing this year's and last year's values in the same row).


Performance optimization: Alternative to LEFT JOIN the same table twice

I think this will do what you are asking.

SELECT
    ivr.ID
FROM ivr
LEFT OUTER JOIN ADDR_MASTER am1
    ON am1.ID = ivr.ID
        AND am1.ADDR_TYPE IN ('WORK','HOME')
WHERE (PHONE1 = '0123456789'
OR PHONE2 = '0123456789'
OR PHONE3 = '0123456789'
OR am1.PHONE = '0123456789'
)

That said, there are a few things you could do differently.

This might be better, not sure, but as long as it's not slower it's probably easier to maintain.

SELECT
    ivr.ID
FROM ivr
LEFT OUTER JOIN ADDR_MASTER am1
    ON am1.ID = ivr.ID
        AND am1.ADDR_TYPE IN ('WORK','HOME')
WHERE '0123456789' IN (PHONE1, PHONE2, PHONE3, am1.PHONE)

Now, assuming that you do in fact only need the ivr.ID (at least not anything from ADDR_MASTER) you could do this:

SELECT
    ivr.ID
FROM ivr
WHERE '0123456789' IN (PHONE1, PHONE2, PHONE3)
    OR EXISTS ( SELECT 1 FROM ADDR_MASTER am1
                WHERE am1.ID = ivr.ID
                    AND am1.ADDR_TYPE IN ('WORK','HOME')
                    AND am1.PHONE = '0123456789' )

I'd try various combinations to see which if any of these are actually faster.


Wait for an AlwaysOn replica to be synchronized in Powershell

I found my problem.

I was working under the wrong assumption that the objects were automatically refreshed in Powershell. I added a "$NewPrimary.Refresh()" insinde of the while loop and that did the trick.

I hope this helps someone with the same wrong assumption in the future.


Query multiple rooms with different conditionals (Adults, Children)

first insert the resultset of the room_type table to a temp table with the filtering by your parameters

 r.max_adults >= 3 AND r.max_children >= 0

then query that table and join the property  and plan tables to it and it should be faster.


sp_BlitzIndex performance concerns in production

When you run it on a restored instance, you won't get any data about which indexes are getting used, and which indexes SQL Server wishes it would have had.

So it's not really all that useful - however, it IS useful to understand how long sp_BlitzIndex will take to run. If you're worried about performance impacts, you can run it against a restored instance on similar hardware, and that'll tell you about the performance impact - but just don't take any actions based on the output.


How does Checkpoint know which point to recover from in PostgreSQL?

How is the REDO location determined exactly. When a Checkpoint starts, does it literally say "REDO location is the current WAL record + 1"?

In essence, yes.  Exactly, no.  There is code in there about rounding up for cases where the record will not fit in the remainder of the log file and other corner cases.  To get an exact answer you have to read the source code.  See CreateCheckPoint in src/backend/access/transam/xlog.c

  When we do determine the REDO location, how is it that we are guaranteed that all WAL records before this are no longer needed?

A checkpoint starts (after computing the REDO pointer) by surveying all pages in the buffer cache, and marking the ones which were dirty at that time.  Before a checkpoint can end successfully, all of those marked pages need to have been written and flushed.  Once an initially dirty page has been flushed, the WAL that protected that initial dirtying of the page is no longer needed, because the page itself is safely on disk and can speak for itself.  If the page starts out dirty and is then re-dirtied multiple times throughout the checkpoint, then the subsequent dirtying actions will each have their own WAL records, which will necessarily fall after the REDO pointer.  Those WAL records will be retained, at least until the successful completion of the next checkpoint after the current one.


MySQL does not support Window Functions(*). There is what we call "a poor man's window function" in the form of GROUP_CONCAT().

There are plenty of tricks using GROUP_CONCAT to emulate window functions. They are not as pretty (syntactically) and are sometimes too limited. I've written a few. See my blog post complaining about the missing window functions, and linking to various solutions based on GROUP_CONCAT.

In particular, Selecting a specific non aggregated column data in GROUP BY and SQL: selecting top N records per group, another solution might be of interest to you and could give you a kick start.

Things you should note about GROUP_CONCAT():

Can use DISTINCT
Can use ORDER BY ... ASC/DESC
Can set SEPARATOR
As any aggregation function - it discards NULL values; plenty tricks on that.

(*) Support for Window Functions has been added in MySQL 8


How to output to same line with relation information same table?

You can get this result by joining the table to itself.

select c.dokumentgruppe
    + ', ' + convert(varchar(10), c.dokumentgruppenr)
    + ', ' + 'Parent:' + isnull(p.dokumentgruppe,'')
    + ', ' + convert(varchar(10), c.parentgruppenr)
    as Dokumentgruppe
from dbo.dokumentgruppe as c
  left join dbo.dokumentgruppe as p on c.parentgruppenr=p.dokumentgruppenr
where c.aktiv = '1'
order by c.Dokumentgruppe

This is an adjacency list style heirarchy, you can find a good introduction to them here: Sql Server Bible Sample Chapter 17: Traversing Hierarchies


When are the unnecessary row versions being removed from the version store

Now if we commit the first transactions that starts at 12:00, no row is being removed from the version store (even pass a minute).
The question is WHY ?

Minimal condition for a version row to be garbage collected is when SQL Server determines, based on the transactional states, that this version is no longer needed.
Also, referring to my SQL Server internals notes, for management of version store, SQL Server will perform a regular cleanup every minute with a background process to reclaim all the reusable space from the version store.
You can monitor Version generation rate and version cleanup rate in SQLServer:Transactions perfmon counter to find out rate at which space is acquired and released from version store in KB/Sec.
Check : Managing TempDB in SQL Server: TempDB Basics (Version Store: Simple Example)


Multiple BIT datatype columns in the same table

For a fixed length field (such as a bit value), the field will always take up the same amount of space - in your case one bit.
The NULL bitmap is in the data row itself - this stored whether a particular field is considered NULL or not. As such the field itself only has to store two values - 1 or 0 - the NULL bitmap handles whether the field is actually set to NULL or not.
The NULL bitmap has a bit for each column in the table regardless of whether the column is nullable, and this contains  the "third value" you mention. Again the space for the null bitmap is rounded up to an integer number of bytes.
The following code demonstrates how SQL Server stores the NULL bitmap - you'll need to replace the page numbers if you run this yourself.
CREATE TABLE TestNullBitmap (
    TestText CHAR(10) NULL
    )
GO

INSERT INTO [dbo].[TestNullBitmap] ([TestText])
VALUES
('hello'),
('hello2'),
(NULL);

--Grab the page number
DBCC IND ([TestDb],TestNullBitmap,-1);
GO

--Use the page number from DBCC IND
DBCC PAGE ([TestDb],1,631032,3) WITH TABLERESULTS;
GO

For me I get the following results for the three data rows:

0000000000000000:   10000e00 68656c6c 6f202020 20200100 00        ....hello     ...
0000000000000000:   10000e00 68656c6c 6f322020 20200100 00        ....hello2    ...
0000000000000000:   10000e00 68656c6c 6f322020 20200100 01        ....hello2    ...

The NULL bitmap is the change to that very last byte value - read in Little Endian the binary representation is 0000 0001 (IE The first column is NULL)
Rather interestingly you can also see that the system has stored the value from the previous INSERT as the actual data for the fixed length field - rather than 0'ing it out.


which mysql version to use - 5.1 or 5.5?

There is no substitute of tuning MySQL. Left unconfigured, there are cases when even MySQL 4.1 outperforms MySQL 5.x on the same level playing field.

Here are my past posts on the subject

Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Feb 08, 2012 : MySQL 5.5 perfomance (StackOverflow)
Feb 22, 2012 : MySQL 5.1 vs MySQL 5.5 (5.1 twice as fast) (ServerFault)
Jul 16, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

Bottom Line

You should always use the most stable release of MySQL because there are just certain bug and features that are fixed that do not carry over to the next major release.

Note that

MySQL 5.0 is up to 5.0.96, but the bugs that MySQL 5.5 Addressed still reside in 5.0.96
MySQL 5.1 is up to 5.1.67, but the bugs that MySQL 5.5 Addressed still reside in 5.1.67

There are many exciting things that MySQL 5.5 features tuning for the following:

Multicore Processing

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance

Semisynchronous Replication

Aug 05, 2011 : Is MySQL Replication Affected by a High-Latency Interconnect?
Oct 29, 2012 : Installing and enabling both master and slave semisynchronous plugins on same server

Buffer Pool Instances : Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?


Are TEXT fields stored separately from the row in MySQL-ISAM?

In MyISAM records are stored in blocks. There are like 20 different block types, some of can be really large. Those are used for BLOB/TEXT values. I never saw that MyISAM record stores a pointer to a BLOB (but I would not insist).

In InnoDB BLOB/TEXT values are also a part of the record and stored in-page as long as total record size not more than ~7k. Otherwise 768 first bytes are stored in-page and remaining part is in external pages. (In Barracuda format it just stores a 20 bytes pointer to the external page).


convert date in string format to date datatype sql

You can use SUBSTRING to pull apart the source. Then DATEFROMPARTS will stitch them back together.
A few notes: dates are not held internally in that format, or any other date-like format. The question says the source is 6 characters but the example shows 8. If you need to find the century you'll have to decide on rules for that.


Restoring full and differential backups from production to a report server

Although you have WITH NORECOVERY on the 1st statement, the error is actually occurring since there is an existing database with the name Demo which cannot be overwritten without explicit instruction.

Try:

ALTER DATABASE Demo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE Demo
FROM DISK = 'C:\DBBackup\Demo_FULL_Sunday.bak'
WITH REPLACE, NORECOVERY;

RESTORE DATABASE Demo
FROM DISK = 'C:\DBBackup\Demo_Diff_Monday.bak'
WITH RECOVERY;

Be aware this will totally destroy the existing database and replace it with the contents of the backup file.  Ensure you do this on the correct server, since you will have no 2nd chances.


This is a very common "exam/interview question". I will answer as good as I can:

In the standard row formats for InnoDB and MyISAM (dynamic/compact) a VARCHAR(50) and a VARCHAR(255) will store the string text in the same way- 1 byte for the length and the actual string with between 1 and 4 bytes per character (depending on the encoding and the actual character stored).

In fact, if I remember correctly, I recall someone modifying the data dictionary with an hexadecimal editor in order to change something like a VARCHAR(50) into a VARCHAR(100), so it could be done dynamically (normally, that requires a table reconstruction). And that was possible, because the actual data was not affected by that change.

That is not true with VARCHAR(256), because then 2 bytes (at least) for the length are always required.

So, that means that we should always do VARCHAR(255), shouldn't we? No. There are several reasons.

While InnoDB may store a varchar in a dynamic way, that is not true for other engines. MyISAM has a fixed row size format, and MEMORY tables are always fixed in size. Should we care about those other engines? Yes, we should, because even if we do not use them directly, MEMORY tables are very commonly used for intermediate results (temporary tables on memory), and as the results are not known beforehand, the table has to be created with the maximum size possible -VARCHAR(255) if that is our type. If you can think about the wasted space, if we are using MySQL's 'utf8' charset encoding, MEMORY will reserve 2 bytes for the length + 3 * 255 bytes per row (for values that may only take a few bytes on InnoDB). That is almost 1GB on a 1 million table -only for the VARCHAR. Not only this causes unnecessary memory stress, it may provoke the actions to be performed on disk, potentially slowing it down thousands of times. All of that because of a poor selection of its defined data type (independently of the contents).

It has some consequences for InnoDB, too. Index size is restricted to 3072 bytes and single column indexes, to 767 bytes*. So, it is very likely that you won't be able to index fully a VARCHAR(255) field (assuming you use utf8 or any other variable length-encoding).

Additionally, the maximum inline row size for InnoDB is half a page (around 8000 bytes), and variable-lenght fields like BLOB or varchar, can be stored off-page if they do not fit on the half-page. That has some consequences in performance (sometimes good, sometimes bad, depending on the usage) that cannot be ignored. This caused some weirdness between the COMPACT and DYNAMIC formats. See, for example: error 1118: row size too large. utf8 innodb

Last but not least, as @ypercube has reminded me, more than 1 byte for the length may be required even if you are using VARCHAR(255), because the definition is in characters, while the length stores bytes. For example REPEAT('ñ', 255) has more than 2^255 bytes in utf8, so it would require more than 1 byte for storing its length:

mysql> SELECT LENGTH(REPEAT('ñ', 255));
+---------------------------+
| LENGTH(REPEAT('ñ', 255))  |
+---------------------------+
|                       510 |
+---------------------------+
1 row in set (0.02 sec)

mysql> SELECT CHAR_LENGTH(REPEAT('ñ', 255));
+--------------------------------+
| CHAR_LENGTH(REPEAT('ñ', 255))  |
+--------------------------------+
|                            255 |
+--------------------------------+
1 row in set (0.00 sec)

So the general piece of advice is to use the smallest type possible, because it can potentially create performance or management problems otherwise. A VARCHAR(100) is better than VARCHAR(255) (although a VARCHAR(20) would be better), even if you do not know the exact length. Try to be conservative because, unless the table is too large, you can always change the definition later.

Update: Because the exploding popularity of variable-length strings, for example, with the usage of emojis, Oracle has been pushing for improved performance for those cases. In the latest MySQL versions (5.6, 5.7), InnoDB has been set as the default engine for both intrinsic and explicit temporary tables meaning that variable-length fields are now first-class citizens. That means that there may be less reasons to have very constrained character lengths (but those still exist).

(*) Second Update: large_prefix_index is now enabled by default on the latest MySQL versions (8.0), but that is still true for older versions or if you are using lagacy innodb file/row formats (other than dynamic or compressed), but now by default, single column indexes can be up to those 3072 bytes.


Your question is tagged database-agnostic, so I'll provide an answer that applies to SQL Server (though I suspect that it goes for most modern platforms).

For SQL Server, the answer is no; DDL statements do not automatically COMMIT or ROLLBACK an open transaction. It normally places a schema lock on the object(s) you're touching with the DDL statement, which is held until the end of the transaction.

In fact, transactions are very useful whenever you want to combine DDL and DML statements, in order to maintain atomic business rules in your database.

Don't know about other database platforms, though.


What is the default value of ANSI Padding Enabled, and what should value should I set it to?

Per @MartinSmith's comment, the database setting is overwritten by the connection setting most of the times.

ADO.NET, ODBC, OLE DB and SSMS all set this to ON. Additional libraries/tools and settings are listed here.


Overloaded Constructor for SQL Server stored procedure?

<TL;DR>

Is overloading supported in SQL Server: Nope. But even if it was, you would likely have issues related to source control and/or debugging.
Can a subset of parameters be passed in, altering the behavior: Yep, but you will have a single piece of code that won't be easy to work with or maintain.

Conclusion: Just create one Stored Procedure or Function per behavior. It will be:

easier to work with via both T-SQL and app code
easier to maintain (hence less error-prone)
easier to debug
easier to integrate into source control
at least one thing you won't be blamed for after you leave or change teams / departments ;-)

There seems to be some confusion regarding how to interpret this Question with regards to the ultimate goal, and assumptions are being made on both sides, so let's start by clarifying what we are actually talking about.

Overloading

Overloading consists all, not some, of the following:

Code separation: Completely separate functions / methods.
Exact same function / method name for these separate pieces of code.
Unique number of, and/or combination of datatypes for, input parameters.
Execution of the shared name routes to the proper function / method based upon the signature -- the unique variation of input parameters.
Each separate function / method of the same name but differing input parameters can have a different datatype for the return value (note: this is merely an ability, not a requirement like the items noted above).

With this in mind, overloading is not possible (to any degree) in SQL Server, either for Stored Procedures or Functions (Scalar or TVF). Stored Procedures and Functions must have unique names (at least within a particular Schema). However, this restriction is just for user-defined objects as quite a few built-in Functions make use of overloading.

SQL Server does have "numbered" Stored Procedures (as pointed out in @Aaron's answer), but these should not be confused with overloads (not even partially) because they are missing everything that makes overloads different from those same, separate pieces of code having unique names:

While the "base" name of the Stored Procedure can be the same, this base name cannot be used to reference the code for execution; you are required to include the "number" (well, for ;1 it is optional), making it no different than having a vastly different name. Meaning, executing that name without any ;<number> following the name will always route to ProcName;1. Hence the "number" is actually part of the name.

In fact, the only time you can refer to the group without using the ;<number> syntax is in a DROP PROCEDURE statement, and then that is the only way to reference the group. Meaning, you cannot DROP PROCEDURE ProcName;3 but instead can only drop all of them.
The variations of input parameters are not required to be unique, as shown in this example:

CREATE PROCEDURE #NumberedProc (@Input INT)
AS
SELECT @Input AS [inp];
GO
CREATE PROCEDURE #NumberedProc;2 (@Input INT)
AS
SELECT @Input * 2 AS [h], 2 AS [g];
GO

EXEC #NumberedProc 55;

EXEC #NumberedProc;2 55;

Automatic routing to the appropriate module is not available, because it cannot be available due to the signatures being allowed to be the same. Hence the version number is required when executing.

True overloading is allowed in PostgreSQL for Functions, and it does work, but it also causes more pain than it is worth. It did, to a small degree, simplify the code from the app layer perspective, but it was very difficult to both manage in source control (assuming each object has a script named for the name of the object) as well as debug (you had to hunt down the particular signature from the error message). We eventually did away with the overloads and made every unique signature have a unique object name ;-).

Passing in a subset of parameters resulting in different behavior

You are allowed to have optional parameters that have default values (and the default can be NULL) for both Stored Procedures and Functions, but this is still a single Stored Procedure or Function. Meaning:

There is no separation of code; you will need a series of IF statements to properly route to the desired functionality. Assuming that the default values are set to NULL, you would be checking for which input parameters are NOT NULL.
Return types:

Scalar Functions can only have one return datatype. You could return SQL_VARIANT to avoid a specific return type, but there are issues with this approach: performance degradation and LOB types are not supported (i.e. VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), and XML).
Table-Valued Functions can only have one return type, and it is determined at compile time.
Stored Procedures do allow for defining the result set structure at run-time, but that would be a very confusing interface to code to in the app layer.
Stored Procedures also allow for OUTPUT parameters, but you would need at least one per each datatype that you will be returning, and the calling code would need to know which one(s) to check based on which input parameters are being passed in. This would also be a confusing interface to code to.

Because this is a single object, you don't have the management and debugging issues like you would with true overloads, but you now have a large, messy, convoluted Stored Procedure or Function that is also hard to maintain, just for different reasons.

It would be something like:

CREATE PROCEDURE dbo.DoTooMuchInOneProcedure
(
  @Int1 INT = NULL,
  @Int2 INT = NULL,
  @String1 NVARCHAR(50) = NULL,
  @IntOut INT = NULL OUTPUT,
  @StringOut NVARCHAR(50) = NULL OUTPUT
)
AS
SET NOCOUNT ON;

IF (@Int1 IS NOT NULL AND @Int2 IS NULL AND @String1 IS NULL)
BEGIN
  SET @IntOut = @Int1;
  RETURN;
END;

IF (@Int1 IS NOT NULL AND @Int2 IS NOT NULL)
BEGIN
  SET @IntOut = @Int1 + @Int2;
  RETURN;
END;

IF (@Int1 IS NOT NULL AND @String1 IS NOT NULL)
BEGIN
  SET @StringOut = @String1 + CONVERT(NVARCHAR(50), @Int1);
  RETURN;
END;


Recovering a dropped table in PostgreSQL

You can't, the file has been physically deleted from the disk. I'm assuming your real question is about a non-trivial table you care about, not a toy-table you can re-create at will.

If so: Restore from a backup.

If you don't have a backup, shut the computer down now and contact a professional data recovery team to see if they can recover the table from the empty space in the file system if it hasn't been over-written yet. Expect to pay several thousand dollars for the service.

Since the disk isn't phsysically damaged you can try some recovery yourself: Take a disk image of the drive by plugging it into another machine. Do not take a file-system-level copy, you need a raw image of the volume/partition the file system is on. You can then attempt to locate the table in the disk image, but be aware that it's likely fragmented and that the inode information that would identify its location is typically gone. Details would require knowledge of the operating system and file system the database is on.

More on this topic here.

Demo

Run your setup:

regress=# CREATE SCHEMA ketoan_vn;
CREATE SCHEMA
regress=# CREATE TABLE ketoan_vn.Customers
regress-# (
regress(#   CustomerId character varying(10) NOT NULL,
regress(#   CustomerName character varying(150),
regress(#   CONSTRAINT Customers_PK_CustomerID PRIMARY KEY (CustomerId)
regress(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "customers_pk_customerid" for table "customers"
CREATE TABLE
regress=# INSERT INTO ketoan_vn.customers(
regress(#             customerid, customername)
regress-#     VALUES ('C0001','Marry'),
regress-#     ('C0002','Tom');
INSERT 0 2

Get the location of the table file on disk:

regress=# SELECT
  COALESCE(
    pg_catalog.pg_tablespace_location(pg_tablespace.oid),
    current_setting('data_directory')||'/base/'
  )
  || (SELECT oid from pg_database where datname = current_database() )
  || '/'
  || relfilenode
  AS path_to_table_file
FROM pg_class
INNER JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid)
LEFT OUTER JOIN pg_tablespace on (reltablespace = pg_tablespace.oid)
WHERE nspname = 'ketoan_vn' AND relname = 'customers';

          path_to_table_file
------------------------------------------
 /var/lib/pgsql/9.2/data/base/43720/43733
(1 row)

Confirm the location on the shell:

$ sudo ls -l /var/lib/pgsql/9.2/data/base/43720/43733
-rw-------. 1 postgres postgres 8192 Apr 18 14:25 /var/lib/pgsql/9.2/data/base/43720/43733

Now back in psql, drop the table:

drop table ketoan_vn.Customers;

and confirm it's now either gone or zero length:

$ sudo ls -l /var/lib/pgsql/9.2/data/base/43720/43733
-rw-------. 1 postgres postgres 0 Apr 18 14:28 /var/lib/pgsql/9.2/data/base/43720/43733

(the actual file will be removed by the DB at some later time, but it's been truncated, or it'll get re-used. Either is very bad for your data.)


Index on integer column not helping. Help? (EXPLAIN ANALYZE output inside)

With only 5 values the index isn't honing to be very selective for joining so it'll pretty much always index scan unless looking for just one value.

That query plan/anal output suggests that it thinks half the work is in matching the rows there with the CTE, could you try run the group+aggregate over the whole lot. If it is index scanning anyway you might find it faster to do that and then filter out the two you are not looking for results for.


Backup Plan on Always On using Maintenance Wizard doing Tlogs on Primary and Secondary

If you tick the "For availability databases, ignore replica priority for backup and backup on primary settings" option, then the maintenance plan jobs will perform the backup on a secondary (or any replica the job is executed on).

This option is used to ignore the AUTOMATED_BACKUP_PREFERENCE option for the Availability Group. Since your preference is set to Primary and you don't want backups on the secondary, disable the "For availability databases, ignore replica priority for backup and backup on primary settings" option and your maintenance plan jobs will not perform a backup on the secondary replicas.

Keep in mind that the backup preference option in the Availability Group will not prevent backups from being taken on a secondary. This option simply ensures that sys.fn_hadr_backup_is_preferred_replica will return the correct value, according to the configured preferences, on each replica. It is up to you to ensure this setting is validated and honoured by your backup application.

Maintenance plans generate a T-SQL command under the hood that has an IF statement that checks this function, unless you have enabled "For availability databases, ignore replica priority for backup and backup on primary settings".

Further Info: https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/02/maintenance-plan-does-not-backup-database-or-log-of-database-defined-in-availability-group/

P.S. I would recommend taking a look at Ola Hallengren's excellent SQL Maintenance Solution as an alternative to the built-in Maintenance Plans. Ola's solution has built-in intelligence for handling Availability Group databases in accordance with your backup preferences, as well as many other useful features.