Incorrect syntax near ')' when executing multiple select statement

One important thing to consider: CASE in T-SQL is an expression that returns a value, not control-of-flow logic like you might find in other languages (e.g. VB treats it like a switch()). In SQL Server, it can't be used this way; you can't say:

CASE WHEN something THEN
  -- run some query
ELSE
  -- run some different query

All output from the CASE expression must happen at the same scope and within the same query. I've written quite a lengthy article about some of the more common misconceptions about CASE here:

Dirty Secrets of the CASE Expression

In your scenario, if these are the only two potential outcomes, then you can probably follow @billspat's advice and just move the location of your CASE expression into the WHERE clause of a single query.

If your actual scenario is more complex (like you have 20 or 60 or 7000 different @id values that can lead to different forms of that WHERE clause, dynamic SQL might be a better solution (SQL Server will have a very hard time coming up with a single execution plan that is optimal for all variations of the query). Something like this:

CREATE PROCEDURE dbo.employee -- this is an odd name for a procedure
    @id   int,
    @year int
AS
BEGIN
  SET NOCOUNT ON; -- should always use this

  DECLARE @sql NVARCHAR(MAX) = N'SELECT e.name, d.roles, d.dayoff
    FROM dbo.employee AS e -- use logical aliases! What sense do a/b make?
    INNER JOIN dbo.department AS d
    ON e.id = d.id -- this join condition makes little sense
    WHERE d.years IN (@year)';

  IF @id = 1 THEN
    SET @sql = @sql + N' OR d.roles IS NOT NULL';
  IF @id = 2 THEN
    SET @sql = @sql + N' OR d.dayoff IS NOT NULL';

  EXEC sys.sp_executesql @sql, N'@year int', @year;
END
GO

I talk about this in a little more detail here:

The "kitchen sink" procedure

You could add logic to also dictate which output columns to show, but the application layer should be able to ignore output columns based on the @id value it passed in.

And please, always use the schema prefix.


Do I need to use -f to fix insufficient max server memory settings?

I was able to reset the memory by establishing a DAC connection and without ever resorting to the -f startup parameter. However, I had to be persistent:

At 128 MB, you will get all kinds of errors in the error log:

SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
(repeat for other caches)
Failed allocate pages: FAIL_PAGE_ALLOCATION 1
Verbose DBCC MEMORYSTATUS dumps
errors about audit / extended events data potentially lost
There is insufficient system memory in resource pool 'internal/default' to run this query.

In some cases, the errors will be persistent enough to simply shut SQL Server down (and nothing will be recorded in the log). This happened twice during my experiments, and may also be influenced by the size of the databases attempting to be recovered on subsequent restarts. You should check whether any of the errors you receive while attempting to establish a DAC connection actually stem from the service not running (or perhaps in the process of shutting down).
In each case, I had to go to Configuration Manager and restart the service. On the second try, I think the service shut down (or was in the process) before I established the DAC connection. On the third try, there was a lot less recovery action on startup, and I was able to establish a DAC query window and adjust max server memory, then when I restarted, everything went back to normal.
Note that I was using 11.0.2376, and I recommend you perform this kind of edge case testing on builds you are likely to run in production. (Things like this may have been fixed post-RTM, and not all fixes are public.) If you're running RTM in production, then I kind of take that back, and will suggest that you reconsider that approach.

If you find that in your case you are also experiencing this "oops, SQL Server is no longer running" symptom, you could probably attach a debugger and determine exactly how SQL Server is shutting down.
So, can you do it without -f? Yes. Is it any cleaner? Only if you're really lucky. If your databases are large you are more likely to want to have a predictable number of restarts (2) because the recovery process could be a lot more involved than mine
Of course, an even cleaner thing would be to not mess around with max server memory using the GUI or ad hoc scripts that are prone to fudges - in production you want use a second set of eyes and have proper, validated, source controlled scripts that are much more predictable than "playing around." :-)


I have a hard time understanding how exactly your JSON values looks like, but I think you want something like this:

select ud.*
from userdata ud
where exists (select *
              from jsonb_object_keys(ud.data) as t(ky)
              where t.ky like 'photo%');


The slowlog is my favorite means of quickly identifying performance problems.  (I don't care for the performance_schema.)  Set long_query_time=1 globally, turn on the slowlog, wait a day, then look at the log

The slowlog can be sent to a file.  If you don't have access to the filesystem, what version of MySQL you are using?  In newer versions, the slowlog can be sent to a table, thereby avoiding the need for access to the disk.

If possible, use pt-query-digest to summary the slowlog file.  The first few queries will be the "worst".  We can help you speed them up, possibly by adding a "composite" index, possibly by modifications to the query, possibly by schema redesign, etc.


remove duplicate values in the same table

Join table to itself:

DELETE z.*
  FROM table AS w
  JOIN table AS z  ON z.name = w.name
                  AND z.id > w.id
;


The problem here is an implicit conversion to UNIQUEIDENTIFIER type. There is a warning about it in the execution plan, and it is connected with Data Type Precedence.

Your table is using the wrong data type to store GUIDs. If col1 were correctly typed as uniqueidentifier, your problem would never have arisen. The string representation of a GUID has a maximum length of 36 (not 37!) characters, which is much less efficient than using uniqueidentifier (16 bytes). Note also that NEWID() returns a uniqueidentifier, not any sort of string.

I added some hints to my queries below to get an identical execution plan.

You can avoid this if you assign NEWID() to a variable but you still have to declare VARCHAR(37) variable or just cast NEWID() to VARCHAR(37) in the where clause.

Here are a few queries that show us how it works:

-- Query 1
SELECT * FROM Test WITH(INDEX=[IX_RegularIndex]) WHERE col1=NEWID()
GO

-- Query 2
DECLARE @id UNIQUEIDENTIFIER = NEWID()

SELECT * FROM Test WITH(INDEX=[IX_RegularIndex]) WHERE col1=@id
GO

-- Query 3
DECLARE @id VARCHAR(37) = NEWID()

SELECT * FROM Test WITH(INDEX=[IX_RegularIndex]) WHERE col1=@id
GO

-- Query 4
SELECT * FROM Test WITH(INDEX=[IX_RegularIndex]) WHERE col1=cast(NEWID() as varchar(37))
GO

Related Q & A concerning the number of times the NEWID function is evaluated:

NEWID() In Joined Virtual Table Causes Unintended Cross Apply Behavior


The mysqldump utility can dump the following ways from a MySQL Instance:

All Databases
One Whole Database
Part of a Database

It is possible to mysqldump a space-separated list of tables from one Database

One Whole Table

It is not possible to mysqldump a table from one Database and a table from another

Part of a Table Using --where Option
Stored Procedures
Schema Only
Data Only

You cannot mysqldump two tables from two different schema. You must script the tables you want dumps one at a time.

Here is a script to dump every table from a MySQL Instance into separate files

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables"
SQL="${SQL} WHERE table_schema NOT IN ('information_schema','mysql')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > /tmp/ListOfTables.txt
COMMIT_COUNT=0
COMMIT_LIMIT=10
for DBTB in `cat /tmp/ListOfTables.txt`
do
    DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
    mysqldump ${MYSQL_CONN} --hex-blob --triggers ${DB} ${TB} > ${DB}_${TB}.sql &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

In your case, you can create the /tmp/ListOfTables.txt file manually. In order to inject use dbname, just create the dump file with the use dbname as the first line. Then, append the output of mysqldump to it. Keep in mind that each table is described as dbname.tablename:

rm -f /tmp/ListOfTables.txt
echo "company.loadme" >> /tmp/ListOfTables.txt
echo "new_company.newload" >> /tmp/ListOfTables.txt
MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
COMMIT_COUNT=0
COMMIT_LIMIT=10
for DBTB in `cat /tmp/ListOfTables.txt`
do
    DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
    echo "use ${DB}" > ${DB}_${TB}.sql
    mysqldump ${MYSQL_CONN} --hex-blob --triggers ${DB} ${TB} >> ${DB}_${TB}.sql &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Give it a Try !!!


Nope. If you order by a not indexed column then the server HAS to load all the results into memory and can only then start sorting them. That is heavy - not sure how mySql does it, but on SqlServer that will also tax the tempdb for the temporary holding of the data. If it is a lot of data that may be super slow due to memory flowing over onto the hard disc.

Physics and logic can not be cheated, sadly.


Stored Procedure to update an adjacency Model to Nested Sets Model

Finally I found the solution but it needed some optimization and tweaks to work with my case and I added sorting with ID to get the tree sorted too,
the answer is mainly got from here so credit goes to @deceze,

CREATE DEFINER=`root`@`localhost` PROCEDURE `tree_recover`()
    MODIFIES SQL DATA
BEGIN

    DECLARE currentId, currentParentId  CHAR(36);
    DECLARE currentLeft                 INT;
    DECLARE startId                     INT DEFAULT 1;

    # Determines the max size for MEMORY tables.
    SET max_heap_table_size = 1024 * 1024 * 512;

    START TRANSACTION;

    # Temporary MEMORY table to do all the heavy lifting in,
    # otherwise performance is simply abysmal.
   DROP TABLE IF EXISTS `tmp_tree`;

   CREATE TABLE `tmp_tree` (
        `id`        bigint(36) NOT NULL DEFAULT '0',
        `parent` char(36)          DEFAULT NULL,
        `lft`       int(11)  unsigned DEFAULT NULL,
        `rgt`      int(11)  unsigned DEFAULT NULL,
        PRIMARY KEY      (`id`),
        INDEX USING HASH (`parent`),
        INDEX USING HASH (`lft`),
        INDEX USING HASH (`rgt`)
    ) ENGINE = MEMORY
    SELECT `id`,
           `parent`,
           `lft`,
           `rgt`
    FROM   `tree`;

    # Leveling the playing field.
    UPDATE  `tmp_tree`
    SET     `lft`  = NULL,
            `rgt` = NULL;

    # Establishing starting numbers for all root elements.
    WHILE EXISTS (SELECT * FROM `tmp_tree` WHERE `parent` = 0 AND `lft` IS NULL AND `rgt` IS NULL LIMIT 1) DO

        UPDATE `tmp_tree`
        SET    `lft`  = startId,
               `rgt` = startId + 1
        WHERE  `parent` = 0
          AND  `lft`       IS NULL
          AND  `rgt`      IS NULL
          ORDER BY `id` ASC
        LIMIT  1;

        SET startId = startId + 2;

    END WHILE;

    # Switching the indexes for the lft/rgt columns to B-Trees to speed up the next section, which uses range queries.
    DROP INDEX `lft`  ON `tmp_tree`;
    DROP INDEX `rgt` ON `tmp_tree`;
    CREATE INDEX `lft`  USING BTREE ON `tmp_tree` (`lft`);
    CREATE INDEX `rgt` USING BTREE ON `tmp_tree` (`rgt`);

    # Numbering all child elements
    WHILE EXISTS (SELECT * FROM `tmp_tree` WHERE `lft` IS NULL LIMIT 1) DO

        # Picking an unprocessed element which has a processed parent.
        SELECT     `tmp_tree`.`id`
          INTO     currentId
        FROM       `tmp_tree`
        INNER JOIN `tmp_tree` AS `parents`
                ON `tmp_tree`.`parent` = `parents`.`id`
        WHERE      `tmp_tree`.`lft` IS NULL
          AND      `parents`.`lft`  IS NOT NULL
        ORDER BY `tmp_tree`.`id` DESC
        LIMIT      1;

        # Finding the element's parent.
        SELECT  `parent`
          INTO  currentParentId
        FROM    `tmp_tree`
        WHERE   `id` = currentId;

        # Finding the parent's lft value.
        SELECT  `lft`
          INTO  currentLeft
        FROM    `tmp_tree`
        WHERE   `id` = currentParentId;

        # Shifting all elements to the right of the current element 2 to the right.
        UPDATE `tmp_tree`
        SET    `rgt` = `rgt` + 2
        WHERE  `rgt` > currentLeft;

        UPDATE `tmp_tree`
        SET    `lft` = `lft` + 2
        WHERE  `lft` > currentLeft;

        # Setting lft and rgt values for current element.
        UPDATE `tmp_tree`
        SET    `lft`  = currentLeft + 1,
               `rgt` = currentLeft + 2
        WHERE  `id`   = currentId;

    END WHILE;

    # Writing calculated values back to physical table.
    UPDATE `tree`, `tmp_tree`
    SET    `tree`.`lft`  = `tmp_tree`.`lft`,
           `tree`.`rgt` = `tmp_tree`.`rgt`
    WHERE  `tree`.`id`   = `tmp_tree`.`id`;

    COMMIT;

    DROP TABLE `tmp_tree`;

END


If you have a table of numbers, or can access the built-in table in master:
SELECT
    T1.id,
    Expanded.[date],
    Expanded.frequency
FROM
(
    -- Add next date for the current id
    SELECT
        T.*,
        next_date =
            LEAD(T.[date], 1) OVER (
                PARTITION BY T.id
                ORDER BY T.[date])
    FROM dbo.t AS T
) AS T1
CROSS APPLY
(
    -- All month ends >= the current date and < next date
    SELECT
        [date] = EOMONTH(T1.[date], SV.number),
        frequency = IIF(SV.number = 0, T1.frequency, 0),
        SV.number
    FROM master.dbo.spt_values AS SV
    WHERE
        SV.[type] = N'P'
        AND SV.number < ISNULL(DATEDIFF(MONTH, T1.[date], T1.next_date), 1)
) AS Expanded
ORDER BY
    T1.id,
    T1.[date],
    Expanded.number;

The output matches that of the recursive CTE you provided (see demo link below):

id
date
frequency

1
2012-04-30
5

1
2012-05-31
0

1
2012-06-30
4

1
2012-07-31
0

1
2012-08-31
0

1
2012-09-30
0

1
2012-10-31
0

1
2012-11-30
0

1
2012-12-31
0

1
2013-01-31
0

1
2013-02-28
0

1
2013-03-31
0

1
2013-04-30
0

1
2013-05-31
0

1
2013-06-30
0

1
2013-07-31
25

2
2012-04-30
7

2
2012-05-31
4

2
2012-06-30
1

2
2012-07-31
6

Explanation
Logically, each row from T1 is expanded by the APPLY into the set of month-ends that ought to be present. Let's look at one particular row in the source table:

id
date
frequency

1
2012-04-30
5

Adding the LEAD in the table expression aliased to T1 gives:

id
date
frequency
next_date

1
2012-04-30
5
2012-06-30

This row is passed to the APPLY:
SELECT
    [date] = EOMONTH(T1.[date], SV.number),
    frequency = IIF(SV.number = 0, T1.frequency, 0),
    SV.number
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number < ISNULL(DATEDIFF(MONTH, T1.[date], T1.next_date), 1)

The references to columns in T1 are outer references. Substituting the values from the current T1 row for [date], next_date, and frequency gives:
SELECT
    [date] = EOMONTH('2012-04-30', SV.number),
    frequency = IIF(SV.number = 0, 5, 0),
    SV.number
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number < ISNULL(DATEDIFF(MONTH, '2012-04-30', '2012-06-30'), 1)

The ISNULL expression determines how many rows are returned from our table of numbers. In the current iteration the ISNULL expression evaluates to 2, so the rows returned from the numbers table have values 0 and 1.
The APPLY table expression as a whole returns:

date
frequency
number

2012-04-30
5
0

2012-05-31
0
1

Notice:

When number = 0, we are dealing with a row that exists in T1, so we just use the frequency given.
When number > 0, the row does not exist in T1 so we need to return a zero for frequency.

That gives us the results needed for the single row we chose from T1. We can do the same for the next row from T1 (substituting values as before), and so on until the result is complete. For more on how APPLY works logically, see my article Understanding and Using APPLY.
The only special handling is for the last row per id, where the LEAD returns NULL. In that case, we only need one row from the numbers table (value zero) to process the current row from T1.

The built-in table is sufficient for a range of dates from '2000-01-01' to '2170-08-01'.
Try the db<>fiddle demo


Read data with group by 100000 distinct values on 36 million rows

Shrink datatypes --

INT takes 4 bytes; probably overkill for your data.
It is usually ill-advised to split a DATE into multiple columns.
Normalize any repeated VARCHARs.

You have nothing limiting the number of rows you need to look at, so the query must read the entire table.  Unless you have magical disks, you must have enough RAM to hold the entire table, do you?

Since you have nested GROUP BYs, there will need to be one or two temp tables, each with lots of rows, plus one or two sorts.

Your only hope to avoid "sub-par" performance by any database engine is to reformulate your query.

Since you are grouping by month, are the previous month's data static?  That is, could you summarize each month at the beginning of the next month, then use the Summary table?

It might be better to summarize daily, thereby avoiding the moderately large monthly query.

Such a summary table would probably look very much like the output of the derived table (in the from cause).  Probably this would be best:  PRIMARY KEY(p_s, bd, month, si).

Bug?  You are grouping by month.  Does that mean you are combining last July with this July?  Or did you mean to include year in that?  At which point, it is even more compelling to use DATE instead of two columns.


Try the following:

select office_id,
[1] as value_1, [2] as value_2,
[3] as value_3, [4] as value_4
from
(
  select office_id, setting_value,
    row_number() over(partition by office_id order by office_id) rnk
  from office_settings
) d
pivot
(
  max(setting_value)
  for rnk in ([1], [2], [3], [4])
) piv;

Or without using PIVOT (generates the same execution plan):

WITH Numbered AS
(
    SELECT
        OS.office_id,
        OS.setting_value,
        row_num = ROW_NUMBER() OVER (
            -- Note: non-deterministic
            PARTITION BY OS.office_id
            ORDER BY OS.office_id)
    FROM dbo.office_settings AS OS
)
SELECT
    N.office_id,
    value_1 = MAX(CASE WHEN N.row_num = 1 THEN N.setting_value ELSE NULL END),
    value_2 = MAX(CASE WHEN N.row_num = 2 THEN N.setting_value ELSE NULL END),
    value_3 = MAX(CASE WHEN N.row_num = 3 THEN N.setting_value ELSE NULL END),
    value_4 = MAX(CASE WHEN N.row_num = 4 THEN N.setting_value ELSE NULL END)
FROM Numbered AS N
GROUP BY
    N.office_id;

With dynamic sql (useful when number of pivot columns varies within a search window):

declare @pivcols as varchar(200);

select  @pivcols = stuff((select distinct '],[' + cast(row_number()
over(partition by office_id order by office_id) as varchar(10))
from office_settings
for xml path('')), 1, 2, '') + ']';

declare @pivquery varchar(500);

set @pivquery = 'Select office_id, '+
@pivcols +'
from
(select office_id, setting_value,
 row_number() over(partition by office_id order by office_id) rnk
 from office_settings
) as d
PIVOT
(
max(setting_value)
for rnk in ( '+ @pivcols +' )
) AS pvt ';

EXECUTE(@pivquery);


I've found the root cause and solution.

Ola Hallengren couldn't get to the back-up encryption certificate because doesn't know the Database Master Key (DMK) encryption password. The DMK can also be encrypted using the Service Master Key (SMK), which is automatically opened for a connection with sufficient privileges.

I've added SMK encryption to the DMK, which allows Ola's scripts to access the back-up encryption certificate.

I guess it only works if it's turtles all the way down. :-P


How does case-insensitive collation work?

indexing against case insensitive strings yet the case of the data is persisted. How does this actually work?

This is actually not a SQL Server specific behavior, it's just how these things work in general.

So, the data is the data. If you are speaking about an index specifically, the data needs to be stored as it is else it would require a look-up in the main table each time to get the actual value, and there would be no possibility of a covering index (at least not for string types).

The data, either in the table/clustered index or non-clustered index, does not contain any collation / sorting info. It is simply data. The collation (locale/culture rules and sensitivities) is just meta data attached to the column and used when a sort operation is called (unless overridden by a COLLATE clause), which would include the creation/rebuild of an index. The rules defined by a non-binary collation are used to generate sort-keys, which are binary representations of the string (sort keys are unnecessary in binary collations). These binary representations incorporate all of the locale/culture rules and selected sensitivities. The sort-keys are used to place the records in their proper order, but are not themselves stored in the index or table. They aren't stored (at least I haven't seen these values in the index and was told that they aren't stored) because:

They aren't truly needed for sorting since they would merely be in the same order as the rows in the table or index anyway. But, the physical order of the index is just sorting, not comparison.
While storing them might make comparisons faster, it would also make the index larger as the minimum size for a single character is 5 bytes, and that's just "overhead" (of the sort key structure). Most characters are 2 bytes each, plus 1 byte if there's an accent, plus 1 byte if it's upper-case. For example, "e" is a 7-byte key, "E" and "é" are both 8 bytes, and "É" is a 9-byte key. Hence, not worth storing these in the end.

There are two types of collations: SQL Server and Windows.

SQL Server

SQL Server collations (those with names starting with SQL_) are the older, pre-SQL Server 2000 way of sorting/comparing (even though SQL_Latin1_General_CP1_CI_AS is still the installation default on US English OSes, quite sadly). In this older, simplistic, non-Unicode model, each combination of locale, code page, and the various sensitivities are given a static mapping of each of the characters in that code page. Each character is assigned a value (i.e. sort weight) to denote how it equates with the others. Comparisons in this model appear to do a two-pass operation:

First, it removes all accents (such that " ü " becomes " u "), expands characters like " Æ " into " A " and " E ", then does an initial sort so that words are in a natural order (how you would expect to find them in a dictionary).
Then, it goes character by character to determine equality based on these underlying values per each character. This second part is what mustaccio is describing in his answer.

The only sensitivities that can be adjusted in these collations are: "case" and "accent" ("width", "kana type" and "variation selector" are not available). Also, none of these collations support Supplementary Characters (which makes sense as those are Unicode-specific and these collations only apply to non-Unicode data).

This approach applies only to non-Unicode VARCHAR data. Each unique combination of locale, code page, case-sensitivity, and accent-sensitivity has a specific "sort ID", which you can see in the following example:

SELECT COLLATIONPROPERTY(N'SQL_Latin1_General_CP1_CI_AS', 'SortID'), -- 52
       COLLATIONPROPERTY(N'SQL_Latin1_General_CP1_CS_AS', 'SortID'), -- 51
       COLLATIONPROPERTY(N'Latin1_General_100_CI_AS',     'SortID'); --  0

The only difference between the first two collations is the case-sensitivity. The third collation is a Windows collation and so does not have a static mapping table.

Also, these collations should sort and compare faster than the Windows collations due to being simple lookups for character to sort weight. However, these collations are also far less functional and should generally be avoided if at all possible.

Windows

Windows collations (those with names not starting with SQL_) are the newer (starting in SQL Server 2000) way of sorting/comparing. In this newer, complex, Unicode model, each combination of locale, code page, and the various sensitivities are not given a static mapping. For one thing, there are no code pages in this model. This model assigns a default sort value to each character, and then each locale/culture can re-assign sort values to any number of characters. This allows multiple cultures to use the same characters in different ways. This does have the affect of allowing for multiple languages to be sorted naturally using the same collation if they do not use the same characters (and if one of them does not need to re-assign any values and can simply use the defaults).

The sort values in this model are not single values. They are an array of values that assign relative weights to the base letter, any diacritics (i.e. accents), casing, etc. If the collation is case-sensitive, then the "case" portion of that array is used, otherwise it's ignored (hence, insensitive). If the collation is accent-sensitive, then the "diacritic" portion of the array is used, otherwise it's ignored (hence, insensitive).

Comparisons in this model are a multi-pass operation:

First, the string is normalized so that various ways of representing the same character will equate. For example, " ü " could be a single character / code point (U+00FC). You could also combine a non-accented " u " (U+0075) with a Combining Diaeresis " ̈ " (U+0308) to get: " ü ", which not only looks the same when rendered (unless there is a problem with your font), but is also considered to be the same as the single character version (U+00FC), unless using a binary collation (which compares bytes instead of characters). Normalization breaks the single character into the various pieces, which includes expansions for characters like " Æ " (as noted above for SQL Server collations).
The comparison operation in this model goes character by character per each sensitivity. Sort keys for the strings are determined by applying the appropriate elements of each characters collation array of values based on which sensitivities are "sensitive". The sort key values are arranged by all of the primary sensitivities of each character (the base character), followed by all of the secondary sensitivities (diacritic weight), followed by the case weight of each character, and so on.
Sorting is performed based on the calculated sort keys. With each sensitivity grouped together, you can get a different sort order than you would with an equivalent SQL Server collation when comparing strings of multiple characters, and accents are involved, and the collation is accent-sensitive (and even more so if the collation is also case-sensitive).

For more details on this sorting, I will eventually publish a post that shows the sort key values, how they are calculated, the differences between SQL Server and Windows collations, etc. But for now, please see my answer to: Accent Sensitive Sort (please note that the other answer to that question is a good explanation of the official Unicode algorithm, but SQL Server instead uses a custom, though similar, algorithm, and even a custom weight table).

All sensitivities can be adjusted in these collations: "case", "accent", "width", "kana type", and "variation selector" (starting in SQL Server 2017, and only for the Japanese collations). Also, some of these collations (when used with Unicode data) support Supplementary Characters (starting in SQL Server 2012). This approach applies to both NVARCHAR and VARCHAR data (even non-Unicode data). It applies to non-Unicode VARCHAR data by first converting the value to Unicode internally, and then applying the sort/comparison rules.

Please note:

There is no universal default collation for SQL Server. There is an installation default which differs based on the current locale/language setting of the OS at time of installation (which is unfortunately SQL_Latin1_General_CP1_CI_AS for US English systems, so please vote for this suggestion). This can be changed during installation. This instance-level collation then sets the collation for the [model] DB which is the template used when creating new DBs, but the collation can be changed when executing CREATE DATABASE by specifying the COLLATE clause. This database-level collation is used for variable and string literals, as well as the default for new (and altered!) columns when the COLLATE clause is not specified (which is the case for the example code in the question).
For more info on Collations / encodings / Unicode, please visit: Collations Info


sort a large table by its primary key and save it back

Unless you specify an ordering using ORDER BY you can never guarantee the order of the output, even if you are very careful what order you put things in.

I'm not familiar with Firebird, but in many databases the difference could be explained by your primary key not being clustered, so when with the ORDER BY present it scans down the index in order then for each row looks up the extra data from the heap, where the version without explicit sorting will just scan the heap. With a clustered index, the index and the heap are one in the same so this extra work is not needed. Another way to avoid extra lookups is to include all the other columns that you need in an index that the DB can use. This is not space efficient though, especially for wide tables. To see if post-scan lookups are your performance problem see how SELECT <pk> FROM <table> ORDER BY <pk> compares to your other timing tests.

The above assumes you have no other filtering clauses, or joins and the large table is not seen as the primary one by the query planner, that complicate matters. If you do have those you should present fuller details of he query and table structure for better help. It may be that adding the ORDER BY is forcing a more complex query to spool the data out to a temporary table (potentially on disk) before rereading in order. Index use hints may help with this sort of problem, but without further details of the query and structures we cant' be more specific on that.


Database level access in Azure SQL managed instance

My guess is that you have a DDL trigger, which is the root cause for this. Check for triggers in Database_A (assuming this is the the database where the table to be altered lives).

Possibly this trigger executes a stored procedure in Database_B which should do some auditing of this operation.

The name TR_XYZ_DDL_AUDIT, gives us some clues.


What permission is needed to set Database read-only?

Form Query Analyzer or Management Studio, you can issue the following command:

ALTER DATABASE database-name SET READ_ONLY

As mentioned here, you need the alter database rights and you must set the database to single mode.

  Changing the state of a database or filegroup to READ_ONLY or
  READ_WRITE requires exclusive access to the database.

USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO


The table itself does not look like it contains redundant data. But, you explain "repetition of experiments and attributes on each day."

If the experiment had other attributes, splitting experiment definition into one table and results into another would be a standard normalization.

Experiment(Id, Name, Description)
Result(Experiment (FK), Participants, Participants_Attribute, Sales)

However, with the naming of the experiments "a" and "b" that seems to be covered already.

Another point of concern is the aggregate data, e.g. user_count. Normally, each result is stored separately and user_count would be a query on a child table. However, if the result is a group datum (individual data not stored), the current method is appropriate.


Visualizing SQL Server Extended Events data

Caveat: Much of the information provided below I learned strictly from going through two Pluralsight courses by Jonathan Keyhayias. Well worth the one month expense for the plus subscription to go through his two courses.

First just a few points of interest that I think will help (or at most be of interest):

When an Extended Event session is started it will have a portion of memory allocated to a buffer space to store data generated by the events of the session. In your session this is set to the default value of 4MB
There are multiple targets available to use. These targets are either synchronous or asynchronous in how they receive data. The two most commonly used targets being Target File and Ring Buffer are both asynchronous. BOL article here indicates what type each target is.
The MAX_DISPATCH_LATENCY is a configuration option that controls when event data is dispatched to the target(s). Dispatching only occurs for asynchronous targets. There are two conditions that will cause event data to be dispatched: (1) memory buffer for the session is full or (2) event data in the buffer exceeds the session's MAX_DISPATCH_LATENCY configured option.
When you open the Live Data Viewer it will attach an additional target to the event session called "streaming target". This will receive the live event stream as memory buffers are being dispatched. It actually will also change the dispatch latency associated with the session to 3 seconds in order to get near real time view of the session.

Now to specific points in your question:

  The problem that I am having is that it appears that the Live Events feature uses an internal buffer, which means that sometimes I need to execute a query several times to get its information to display in the window. I therefore have a two-part question to ask

I am not aware that it does this other than what I stated above. I would expect that the event was captured it just has not meet the thresholds required for it to be dispatched to your live data viewer. I tested this with the following query from AdventureWorks2012:

SELECT * FROM dbo.ErrorLog
WAITFOR DELAY '00:00:01' ;
GO

Using your event session configuration, with the exception that I am filtering to only capture data for AdventureWorks2012 database on my local instance, I can view the target data for this session and find the query was captured:

Executing this query one more time will finally cause it to get dispatched and the data viewer displays one event. Now if you actually want to see all the events that are displayed simply STOP the session and the buffer will get fully dispatched. I see this once I stop my session:

  1.Is there a way to get around this delay in getting the events to display in the live feed? (I'm doing this on a local database so performance is not an issue)

I had thought you could change the MAX_MEMORY to a lower value which would indicate a small buffer size to capture events. However the lowest value you can set this to in SQL Server 2012 is 200KB, which the query I used does not meet that limit to cause it to immediately be dispatched. Only thing I could do was at most execute a query that would cause the buffer to be reached and the previous events captured to be dispatched:

SELECT *
FROM Person.Person
ORDER BY EmailPromotion DESC;

  2.Is the live feed the best way to visualize Extended Events data? Is there another tool either in SSMS or not that is better adapted to my use case?

Not that I am aware of currently. I would suggest the best method of getting data out as soon as it occurs is to query the XML for the ring_buffer target and just shred that out. I can repeat the above example and as soon as I execute the query below I see the event.

-- Create XML variable to hold Target Data
DECLARE @target_data XML
SELECT  @target_data = CAST([t].[target_data] AS XML)
FROM    [sys].[dm_xe_sessions] AS s
JOIN    [sys].[dm_xe_session_targets] AS t
        ON [t].[event_session_address] = [s].[address]
WHERE   [s].[name] = N'Simple Query Benchmarking' AND
        [t].[target_name] = N'ring_buffer' ;

-- Return the full XML document
--SELECT @target_data;

--Shred XMl to get needed data
SELECT  DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
    n.value('(data[@name="duration"]/value)[1]', 'bigint') as duration,
    n.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') as sql_text
FROM @target_data.nodes('RingBufferTarget/event[@name=''sql_batch_completed'']') AS q(n)


count aggregate between three date ranges

You could use COUNT(DISCTINCT(CASE ... WHEN

I've set up next example:

CREATE TABLE nc (id int, flowstepname varchar(20), created datetime);

INSERT INTO nc VALUES
(1,  '1152', NOW() - interval 20 hour),
(1,  '1152', NOW() - interval 21 hour),
(2,  '1152', NOW() - interval 22 hour),
(1,  '1152', NOW() - interval 31 hour),
(1,  '1152', NOW() - interval 32 hour),
(1,  '1152', NOW() - interval 34 hour),
(2,  '3154', NOW() - interval 35 hour),
(2,  '1152', NOW() - interval 40 hour),
(1,  '1152', NOW() - interval 49 hour),
(1,  '1152', NOW() - interval 52 hour),
(2,  '4152', NOW() - interval 60 hour),
(3,  '1152', NOW() - interval 65 hour),
(4,  '1152', NOW() - interval 66 hour);

select nc.flowstepname,
       count(distinct(case when nc.created >= (now() - interval 24 hour) then nc.id else null end)) as day0,
       count(distinct(case when nc.created < (now() - interval 24 hour)
                            and nc.created >= (now() - interval 48 hour) then nc.id else null end)) as day1,
       count(distinct(case when nc.created < (now() - interval 48 hour)
                            and nc.created >= (now() - interval 72 hour) then nc.id else null end)) as day2
from   nc
where  nc.flowstepname like "1%"
and    nc.created > NOW() - INTERVAL 72 HOUR
group by nc.flowstepname ASC;

flowstepname | day0 | day1 | day2
:----------- | ---: | ---: | ---:
1152         |    2 |    2 |    3

dbfiddle here


No. SQL Server will first check the user's default schema for unqualified object references, and then the dbo schema. If not found at this stage, then you get an error.
I've been searching high and low now, but I can't find a reference for this in the product documentation.  And it is even hard to find anybody written about this over the whole interweb. I did, however, find this from Bob.
Max Vernon wrote a post showing how this works here.
Aaron Bertrand has some interesting advice about always specifying the schema, here


Query Plan - Estimated Row Count Very High

Here is an alternative using CASE statement, Please test the code before run

INSERT INTO #TmpClientOnboardingTable
   SELECT
    f.FundCode
   ,f.FundName
   ,tr.Period
   ,td.TaskName
   ,td.TaskDetailID
   --,tr.LatestComment
   ,[Tasks].[OverallTaskStatus](tr.TaskDetailID,tr.TagID,f.FundID,tr.Period) as 'OverallStatus'
   ,[Tasks].[LatestComment](tr.TaskDetailID,f.FundID,tr.Period) as 'LatestComment'

   --,[TaskDetailID]
   --,[TagID]
   --,[StatusFlagID]
   --,[ApprovalLevelID]
   --,[ApprovedByID]
   --,[ApprovedDateTime]
   --,[LatestComment]
   --,[CommentDateTime]
   --,[CommentPostedByID]
   FROM [Tasks].[TaskRecord] tr
   INNER JOIN dbo.Fund f
   ON f.FundID = tr.FundID
   INNER JOIN [Tasks].[Tag] t
   ON t.TagID = tr.TagID
   INNER JOIN [Tasks].[TaskDetail] td
   ON td.TaskDetailID = tr.TaskDetailID
   INNER JOIN [dbo].[StatusFlag] s
   ON s.StatusFlagID = tr.StatusFlagID
   LEFT JOIN [dbo].[UserDetails] u
   ON u.UserDetailID = tr.ApprovedByID
   WHERE tr.Period = @Period
   AND t.TagID = 4; -- Client Onboarding View -- ('Cash Reconciliation', 'Non Trade Related')
   --AND tr.ApprovalLevelID = 1 -- analyst
   --ORDER BY f.FundCode

  --optional if its unique
  --CREATE CLUSTERED INDEX CL_idx ON  #TmpClientOnboardingTable (Fundcode)

    SELECT DISTINCT
  c.FundCode
 ,c.FundName
 ,c.Period
 ,MAX(CASE WHEN TaskName= 'Client Risk Assessment' THEN TaskDetailID ELSE NULL END) AS 'ClientRiskAssessmentParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Client Risk Assessment' THEN OverallStatus ELSE NULL END,'RED')) as 'ClientRiskAssessment'
 ,MAX(CASE WHEN TaskName= 'AML/KYC' THEN TaskDetailID ELSE NULL END) AS 'AMLKYCParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'AML/KYC' THEN OverallStatus ELSE NULL END,'RED')) as 'AMLKYC'
 ,MAX(CASE WHEN TaskName= 'Broker' THEN TaskDetailID ELSE NULL END) AS 'BrokerParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Broker' THEN OverallStatus ELSE NULL END,'RED')) as 'Broker'
 ,MAX(CASE WHEN TaskName= 'Checklist' THEN TaskDetailID ELSE NULL END) AS 'ChecklistParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Checklist' THEN OverallStatus ELSE NULL END,'RED')) as 'Checklist'
 ,MAX(CASE WHEN TaskName= 'Register of Assets' THEN TaskDetailID ELSE NULL END) AS 'RegisterOfAssetsParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Register of Assets' THEN OverallStatus ELSE NULL END,'RED')) as 'RegisterOfAssets'
 ,MAX(CASE WHEN TaskName= 'Expense Budget' THEN TaskDetailID ELSE NULL END) AS 'ExpenseBudgetParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Expense Budget' THEN OverallStatus ELSE NULL END,'RED')) as 'ExpenseBudget'
 ,MAX(CASE WHEN TaskName= 'Decisions' THEN TaskDetailID ELSE NULL END)  AS 'DecisionsParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Decisions' THEN OverallStatus ELSE NULL END,'RED')) as 'Decisions'
 ,MAX(CASE WHEN TaskName= 'Onboarding Review' THEN TaskDetailID ELSE NULL END) AS 'OnboardingReviewParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Onboarding Review' THEN OverallStatus ELSE NULL END,'RED')) as 'OnboardingReview'
FROM #TmpClientOnboardingTable c
GROUP BY c.FundCode
        ,c.FundName
        ,c.Period

Also, the estimated number is high because you are joining to the same table for 9 times which bloats the number of rows.


Select query on information_schema is too slow

information_schema isn't a real database. To preform this query it needs to open tables.

The internal code for information_schema isn't optimized and it will frequently generate the data for the entire table requested without consideration of the query. As such your query could be opening every table regardless of the current table.


Use result from table_schema query as  in select * from

You can't do this with ordinary SQL, at least in PostgreSQL. You need to use PL/PgSQL to generate dynamic SQL and run it with EXECUTE. The

RETURN QUERY EXECUTE format('SELECT ... FROM %I WHERE $1, $2', tablename) USING param1, param2;

construct is useful.

See executing dynamic statements.


Table scan estimates when there are no stats on the table

Try a different question: should I need to create statistics just to see how much space a table uses?

EXEC sp_spaceused 'test';

That query shouldn't just fail, right? Yet it provides the correct row count of 1000 rows. You can look at the code of sp_spaceused quite easily:

SELECT
            @reservedpages = SUM (reserved_page_count),
            @usedpages = SUM (used_page_count),
            @pages = SUM (
                CASE
                    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                    ELSE 0
                END
                ),
            @rowCount = SUM (
                CASE
                    WHEN (index_id < 2) THEN row_count
                    ELSE 0
                END
                )
        FROM sys.dm_db_partition_stats
        WHERE object_id = @id;

This doesn't mean that the estimate "comes from the sys.dm_db_partition_stats DMV". DMVs are created for end users to write application code against. The approximate row count of the table is stored in some internal structure which can be used to provide a cardinality estimate when there's no other option available for the query optimizer. If you need to know the details about this internal structure you could try using a debugger, but it's not like there's any guarantee that it wouldn't change between SQL Server versions.

It's worth mentioning that you should almost never have tables without statistics in production.


SQL 2008 R2 creates user/schema when Windows user creates tables

This has always happened, back to SQL Server 2000.
Without schema, how does SQL Server know you want to put it in the dbo schema?

The only way to specify a default schema is to :

use SQL logins (not Windows)
run as "sysadmin"

Neither of these is acceptable

Best practice is to always qualify schema for every object reference for DDL and DML. There are clear performance benefits because of plan re-use.

Also, deliberate schema use is better for SQL Server 2005:

tables in Data
other tables in Archive, Staging etc
code in schemas per client permissions: Desktop, WebGUI etc

Using the dbo schema is so last millenium :-) Links:

Schema design - best practices?
https://dba.stackexchange.com/q/7257/630


Postgres 11 declarative partitioning by relative time

You could partition by 4 hour intervals (aligned to a specific boundary pattern) and then subpartition that into hours.  But, I don't see what that would get you. If you have them partitioned by hour and run a query that covers 4 hours, it will just query the appropriate 4 individual hourly partitions.  Adding an intervening layer of 4-hour partitioning doesn't get you anything.

For 2, no.  It would have to move rows between partitions atomically with the tick of the smallest clock granularity, and that just isn't feasible.  But if you query for an unaligned one hour interval, it will just have to include two adjacent one hour partitions, so it should still get a large part of whatever benefit there is to be had.

But it isn't clear what benefit there is to be had. It will depend on the nature of your queries. The biggest benefit you might get would be that once data reaches 31 days, you could drop the partition, rather than needing a massive DELETE.


Slow SQL query from app, fast from SQL Studio

The option PARAMETRIZATION was "forced" instead of "simple"
Switching back to simple + disabling Parameter Sniffing solved the issue.


Can't grant role permissions under sysadmin login

This is happening because the principal in the AS clause needs to have the permission with the "GRANT OPTION".  Running this will allow you to run your original code:

GRANT ALTER ON ROLE::[AdaptTemplate] TO [AdminSapr] WITH GRANT OPTION

My reading of Books Online made me think that what you're doing is right since AdminSapr is in the db_owner role but in testing I had to do something like the above to get this to work.


Simultaneous READ COMMITTED transactions updates a select for update columns

You can try to modify the testproc in the following way. I have tryed to be more simple as possible:

DELIMITER $$
CREATE PROCEDURE `testproc`()
BEGIN
  START TRANSACTION;
  select 'try to acquire lock';
  SELECT CurrentSize, BucketSize into @cursize, @bucketsize
  FROM TestTable WHERE id = 1 FOR UPDATE;
  select 'acquired lock';  -- only one thread is permitted here
  select sleep(1);  -- some work here
  IF @cursize < @bucketsize THEN
    select concat('CurrentSize < BucketSize (', @cursize, '<', @bucketsize, ')');
    UPDATE TestTable SET CurrentSize = CurrentSize+1 WHERE id = 1;
  ELSE
    select concat('CurrentSize >= BucketSize (', @cursize, '>', @bucketsize, ')');
  END IF;
  COMMIT;
  select 'lock released';
END $$
DELIMITER ;

The main point is: only one thread can get a lock on the row. Only after the lock is acquired, it can update the counter. (You can use double check design pattern for performance reason but it is a little more complex). You don't need to read uncommited transactions.

To get an insight, I have called testproc from two or more mysql clients in a loop. So I have defined a loop:

create table TestTable( id int primary key, CurrentSize int, BucketSize int ) engine=innodb;
insert into TestTable (id, CurrentSize, BucketSize) values (1, 1, 5);

delimiter $$
create procedure test_loop()
begin
   declare i int default 30;
   while i >0 do
     call testproc();
     set i = i -1;
   end while;
end $$
delimiter ;

I have launched two mysql clients:

mysql1> call test_loop();

mysql2> call test_loop();

mysql1> select * from TestTable;
+----+-------------+------------+
| id | CurrentSize | BucketSize |
+----+-------------+------------+
|  1 |           5 |          5 |
+----+-------------+------------+
1 row in set (0.00 sec)

You can try to comment out FOR UPDATE into the testproc:

SELECT CurrentSize, BucketSize into @cursize, @bucketsize
FROM TestTable WHERE id = 1;  -- FOR UPDATE;

If you try several times the previous test, you could get 6 into CurrentSize column.

mysql1> call test_loop();

mysql2> call test_loop();

mysql1> select * from TestTable;
+----+-------------+------------+
| id | CurrentSize | BucketSize |
+----+-------------+------------+
|  1 |           6 |          5 |
+----+-------------+------------+
1 row in set (0.00 sec)

Hope it is help.


The SELECT permission was denied - View querying data from a different database

To maintain an unbroken ownership chain among databases with views, the databases must be owned by the same login in the case of dbo-owned objects. Also, the DB_CHAINING option needs to be enabled for the databases involved. The user needs connect permissions to all databases accessed by the view but only permissions on the view need to be granted.

Be aware that of the security implications with DB_CHAINING. Only enable the option when you fully trust users who can create objects in databases with chaining enabled. This is less of a concern when only sysadmin role members can create objects and review scripts before deployment.


Which option is better to search in two tables in varchar columns?

Paul White wrote this article about using a trigram wildcard string search in place of the LIKE operator. It's quite an in-depth article, but it's probably the best you'll find on the subject (like a lot of what Paul White writes). I've implemented it in a test environment and it really is lightning fast compared to the LIKE operator.


MySQL user password vs authentication_string


MySQL LOAD DATA INFILE Error Code 1366. Incorrect integer value: ' '

To set NULL you just need your table to have allow nulls.

If you want to change it to some default value in case of nulls/missing values, you need to specify individual column names as below:

LOAD DATA INFILE "/home/mycsv.txt"
INTO TABLE inbound.master_data
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
    (name,proddescr,country,thing,charA,datecol,charB,@VAL)
    set numberCol=if(@VAL is null, 0, @VAL);

You can change that 0 to any other default value.

This blog post has many other interesting load data scenarios you might want to learn. (go through the comments as well).


SQLServer Query Store not saving failed queries run by SP_ExecuteSQL

does this somehow cheat the querystore into thinking it is ok, no matter what the result ?

Queries executed with sys.sp_executesql can definitely be logged in the Query Store as "Aborted" or "Exception."  Here's a small repro.  First I created a database with impractical Query Store settings:
USE [master];
GO
DROP DATABASE IF EXISTS [278067];
GO
CREATE DATABASE [278067];
ALTER DATABASE [278067] SET QUERY_STORE = ON;
ALTER DATABASE [278067]
SET QUERY_STORE
(
    OPERATION_MODE = READ_WRITE,
    DATA_FLUSH_INTERVAL_SECONDS = 30,
    INTERVAL_LENGTH_MINUTES = 1
);
GO
USE [278067];
GO

Then I ran some .NET code that exercised the following scenarios:

Run a normal successful query
Run a query that errors out (in this case, it was a divide by zero)
Run a query that returns millions of rows, and then cancel client-side before all the results have been consumed
Run a query that returns millions of rows, and then close the connection before all the results have been consumed
Run a query that gets blocked, and experiences a server side lock timeout

Here's how the came through in the Extended Events "rpc_completed" event:

I embedded a comment of /* Track Me */ in each of the queries so I could find them in the query store:
SELECT
    qsrs.execution_type,
    qsrs.execution_type_desc,
    qsrsi.start_time,
    qsrsi.end_time,
    qsqt.query_sql_text,
    qsp.query_plan
FROM sys.query_store_plan qsp
INNER JOIN sys.query_store_query qsq
    ON qsp.query_id = qsq.query_id
INNER JOIN sys.query_store_query_text qsqt
    ON qsqt.query_text_id = qsq.query_text_id
INNER JOIN sys.query_store_runtime_stats qsrs
    ON qsrs.plan_id = qsp.plan_id
INNER JOIN sys.query_store_runtime_stats_interval qsrsi
    ON qsrsi.runtime_stats_interval_id = qsrs.runtime_stats_interval_id
WHERE
    qsqt.query_sql_text LIKE '%Track Me%'
    AND qsqt.query_sql_text NOT LIKE '%sys.query_store_plan%';

As expected, we see Regular, Exception, and then three Aborted queries.
All that is to say that I don't think the discrepancy can be explained by the application's use of sys.sp_executesql.
If you could elaborate more on:

your query store settings (perhaps the queries experiencing timeouts aren't being logged)
what specific kind of timeout the query experienced (client side connection or command timeout, lock timeout, something else)
the extended event you used to identify the timeout

Then you might be able to get some more help tracking this down.


Assuming we're talking about Sybase ASE ...
To allow DDL (eg, create table) to be performed within a transaction the DBA can configure the database (where the DDL is to be performed) with the following:
sp_dboption <dbname>,'ddl in tran',true

NOTE: ddl in tran == true is not recommended since this can lead to excessive blocking, and in some rare cases deadlocking, on system table activity


Are there other sites still using SQL Server with codepage 850 or codepage 437 for their sql scripts?

I'm not sure this is an answerable question - yes, there are always sites on the web using wacko codepages for their defaults.

The most obvious problem when dealing with different collations is joining them together (cross-database queries), especially TempDB.  If TempDB has a different collation than your database, you can run into problems just creating a temp table and joining it to your data.

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Changing-Database-Collation-and-dealing-with-TempDB-Objects.aspx


Force everyone to use snapshot isolation

Is there a way for SQL Server to force everyone to use snapshot isolation or remove the explicit hints they added?

There is no way to do that !

If you enable RCSI using alter database then DML statements against objects stored in that database start generating row versions even when no transaction uses snapshot isolation. Once this option is enabled, the transactions specifying the read committed isolation level use row versioning instead of locking. When a transaction runs at the read committed isolation level, all statements see a snapshot of data as it exists at the start of the statement.


How does AWS DMS on-going replication works internally?

The reason you didn't find any specific information in AWS's documentation on the replication rate is because it mostly depends on the source database. For example, in Microsoft SQL Server, there's a feature called Change Data Capture (CDC) which logs data changes in real-time. This means DMS will gather all logs from the CDC tables in the source Microsoft SQL Server database since the last time DMS ran.
Other database systems might log changes at slower rates, so there isn't once answer from AWS's perspective. Additionally, I believe you can schedule DMS Tasks to run at their own intervals as well.
But in general, the goal of AWS's DMS Tool is to replicate all changes since it last synced.


Does PostgreSQL `CLUSTER` command also cluster extended storage?

cluster command is a table rewrite process. So, yes it will cluster anything on the table, including extended storage.


postgresql replace table without losing dependancies

Disable any FKs pointing to the table
Create table SAVE as SELECT * FROM A
Truncate table A
Begin to create a script with the following

with xfer as (select cast ('a' as text) as targ_tab, cast ('b' as text) as from_tab)
select xfer.*, tabs.table_name as targ_table_name, cols.table_name as from_table_name,
  'alter table ' || tabs.table_name || ' add (' ||
  coalesce(cols.column_name, '') || ' ' || coalesce (cols.data_type, '')
  ||
      case when data_type = 'numeric' and coalesce (cols.numeric_precision,
          cols.numeric_scale) is not null then
          '(' || coalesce ('' || cols.numeric_precision, '*') || ', ' ||
          coalesce (cols.numeric_scale, 0) || ')'
      else ''
      end
  || ')'
  as synt
from xfer join
  information_schema.tables tabs on (xfer.targ_tab = tabs.table_name)
  join
  information_schema.columns cols on (xfer.from_tab = cols.table_name)
where not exists (
select 1
from information_schema.columns targcols
where targcols.table_name = xfer.targ_tab
and targcols.column_name = cols.column_name
)
order by ordinal_position

Repopulate with INSERT/SELECT
Re-enable foreign keys


Differentiate between a row modification and add?

From the datasets you posted, no. You could add a column "modified" which contains a flag that tells you whether the row was modified or not. That column could be maintained via a trigger on the table, or, in my opinion better, you could encapsulate inserts/updates to that table in a stored procedure and have the procedure maintain that column.


Slow queries on billions-rows-table // index used

This may reflect my MS SQL bias, but I'd try clustering the table by timestamp.  If you're frequently pulling data for a specific time span, this will help because the data will be physically stored contiguously.  The system can seek to the start point, scan to the end of the range, and be done.  If you're querying for a specific hour, that's just 3,600,000 records.

If your query (which is...?) is for a specific machine, Postgres will then need to filter out 99.9% of those 3.6 M records.  If this one-in-a-thousand filter is more selective than a typical date range fitler, you should use the more selective mac field as the first component of your index.  It may still be worth clustering.

If that still doesn't do it, I'd partition by the same field you're indexing, either timestamp or mac.

You didn't give the data types.  Are they appropriate to the data?  Storing dates as text will needlessly bloat your table, for example.


Select record based on multi-row criteria

SELECT *
FROM [Table_A]
WHERE [ID] IN
(
    SELECT [ID]
    FROM [Table_A]
    GROUP BY [ID]
    HAVING (
            COUNT(*)=1 AND
            MAX(CASE WHEN CODE IN ('AR', 'RT')
                AND BAL <= 0 THEN 1 ELSE 0 END)=1
            )
            OR
            (
            COUNT(*)>1 AND MAX(BAL)<=0
            )
)


FlushCache messages appearing in log at specific times

The FlushCache message in the error log is caused by checkpoint logging, and in this case by a long checkpoint (which is defined as a checkpoint that is taking longer than the recovery interval).  Whether it's logged or not, the behavior is different in pre-2012 and 2012+.  Before SQL Server 2012, to get checkpoint logging you'd have to turn on a trace flag (T3504).  But starting in SQL Server 2012 that message is logged by default when a long checkpoint is encountered.

Now as for the question of "is this actually bad?", you really need to start looking at these numbers given their context.  It took you 97+ seconds to flush only about 93 MB of dirty buffers.  This looks like this could potentially be a mixture of a lot of data churn (during the actual checkpoint itself, about 64 MB worth of buffers were also dirtied) and potentially storage that isn't keeping up with the data modification and/or the rest of the I/O workload.

What I would do is verify the health of your storage subsystem, look at waits, and just get an overall performance picture of the instance.  Take a look at logical disk perfmon counters and see what the overall I/O churn is with throughput, latency, and IOps.  It will help you paint a more vivid picture of how the disks are performing.  If you have the ability to benchmark your storage, if you haven't already baselined it, you should see what these volumes in question are capable of (SQLIO is a great utility for that) and what they are doing right now (it's nice to have a benchmark baseline when the volumes were stood up to compare to a current benchmark).

Here is a great article explaining this message - How It Works: When is the FlushCache message added to SQL Server Error Log?

EDIT:  Re-reading your question, I must've missed this comment:

  I noticed that every morning around 5 am we are started to get this message

See what is happening on your storage at this time as per the guidance above.  That sounds like textbook scheduled operation that is taking a toll on the storage causing the checkpoint performance to suffer and to be "long".


How to determine that the maximum size of the database file is exceeded, and switch the data insertion to the second server?

Usually it would be slower to do what you suggest.

Case 1 -- big disk.  With a good index, the time it takes to fetch a row is almost the same for a huge table as for a tiny table.

Case 2 -- table is split across multiple servers.  Now you have to try the query on one server, then move to another.  This seems like a lot more work.

Lets be specific...  Please show us the query that you are trying to optimize.  And show us SHOW CREATE TABLE.  There may be a simple way to make it run fast, regardless of the 2GB boundary you propose.


TSQL Convert FLOAT to STRING Truncation/Rounding Issue

From the documentation about CAST and CONVERT:

CAST is 'apparently' using the default style of 0 since you cannot specify a style when using CAST.

CONVERT defaulted to style 0 since you did not specify a style.

You can achieve more precision using CONVERT and style (and your have to increase the length of the target datatype), but the results appear to be in scientific notation (so, not exactly like STR).

DECLARE @floatVal   FLOAT = 4.76758527755737

SELECT
    CONVERT(VARCHAR(30), @floatVal,1)     'VARCHAR'
    ,CONVERT(NVARCHAR(30), @floatVal,1)   'NVARCHAR'
GO


Does PostgreSQL always initialize database of same size?

Absolutely not. The database is formed from the contents of template1 a "master database." Adding things to template1 will directly affect the contents of the new database.
From the docs, Template Databases

CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database named template1. Thus that database is the "template" from which new databases are made. If you add objects to template1, these objects will be copied into subsequently created user databases. This behavior allows site-local modifications to the standard set of objects in databases. For example, if you install the procedural language PL/Perl in template1, it will automatically be available in user databases without any extra action being taken when those databases are created.

However, you can create a copy of template0 which we hope is a virgin database, also from the docs links above.

There is a second standard system database named template0. This database contains the same data as the initial contents of template1, that is, only the standard objects predefined by your version of PostgreSQL. template0 should never be changed after the database cluster has been initialized. By instructing CREATE DATABASE to copy template0 instead of template1, you can create a "virgin" user database that contains none of the site-local additions in template1. This is particularly handy when restoring a pg_dump dump: the dump script should be restored in a virgin database to ensure that one recreates the correct contents of the dumped database, without conflicting with objects that might have been added to template1 later on
Another common reason for copying template0 instead of template1 is that new encoding and locale settings can be specified when copying template0, whereas a copy of template1 must use the same settings it does. This is because template1 might contain encoding-specific or locale-specific data, while template0 is known not to.
To create a database by copying template0, use:
CREATE DATABASE dbname TEMPLATE template0;


Best way to design table structure for Operating Hours along with break time and optimized Query

From your comment above I cannot understand your motive for how you are trying to achieve it the way that you are but this is how I would do it (this is off the top of my head and I have not had chance to test this yet so please bear that in mind):

CREATE TABLE `Store`
(
    id INT AUTO_INCREMENT NOT NULL,
    -- additional columns for the store type
    PRIMARY KEY (id)
);
CREATE TABLE `OpeningHours`
(
    store_id INT NOT NULL,
    `open` TIME NOT NULL,
    `close` TIME NOT NULL,
    FOREIGN KEY (`store_id`) REFERENCES `stores`(`id`)
    -- add your own indexes here
);

Then put your open to close times in here such that if you opened at 9, closed at 5 and took a 1 hour lunch at 12 you would have data like so:

stored_id    `open`    `close`
1             09:00:00  12:00:00
1             13:00:00  17:00:00

Then you can query for stores that are open between a range of times like so:

DECLARE p_Time TIME;
    SET p_Time = TIME();

 SELECT DISTINCT store_id
   FROM `OpeningHours`
  WHERE  p_Time BETWEEN `open` AND `close`;

You can then use the store_id from the results above to join to the store table to get the store details.


Is network traffic encrypted when writing remote backups using SQL Server TDE?

Yes, backups are encrypted while moving over the network because TDE data is encrypted on disk, and the backup operation never decrypts it.
Paul Randal's Backup Myths:

Myth 30-09) backups read data through the buffer pool
No. The backup subsystem opens its own channels to the database files to avoid the performance hit of having to read everything into SQL Server’s memory and back out to the backup device (and also effectively flushing the buffer pool in the process). If you ask the for page-checksum checking, it uses it’s own small portion of memory.

If the pages were loaded into the buffer pool (the "normal" memory space SQL uses to cache database table and index data), they would have to be decrypted. But backups don't do that, they just dump raw encrypted "extents" (contiguous 8-page chunks) to your backup destination.
I was able to get confirmation from Paul Randal that his above comment is still relevant for TDE:

It works exactly the same way. The buffer pool does encryption then adds a page checksum before writing a page to disk. Backups never read through the buffer pool. So yes, a backup of TDE database has the encryption still in it. Page checksums are validated, but by backup code, not buffer pool code.

In other words, if you've enabled CHECKSUMs on a database, those are added (during normal SQL write operations) after encryption occurs. This means that the backup process can read the raw (encrypted) extent, validate the checksum, and write the backup, all without decrypting the data.
This is almost certainly the reason that (prior to SQL 2016), enabling backup compression on database with TDE didn't do anything, since encrypted data is not very compressible:

This is because when backups of a TDE encrypted database are taken the database pages are not decrypted when backed up. They are backed up in the same encrypted state that they are normally in, then compressed. By it’s nature encrypted data is very unique so data compression doesn’t do much good against encrypted data.

For a restore operation, the same principle applies. The encrypted backup stays encrypted across the network, and is written to the restoring server's disk in their still-encrypted state. They are only decrypted when the database is loaded in memory after the restore is complete.


Order by exact matches (jsonb array), then lexeme similarity

Check if this is what you're looking for [I didn't fully understand your first OR / AND condition. I assumed it was just an OR].

WITH posts AS
(
SELECT
    *
FROM
    (VALUES
        ('how do I say ''hello'' in French?', '{"orange":1, "apple":2}'::jsonb),
        ('what''s the weather like in the French Riviera?', '{"peach":3, "lemon":4}'::jsonb),
        ('awful weather in England', '{"peach":5, "lemon":6}'::jsonb),
        ('awful weather in England', '{"pineapple":5, "strawberry":6}'::jsonb),
        ('doubtful french fries', '{"blueberry":5, "pear":6}'::jsonb),
        ('the rain, in Spain, is mainly in the plain', '{"melon":7, "watermelon":8, "banana":9}'::jsonb)
    ) AS posts(content, tags)
)

SELECT
    *,
    /* Use ts_rank to compare level of full text search coincidence */
    ts_rank(to_tsvector(content), plainto_tsquery('french') ||
          plainto_tsquery('hello')) AS rank,
    /* Subquery to count number of tag matches */
    (SELECT
          count(case when tags ? a then 1 end)
     FROM
          unnest(array['melon', 'banana', 'lemon']) AS a
    ) AS number_of_matching_tags
FROM
    posts
WHERE
    /* Check for any of the tags */
    tags ?| array['melon', 'banana', 'lemon']
    OR
    /* Check for any of the search terms. You have to || tsqueries */
    to_tsvector(content) @@
        (plainto_tsquery('french') || plainto_tsquery('hello'))
ORDER BY
    number_of_matching_tags desc nulls last,
    rank desc ;

(The inclusion of number_of_matching_tags and rank columns is only to clarify results)


You need a recursive CTE (common table expression):

with    -- recursive
        -- some DBMS (e.g. Postgres) require the word "recursive"
        -- some others (Oracle, SQL-Server) require omitting the "recursive"
        -- and some (e.g. SQLite) don't bother, i.e. they accept both
descendants
  (parent, descendant, lvl)
as
  ( select parent, child, 1
    from source
  union all
    select d.parent, s.child, d.lvl + 1
    from descendants  d
      join source  s
        on d.descendant = s.parent
  )
select *
from descendants
order by parent, lvl, descendant ;

See the SQLfiddle. The level I added in not needed. It gives you the "distance" between the parent and the descendant, (1 is child, 2 is grandchild, etc).

Link for the related Teiid documentation about: (recursive) CTEs.

PS:
For Oracle:

LEVEL is a restricted keyword


No, it's not recommended because it's largely a waste of time. When SQL Server restarts, tempdb is created from scratch. Also, the data in there isn't around long enough to be a good indicator of if there's an issue with it, or the underlying storage.

Both Ola's scripts and Maintenance Plans will skip tempdb when you set up CHECKDB routines.

Another reason why it's largely useless is because if tempdb does get corrupted, you'll know in a hurry. Check out my blog post here where I corrupt tempdb on purpose: DBA Days: Killing TempDB softly

Coming up in SQL Server 2019, we'll be able to use "in memory" technology for system tables in tempdb. While this solves pretty big contention issues, you can't run CHECKDB against in-memory tables. The only way you could check those is if you backed up tempdb, which isn't something you can do.

BACKUP DATABASE tempdb TO DISK = 'D:\Backups\t.bak'

  Msg 3147, Level 16, State 3, Line 27 Backup and restore operations are
  not allowed on database tempdb. Msg 3013, Level 16, State 1, Line 27
  BACKUP DATABASE is terminating abnormally.

If you really think it's worth running checkdb against a database that SQL Server won't allow you to back up, well, it's your maintenance window, I suppose.


Unable to shrink transaction log file in SQL Server

The reason your process isn't working is that it's waiting to release the inactive portion of the log using a transaction log backup. Switching to Simple Recovery Model bypasses this issue.

If you really need to shrink down the transaction log, so that you can regrow it (to reduce the number of VLFs, for instance), this is the process:

1) Switch to Simple Recovery Model

2) Run a CHECKPOINT and DBCC DROPCLEANBUFFERS (just in case)

3) Shrink the log file

4) Switch back to Full Recovery Model

5) Grow the log in reasonable increments (I use 4000MB increments)

6) Set autogrowth to a value your I/O subsystem can handle

7) Run a full or differential backup to restart the log backup chain.


When duplicate date values exist in a column, change the value of the oldest

I think this solves your problem.

--demo setup
drop table if exists billing;
go
CREATE TABLE Billing
    ([BillDt] datetime, [BillMonth] varchar(3))
;

INSERT INTO Billing
    ([BillDt], [BillMonth])
VALUES
    ('2018-01-30', 'Jan'),
    ('2017-12-28', 'Dec'),
    ('2017-11-30', 'Nov'),
    ('2017-10-31', 'Oct'),
    ('2017-09-28', 'Sep'),
    ('2017-08-31', 'Aug'),
    ('2017-07-31', 'Jul'),
    ('2017-06-29', 'Jun'),
    ('2017-05-31', 'May'),
    ('2017-04-27', 'Apr'),
    ('2017-03-30', 'Mar'),
    ('2017-03-01', 'Mar')
;

--the solution
--With SQL Server looking at each row on the Billing table...
--if you can find a row where the month is the same as the 'current' row, but
--the day is greater than the 'current' row, update the BillMonth of the 'current'
--row to be the previous month of the 'current' row.
UPDATE b
SET BillMonth = Substring(datename(month, DATEADD(month, - 1, billdt)), 1, 3)
FROM Billing b
WHERE EXISTS (
        SELECT *
        FROM Billing
        WHERE datepart(month, BillDt) = datepart(month, b.BillDt)
            AND datepart(day, BillDt) > datepart(day, b.BillDt)
        )

SELECT *
FROM billing
ORDER BY billdt desc

 BillDt                  | BillMonth |
|-------------------------|-----------|
| 2018-01-30 00:00:00.000 | Jan       |
| 2017-12-28 00:00:00.000 | Dec       |
| 2017-11-30 00:00:00.000 | Nov       |
| 2017-10-31 00:00:00.000 | Oct       |
| 2017-09-28 00:00:00.000 | Sep       |
| 2017-08-31 00:00:00.000 | Aug       |
| 2017-07-31 00:00:00.000 | Jul       |
| 2017-06-29 00:00:00.000 | Jun       |
| 2017-05-31 00:00:00.000 | May       |
| 2017-04-27 00:00:00.000 | Apr       |
| 2017-03-30 00:00:00.000 | Mar       |
| 2017-03-01 00:00:00.000 | Feb       |


How can I make an automatic copy of a MySQL database to another MySQL database on the same server?

You can setup second mysql instance with replication. If you need to "copy" only during certain time period, you may choose to cron a script which will start and stop replication according to the time.
Again you can only choose to replicate-do-db certain databases as well.


Not searching GIN index when querying ANY(column)

Use array operators which are supported by your GIN index. The = ANY () construct is not.

SELECT *
FROM   ebooks
WHERE  authors_cache @> '{Charles Bukowski}'  -- array literal
LIMIT  60;

Detailed explanation:

Can PostgreSQL index array columns?

Not sure your de-normalization will buy you much. A standard 1:n implementation with simple btree indexes should perform nicely, too.

If you are going with this approach, I would consider storing an array of integer (int[]) with author_id's. Substantially smaller array column and GIN index. Potential problems with referential integrity are about the same. (Array elements cannot currently have FK constraints. Attempts to implement this in Postgres were unsuccessful so far.)

Oh, and consider upgrading. Performance with GIN indexes has been improved substantially since Postgres 9.4.


Equivalent of SQL Not Exists in MDX (or finding another way to accomplish same result)

As you say it's hard to try out without your actual cube and it depends a bit on how your dimensions are layed out, but I think you can get there using the BOTTOMCOUNT function if you create  a measure on seq.

  BottomCount(Set_Expression, Count [,Numeric_Expression])

Your set_expression could be a crossjoin between the dimensions you care about and you could create a dynamic set based on that expression.

Something along the lines of

WITH DYNAMIC SET LowestSeq AS
BottomCount({Part.Members * Customer.Members * Person.Members})
   , 1
   , [Measures].[seq])

It would help if you published a small repro of your scenario as an XMLA script on pastebin or something. If your dsv is based on named queries producing the data we can easily create the same cube on our machine.


I get the error "You are attempting to execute a query in which the selected expression is no part of a statistical function." in MS Access 2010?

When you have an aggregate function, you need a GROUP BY statement.  In your case, it would be

SELECT
    LEERLINGEN.LLNR,
    UITLENINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    SUM(UITLENINGEN.BOETE)
FROM
    LEERLINGEN
    INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR
WHERE
    (((UITLENINGEN.BOETE)>25))
GROUP BY
    LEERLINGEN.LLNR,
    UITLENINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM;

The GROUP BY clause comes after a WHERE clause but before an ORDER BY (or HAVING) clause on a SELECT statement.  In this case, you want to get the sum of "Boete" for each combination of your other columns, hence the need to group by all of those other rows.

In short, if you have an aggregate function (like SUM, AVG, MIN, or MAX), you'll need to tell the database engine what to do with those non-aggregated columns.  That's a simplistic way of explaining what GROUP BY does for you there.

Incidentally, if you want to find out cases where the sum of all Uitleningen.Boete is greater than 25 (rather than only including the records in which Uitleningen.Boete is greater than to 25), you actually have to use the HAVING clause.  Then it would look like this:

SELECT
    LEERLINGEN.LLNR,
    UITLENINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    SUM(UITLENINGEN.BOETE)
FROM
    LEERLINGEN
    INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR
GROUP BY
    LEERLINGEN.LLNR,
    UITLENINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM
HAVING
    SUM(UITLENINGEN.BOETE)>25;

Think of HAVING as a WHERE for your groups.  You filter rows using WHERE, and you filter groups using HAVING.


CONCAT_WS causes "Data too long" which works partially?

I would expect and hope that since it produced an error, no changes would be commited

The reason is in Strict SQL mode.

When error (instead of warning) occures, only the rest of rows is ignored, rows already updated do not ROLLBACKed.

See Strict SQL Mode:

  For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, because the earlier rows have been inserted or updated, the result is a partial update. To avoid this, use single-row statements, which can be aborted without changing the table.

  what do they mean by "single-row statements"?

It is a statement which affects strictly one row. By WHERE conditions and/or by LIMIT borders. If warning converted to error occures, the row in update is not altered.

  both a and b are VARCHAR(200)

This means that concatenated value can be up to 402 symbols. So use

UPDATE person
SET a = LEFT(CONCAT_WS('\n', a, b), 200); -- truncate if too long

or

UPDATE person
SET a = CONCAT_WS('\n', a, b)
WHERE LENGTH(CONCAT_WS('\n', a, b)) <= 200; -- do not update if too long


Backup & restore to different servers different locations

I agree with Kin dbatools you can either use Start-DbaMigration or Copy-DbaDatabase depending on your needs.

Update: Now, you can migrate from one server to many. This applies to both Start-DbaMigration and all of the Copy-Dba* commands, including Copy-DbaDatabase and Copy-DbaLogin - see https://dbatools.io/migration-enhancements/


mysql no longer defaulting to /tmp/mysql.sock

The MySQL documentation states that the default path to mysql.sock is /var/lib/mysql/ on RPM distributions. The path is set at compile-time depending on the target OS and can be overridden by configuration or command line, so for CentOS the behavior you are seeing is correct.
This means that the socket path must have originally been set to /tmp by some other mechanism. There are several places this could have been done; likely in the init script /etc/init.d/mysql.server (to set socket=/tmp/mysql.sock on the command line - this can be done in an init script even though it is unsupported) or a local options file. See this page in the documentation for MySQL's precedence order when processing startup options. Entries listed later in the table will override the preceding ones.
It probably isn't possible to trace the sequence of events leading up to the socket path change, but fortunately you can change it via ~/.my.cnf, the user-specific option file.


postgres parsing whitespace before vectorizing

You can create your own parser, but I think that doing so is unreasonably difficult.  Preprocessing the strings yourself before the parser gets called is probably the way to go.  You just need to make sure it is done consistently, for both tsvector and tsquery.

  Also, another problematic string is "Main St./First St./Second St." which separates into "Main" "St./First" and "St./Second".

In my hands it is separated into './first', etc., not into 'st./first'.  Just preprocessing the '/' into ' ' should do the job of getting rid of the leading './', but will not do anything to make "First St." stay together as one unit if that is what you want  (If that is what you want, I don't know what the solution would be).

To make 'R&R' stick together, you could just replace the '&' with either '.' or '/'.  Both of those symbols are recognized as being part of file names or paths and so don't break up the words they come between (as long as there is not also some other whitespace/punctuation).

create function j(text) returns text immutable language SQL as $$ select translate($1,'&/','/ ') $$;

select to_tsvector(j('The R&R runs between First St./Second St.')) @@ plainto_tsquery(j('R&R'));

This is all pretty grotty, and the 2 examples you give are unlikely to cover all special cases you will want to deal with.  And a more complete list will likely contain internal contradictions, like what if someone wrote 'Main St&First St'?


How to move a database from SQL Server 2012 to SQL Server 2005


How to setup SSL in SQL Server 2012 with own certificate?

This blog post details the entire process.

You use IIS manager to create a self signed certificate, then add it through the Certificates MMC for the computer account.

Then give SQL server's service account read permissions on the certificate, and choose the certificate in SQL Server's network configuration in configuration manager.


Find and replace/wrap certain terms in html tags

SQL Server is not the best with String Manipulation.  If this functionality will change over time or become complex in any nature, I would suggest you look into CLR as a way to search/replace complex strings.  Here's a great article from Phil Factor on Simple Talk that walks through how to use CLR for complex string operations.


It has to wait on the scheduler A runnable queue, there is no switching of tasks to alternative schedulers.  The time accumulated waiting for the scheduler to be available is referred to as a signal wait.

  If a thread is ready to go, but the scheduler to which it’s assigned
  currently has another session running, then it will be placed in the
  “Runnable” queue. This means it’s in the queue to get on the
  processor. This is referred to as a signal wait (ref).

This is different and separate to the time accumulated while Suspended on the wait list.

  The signal wait time is exposed by the signal_wait_time_ms column, and
  is solely CPU wait time. If a session is waiting for another resource
  to become available in order to proceed, such as a locked page, or if
  a running session needs to perform I/O, then it’s moved to the wait
  list. This is a resource wait and the waiting session’s status will be
  recorded as “Suspended.” (ref).

The relationship from sessions to schedulers maps out as:

Session -> Task -> Thread -> Scheduler -> Logical CPU

Once a thread is allocated to a scheduler it remains on that scheduler until it completes. It may move repeatedly between Running, Suspended and Runnable queues but will remain assigned to one scheduler.

A session may of course be comprised of multiple tasks and threads which are distributed across multiple schedulers as the result of a parallel execution plan.


SQL Replication Automatically Add table to Publisher

There is no inbuilt way of automatically adding new table to replication. This means, you have to carve out your own method suited to your environment.

Couple of thing that you should do ..

Poll sys.tables or have a trigger to fire on CREATE_TABLE
Check if table has primary key (must for replication)
modify publication property - immediate_sync to 0 --> should be done once to avoid generating snapshot for all articles.
Call sp_addarticle and sp_addsubscription to add article to publication and subscription.
Kick off snapshot .. this will generate ONLY shapshot for the article that is newly added.


Is it possible to get seek based parallel plan for distinct/group by?

I have exactly the same set up and I've been through the same stages of rewriting the query.

In my case the table names and meaning is a bit different, but overall structure is the same. Your table Transactions corresponds to my table PortalElevators below. It has ~2000 rows. Your table TxLog corresponds to my table PlaybackStats. It has ~150M rows. It has index on (ElevatorID, DataSourceRowID), same as you.

I'll run several variants of the query on the real data and compare execution plans, IO and time stats. I'm using SQL Server 2008 Standard.

GROUP BY with MAX

SELECT [ElevatorID], MAX([DataSourceRowID]) AS LastItemID
FROM [dbo].[PlaybackStats]
GROUP BY [ElevatorID]

Same as for you optimizer scans the index and aggregates results. Slow.

Individual row

Let's see what optimizer would do if I requested MAX just for one row:

SELECT MAX([dbo].[PlaybackStats].[DataSourceRowID]) AS LastItemID
FROM [dbo].[PlaybackStats]
WHERE [dbo].[PlaybackStats].ElevatorID = 1

Optimizer is smart enough to use the index and it does one seek. By the way, we can see that optimizer uses TOP operator, even though the query doesn't have it. This is a telling sign that optimization paths of MAX and TOP have something in common in the engine, but they are different as we'll see below.

CROSS APPLY with MAX

SELECT
    [dbo].[PortalElevators].elevatorsId
    ,LastItemID
FROM
    [dbo].[PortalElevators]
    CROSS APPLY
    (
        SELECT MAX([dbo].[PlaybackStats].[DataSourceRowID]) AS LastItemID
        FROM [dbo].[PlaybackStats]
        WHERE [dbo].[PlaybackStats].ElevatorID = [dbo].[PortalElevators].elevatorsId
    ) AS CA
;

Optimizer still scans the index. It is not smart enough to convert MAX into TOP and scan into seeks here. Slow. I didn't think of this variant originally, my next try was scalar UDF.

Scalar UDF

I saw that plan for getting MAX for individual row had index seek, so I put that simple query in a scalar UDF.

CREATE FUNCTION [dbo].[GetElevatorLastID]
(
    @ParamElevatorID int
)
RETURNS bigint
AS
BEGIN
    DECLARE @Result bigint;
    SELECT @Result = MAX([dbo].[PlaybackStats].[DataSourceRowID])
    FROM [dbo].[PlaybackStats]
    WHERE [dbo].[PlaybackStats].ElevatorID = @ParamElevatorID;
    RETURN @Result;
END

SELECT
    [dbo].[PortalElevators].elevatorsId
    ,[dbo].[GetElevatorLastID]([dbo].[PortalElevators].elevatorsId) AS LastItemID
FROM
    [dbo].[PortalElevators]
;

It does run fast. At least, much faster than Group by. Unfortunately, execution plan doesn't show details of UDF and what is even worse, it doesn't show the real IO stats (it doesn't include IO generated by UDF). You need to run Profiler to see all calls of the function and their stats. This plan shows only 6 reads. The plan for individual row has 4 reads, so real number would be close to: 6 + 2779 * 4 = 6 + 11,116 = 11,122.

CROSS APPLY with TOP

Eventually, I discovered the CROSS APPLY and how it can be applied ;-) in this case.

SELECT
    [dbo].[PortalElevators].elevatorsId
    ,LastItemID
FROM
    [dbo].[PortalElevators]
    CROSS APPLY
    (
        SELECT TOP(1) [dbo].[PlaybackStats].[DataSourceRowID] AS LastItemID
        FROM [dbo].[PlaybackStats]
        WHERE [dbo].[PlaybackStats].ElevatorID = [dbo].[PortalElevators].elevatorsId
        ORDER BY [dbo].[PlaybackStats].[DataSourceRowID] DESC
    ) AS CA
;

Here optimizer is smart enough to do ~2000 seeks. You can see that number of reads is much lower than for group by. Fast.

Interestingly, number of reads here (11,850) is a bit more than reads that I estimated with UDF (11,122). Table IO stats with CROSS APPLY have 11,844 reads and 2,779 scan count of the large table, which gives 11,844 / 2,779 ~= 4.26 reads per index seek. Most likely, seeks for some values use 4 reads and for some 5, with average 4.26. There are 2,779 seeks, but there are values only for 2,130 rows. As I said, it is difficult to get real number of reads with UDF without profiler.

Recursive CTE

As was pointed in comments, Paul White described a Recursive Index Skip Scan method to find distinct values in a large table without performing a full index scan, but doing index seeks recursively. To start recursion we need to find the MIN or MAX value for an anchor and then each step of recursion adds next value one by one. The post explains it in details.

WITH RecursiveCTE
AS
(
    -- Anchor
    SELECT TOP (1) [ElevatorID], [DataSourceRowID]
    FROM [dbo].[PlaybackStats]
    ORDER BY [ElevatorID] DESC, [DataSourceRowID] DESC

    UNION ALL

    -- Recursive
    SELECT R.[ElevatorID], R.[DataSourceRowID]
    FROM
    (
        -- Number the rows
        SELECT
            T.[ElevatorID], T.[DataSourceRowID]
            ,ROW_NUMBER() OVER (ORDER BY T.[ElevatorID] DESC, T.[DataSourceRowID] DESC) AS rn
        FROM
            [dbo].[PlaybackStats] AS T
            INNER JOIN RecursiveCTE AS R ON R.[ElevatorID] > T.[ElevatorID]
    ) AS R
    WHERE
        -- Only the row that sorts lowest
        R.rn = 1
)
SELECT [ElevatorID], [DataSourceRowID]
FROM RecursiveCTE
OPTION (MAXRECURSION 0);

It is pretty fast, though it performs almost twice amount of reads as CROSS APPLY. It does 12,781 reads in Worktable and 8,524 in PlaybackStats. On the other hand, it performs as many seeks as there are distinct values in the large table. CROSS APPLY with TOP performs as many seeks as there are rows in the small table. In my case small table has 2,779 rows, but large table has only 2,130 distinct values.

Summary

                         Logical Reads       Duration
CROSS APPLY with MAX           482,121          6,604
GROUP BY with MAX              482,123          6,581
Scalar UDF                    ~ 11,122            728
Recursive                       21,305             30
CROSS APPLY with TOP            11,850              9 (nine!)

I ran each query three times and picked the best time. There were no physical reads.

Conclusion

In this special case of greatest-n-per-group problem we have:

n=1;
number of groups is much smaller than the number of rows in a table;
there is appropriate index;

Two best methods are:

In case when we have a small table with the list of groups, the best method is CROSS APPLY with TOP.
In case when we have only large table, the best method is Recursive Index Skip Scan.


Almost Empty Data File is still taking time to Shrink in SQL Server

Three things that can make shrink of data file very slow are:
Heaps tables. For every page moved, SQL server has to adjust every index, for all rows.
LOB pages. For every page moved, SQL Server has to do a table scan of the table (there's no back-pointer).
Shrink being blocked - it has an endless timeout.
My guess is that you encountered one or both of the first two.


You replaced master with the one that comes from the SQL Server build process. This one is pointing to the release build mkmaster folders for msdb database. As msdb is no a system database, you should be able to start the engine, connect to it, detach msdb and re-attach the correctly located one. But this is not the correct process. The correct process is described in Rebuild System Databases, and involves running setup.exe:

Setup /QUIET /ACTION=REBUILDDATABASE ...


Method to Obtain Lossless Join Boyce-Codd Normal Form (BCNF) Decomposition

For determining the candidate keys:
 1. Determine the attributes not present in the RHS of the FDs
 2. Find the closures of those attributes
 3. Identify those closures that determine R. These will be your candidate keys

For determining the super keys:
Include the candidate keys with all possible combinations of the rest of the attributes

For determining trivial dependency:
$A \supseteq B$, B is trivially dependent upon A. Locate those FDs.


Seek and you shall Scan... on partitioned tables

The basic issue is that the Index Seek is not followed by a Top operator. This is an optimization that is usually introduced when the seek returns rows in the correct order for a MIN\MAX aggregate.

This optimization exploits the fact that the min/max row is the first one in ascending or descending order. It may also be that the optimizer cannot apply this optimization to partitioned tables; I forget.

Anyway, the point is that without this transformation, the execution plan ends up processing every row that qualifies S.UpdatedID <= @IDColumnThresholdValue per partition, rather than the desired one row per partition.

You have not provided table, index, or partitioning definitions in the question so I can't be much more specific. You should check that your index would support such a transformation. More or less equivalently, you could also express the MAX as a TOP (1) ... ORDER BY UpdatedID DESC.

If this results in a Sort (including a TopN Sort), you know your index is not helpful. For example:

SELECT
    @MaxIDPartitionTable = ISNULL(MAX(T2.IDPartitionedTable), 0)
FROM
(
    SELECT
        O.IDPartitionedTable
    FROM
    (
        SELECT
            P.partition_number AS PartitionNumber
        FROM sys.partitions AS P
        WHERE
            P.[object_id] = OBJECT_ID(N'fct.MyTable', N'U')
            AND P.index_id = 1
    ) AS T1
    CROSS APPLY
    (
        SELECT TOP (1)
            S.UpdatedID AS IDPartitionedTable
        FROM fct.MyTable AS S
        WHERE
            $PARTITION.PF_MyTable(S.PCTimeStamp) = T1.PartitionNumber
            AND S.UpdatedID <= @IDColumnThresholdValue
        ORDER BY
            S.UpdatedID DESC
    ) AS O
) AS T2;

The plan shape this should produce is:

Notice the Top below the Index Seek. This limits the processing to one row per partition.

Or, using a temporary table to hold partition numbers:

CREATE TABLE #Partitions
(
    partition_number integer PRIMARY KEY CLUSTERED
);

INSERT #Partitions
    (partition_number)
SELECT
    P.partition_number AS PartitionNumber
FROM sys.partitions AS P
WHERE
    P.[object_id] = OBJECT_ID(N'fct.MyTable', N'U')
    AND P.index_id = 1;

SELECT
    @MaxIDPartitionTable = ISNULL(MAX(T2.UpdatedID), 0)
FROM #Partitions AS P
CROSS APPLY
(
    SELECT TOP (1)
        S.UpdatedID
    FROM fct.MyTable AS S
    WHERE
        $PARTITION.PF_MyTable(S.PCTimeStamp) = P.partition_number
        AND S.UpdatedID <= @IDColumnThresholdValue
    ORDER BY
        S.UpdatedID DESC
) AS T2;

DROP TABLE #Partitions;

Side note: accessing a system table in your query prevents parallelism. If this is important, consider materializing the partition numbers in a temporary table, then APPLY from that. Parallelism is not normally helpful in this pattern (with correct indexing) but it would be remiss of me not to mention it.

Side note 2: There is an active Connect item requesting built-in support for MIN\MAX aggregates and Top on partitioned objects.


Is there another source for untrusted foreign keys beside FK disable and BULK inserts?

Another source is a bug in MERGE that allows foreign key constraints to be ignored. This was reported by Paul White, closed as Won't Fix, and has been ignored ever since. It's one of the main reasons I've long been vocal against using MERGE at all.

MERGE query plans allow FK and CHECK constraint violations (also reported here)
MERGE with caution

There are other possible scenarios too - I haven't thoroughly checked out this one about check constraints to see if it could also affect FKs, and another one I didn't read is this one...

While she focused on the performance aspect, my co-worker Melissa Connors wrote a great post on finding and fixing untrusted foreign keys:

Trust issues hurting database performance?


SQL Server - Inconsistent, hidden machine precision error when truncating result of LOG to integer

The problem is simpler than that.  LOG outputs a floating point number, and you should always ROUND when converting form a floating point value to an int instead of truncating.

What you are doing is similar to:

select cast(0.99999999999999999999999999999999999 as int)

The result of LOG(1000,10) is not 3, which you can see

select convert(varchar(200),LOG(1000, 10), 3)

is closest to

   2.9999999999999996e+000

(although internally the significand is stored in base-2).

There's nothing inconsistent about this behavior.  LOG is an estimate based on a series expansion that converges to the logarithm, and the result is returned with a limited amount of precision.

The LOG function is designed to return a result accurate to 15 digits of decimal precision, but depending on the arguments the result might be slightly higher or slightly lower than the correct answer.


Column in query should be selected according to value from another column

select t.price, t.id
from table1 t
where (coffee_sender = tux and sent_bag_size= xl)
or (coffee_receiver = tux and received_bag_size= m)
or (coffee_sender = tux and sent_bag_size = xl AND coffee_receiver = tux and received_bag_size= m)


Oracle 12c PDB Cloning with RMAN - Aux DB connection issues

$ oerr rman 6814
6814, 1, "cannot connect to pluggable database %s"
// *Cause:  An error was received when connecting to database because
//          the database is a pluggable database.
// *Action: Connect to the root database and retry the command.

That is not how you clone a PDB.

Duplicating PDBs

When you duplicate one or more PDBs, RMAN also duplicates the root (CDB$ROOT) and the seed database (PDB$SEED). The resulting duplicate database is a fully functional CDB that contains the root, the seed database, and the duplicated PDBs.

And this is how you do it:

Cloning a Remote PDB or Non-CDB

Example:

CREATE PLUGGABLE DATABASE pdb02 FROM pdb01@pdb01_link;