How to disable case sensitivity in SQL Server 2012?

First, as you have now seen, you cannot directly alter meta-data in the system views. However, you could change the setting for a particular database using ALTER DATABASE:

ALTER DATABASE { database_name  | CURRENT }
  COLLATE collation_name;

Please note that the option to use the CURRENT keyword was introduced in SQL Server 2012.

OR, if you only want to change how a particular field in a particular table behaves for all queries (well, all that do not override the fields collation with the COLLATE clause -- see notes below), then you can do that by changing the collation for just that one field:

ALTER TABLE [TableName]
  ALTER COLUMN [ColumnName] NVARCHAR(10)
  COLLATE Latin1_General_100_CI_AS
  NOT NULL;

Just be sure to specify the exact same datatype and NULL vs NOT NULL setting that the field currently has!!

Now, a few notes about what you are trying to accomplish:

The collations starting with SQL_ are deprecated. You should use the Windows Collations. In this case, you would use Latin1_General_100_CI_AS. The _100 series are the newest versions and were introduced in SQL Server 2008.
Changing the default collation of a database does not change the collation for any string fields in any existing tables. It only affects operations involving string literals and variables that do not also involve a field in a table that would already have a collation specified. For more details about the ramifications of changing a database's collation when tables already exist (with example code showing what the true effects are, including potential errors if joining existing and new tables on string fields), please see my answer here: Latin1_General_BIN performance impact when changing the database default collation
Any string operation, whether it involves literals, variables, or fields in tables, can have the default collation overridden by using the COLLATE clause. For example:

SELECT *
FROM   dbo.SomeTable st
WHERE  st.SomeField = @StoredProcedureParameter COLLATE Latin1_General_100_CI_AS;

If you are only concerned about some (not all) queries on some (not all) tables, then this is a great place to start.
The collation actually used for a string operation is determined by looking at the various levels of where to specify a collation. Please see Collation Precedence for more info.


Database-design for a small filter / search tag function

In my opinion option 3 is the best.

Option 1 has the problem that you always have to use a like on the tags field to find the articles.

Option 2 will cause a rewrite of the tag each time that an article is added or deleted. When there are a lot of articles for the tag then the record becomes long.

Both option 1 and 2 will end up with 'big' rows which means less rows in a physical block which means more physical reads and 'slower' result.

Option 3 has a simple setup. It is the way to present an M-to-N relation. Put an index on the name of the tag to help to speed up the finding of the tags (will not help much if you have a very limited number of tags). Adding an article or adding a tag to an article will be fast too.


What are the drawbacks of using DOMAINS to store metadata about the column?

While it's not a bad idea to store the meta-data as the type name, I would not. That's a slippery slope that will lead to bizzare place where ints become more specific types that provide nothing (insofar as constraints, what DOMAINs are for). This meta-data about what is in the column belongs in the column name, not the type.

Further you can explain it with a PostgreSQL COMMENT

COMMENT ON COLUMN mycatalog.myschema.mytable IS
  $$ Not really a UUID, just suing it to store an MD5 $$;

Inability to change underlying types

From a_horse_with_no_name,

You can no longer change the definition of a domain as long as it is used by at least one column. E.g. a domain defined as varchar(10) can not be changed to varchar(20) if it's used.

Other problems

There are some oddities regarding ranges over domains
For any functions that use the domain, you will want to ensure that the types from literals are in the domain type.

CREATE DOMAIN foo AS text;
CREATE FUNCTION f(foo)
RETURNS bool
AS $$
  SELECT false
$$ LANGUAGE SQL;

SELECT f('foo'); -- returns false

CREATE FUNCTION f(text)
RETURNS bool
AS $$
  SELECT true;
$$ LANGUAGE SQL;

SELECT f('foo'); -- returns true

I use domains liberally, but only to add constraints so that they're centrally managed and not in a hodgepodge of CHECK statements.


Viewing how much transaction log is used in Simple Recovery Model

Seems like an appropriate way to do it.

Create a logging table:

CREATE TABLE dbo.LogSpace
(
  dt DATETIME NOT NULL DEFAULT SYSDATETIME(),
  dbname SYSNAME,
  log_size_mb DECIMAL(22,7),
  space_used_percent DECIMAL(8,5),
  [status] BIT
);

Do this before and after your load:

INSERT dbo.LogSpace(dbname, log_size_mb, space_used_percent, [status])
EXEC sp_executesql N'DBCC SQLPERF(LogSpace) WITH NO_INFOMSGS;';

Optionally, remove any rows not related to this specific database:

DELETE dbo.LogSpace WHERE dbname <> N'yourdb';

Then you can compare the before and after size/space used for any given date, or for all dates you have collected.

;WITH x AS
(
  SELECT dbname, dt,
  duration = DATEDIFF(SECOND, LAG(dt) OVER
    (PARTITION BY dbname ORDER BY dt), dt),
  [current] = space_used_percent,
  previous = LAG(space_used_percent) OVER
    (PARTITION BY dbname ORDER BY dt),
  rn = ROW_NUMBER() OVER
    (PARTITION BY dbname ORDER BY dt),
  log_size_mb
 FROM dbo.LogSpace
)
SELECT * FROM x WHERE rn % 2 = 0;

Keep in mind that checkpoints that happen during your process can actually make log space be re-used; I remember doing some performance testing recently and after certain operations the space_used_percent actually went down. So you may want to take the max observed over a few days (and maybe run it more often - in which case you want a slightly different query, that doesn't assume pairs of consecutive rows are related to any specific activity), rather than just relying on how it ended up after the load.

Also make sure that the autogrow settings for the log file are reasonable - you don't want 1MB or 10%, but you don't want 10GB, either. Since an autogrow event for a log file will (a) make all transactions wait and (b) does not benefit from instant file initialization, you want a good balance between how many times the log file has to grow during an abnormal operation like your data cleanup, and how long it takes any individual growth event to happen. If that event was recent enough, you can review these events in the default trace to see how long it took then.


Slow postgresql query with pagination and filtering

I now believe my question to be a duplicate of Slow ORDER BY with LIMIT.

I simplified the query (taking out the complication of a join table) to what matters.

Fast query

explain (analyze, buffers)
SELECT *
FROM big_table

where small_table_id = 822573
ORDER BY sort_key_1, sort_key_2, sort_key_3

Sort  (cost=24642.79..24686.64 rows=17540 width=113) (actual time=0.983..1.471 rows=227 loops=1)
  Sort Key: sort_key_1, sort_key_2, transmitter_id
  Sort Method: quicksort  Memory: 56kB
  Buffers: shared hit=1 read=8
  ->  Index Scan using big_table_small_table_index on big_table  (cost=0.56..23406.36 rows=17540 width=113) (actual time=0.055..0.514 rows=227 loops=1)
        Index Cond: (small_table_id = 822573)
        Buffers: shared hit=1 read=8
Total runtime: 1.941 ms

Slow query

Note: only change is the addition of a  LIMIT clause.

explain (analyze, buffers)
SELECT *
FROM big_table

where small_table_id = 822573
ORDER BY sort_key_1, sort_key_2, sort_key_3

LIMIT 1

Limit  (cost=0.56..6061.27 rows=1 width=113) (actual time=206737.547..206737.550 rows=1 loops=1)
  Buffers: shared hit=13259226 read=303563
  ->  Index Scan using pagination_index on big_table  (cost=0.56..106304766.93 rows=17540 width=113) (actual time=206737.541..206737.541 rows=1 loops=1)
        Filter: (small_table_id = 822573)
        Rows Removed by Filter: 15520057
        Buffers: shared hit=13259226 read=303563
Total runtime: 206737.643 ms

Fast query, with limit

As suggested in the linked question, disabling index scans makes the limited query fast:

SET enable_indexscan = OFF;

explain (analyze, buffers)
SELECT *
FROM big_table

where small_table_id = 822573
ORDER BY sort_key_1, sort_key_2, sort_key_3

LIMIT 1

Limit  (cost=62302.77..62302.77 rows=1 width=113) (actual time=0.897..0.899 rows=1 loops=1)
  Buffers: shared hit=9
  ->  Sort  (cost=62302.77..62346.62 rows=17540 width=113) (actual time=0.894..0.894 rows=1 loops=1)
        Sort Key: sort_key_1, sort_key_2, transmitter_id
        Sort Method: top-N heapsort  Memory: 25kB
        Buffers: shared hit=9
        ->  Bitmap Heap Scan on big_table  (cost=388.50..62215.07 rows=17540 width=113) (actual time=0.040..0.477 rows=227 loops=1)
              Recheck Cond: (small_table_id = 822573)
              Buffers: shared hit=9
              ->  Bitmap Index Scan on big_table_small_table_index  (cost=0.00..384.11 rows=17540 width=0) (actual time=0.030..0.030 rows=227 loops=1)
                    Index Cond: (small_table_id = 822573)
                    Buffers: shared hit=4
Total runtime: 0.932 ms

Fast query, with Common Table Expression

The following query is also fast:

SET enable_indexscan = ON;

explain (analyze, buffers)
with t as (
SELECT *
FROM big_table

where small_table_id = 822573
ORDER BY sort_key_1, sort_key_2, sort_key_3
)
select * from t
LIMIT 1

Limit  (cost=24686.64..24686.66 rows=1 width=3172) (actual time=1.011..1.013 rows=1 loops=1)
  Buffers: shared hit=9
  CTE t
    ->  Sort  (cost=24642.79..24686.64 rows=17540 width=113) (actual time=1.001..1.001 rows=1 loops=1)
          Sort Key: big_table.sort_key_1, big_table.sort_key_2, sort_key_3
          Sort Method: quicksort  Memory: 56kB
          Buffers: shared hit=9
          ->  Index Scan using big_table_small_table_index on big_table  (cost=0.56..23406.36 rows=17540 width=113) (actual time=0.025..0.504 rows=227 loops=1)
                Index Cond: (small_table_id = 822573)
                Buffers: shared hit=9
  ->  CTE Scan on t  (cost=0.00..350.80 rows=17540 width=3172) (actual time=1.007..1.007 rows=1 loops=1)
        Buffers: shared hit=9
Total runtime: 1.070 ms


SSIS 2012 Ftp parallel/asynchronous multiple file download

Native, no.

Heck, I barely classify the out of the box FTP component as a real component. It meets some technical specification for FTP only for the most rudimentary operations. I've usually gone the route of calling ftp.exe from the Execute Process Task with a parameter file or just used the .NET libraries for doing so.

I had never thought about parallelizing FTP downloads but this question over on SO looks like a valid implementation of it https://stackoverflow.com/questions/18117536/c-sharp-multiple-download-from-ftp-using-parallel-task-duplicate-download-issu

Depending on how you design things, I've come to the conclusion that having packages download files and then perform operations on the file(s) it might have found is a painful pattern. Instead, I find I've had better results with separating those activities and making an assumption that the consuming package will only run if data already exists. This allowed me to make radical changes to how I acquired the data (we went from SFTP to FTP with no core package change) without having to validate/retest the processing of the data. Might not be an issue for me but simplified my compliance life.

The net result of the above was that my agent job went from "run Package" to "run ftp package", "test for existence", "run processing package" or "alert that no file found". Modularization allowed us more flexibility as well as letting more people work on the problem vs one person working on a monolithic package.


AlwaysON - Force users connection to use Readable Secondary Nodes

There is no way to complete this with just SQL Server and the interfaces it exposes. In order to do this you'll either need an appliance between the clients and SQL Server (the users connect to the appliance endpoint and the appliance does the redirection) or you can implement your own through a cname and some type of configurable load balancer like an F5.

There is nothing in 2016 to change this behavior AFAIK.


Convert it to CHAR  first:

ORDER BY CAST( AccounNumber AS CHAR )


You are trying to import a dump from a recent PostgreSQL version into an older PostgreSQL server. That is not supported.

Your only option is to manually edit the dump and fix any syntax that the older server doesn't understand.

Check for the first error message you get when restoring the dump, later ones can be consequences of the first one.


Logically the joins are resolved in the order of the ON clauses from left to right.

The output of each join is a virtual table that goes into the next join.

So for the query in your question the virtual table result of A LJ B is then right joined onto C. The join condition of B.Col3 = C.Col4 will lose any null extended rows preserved by the original left join effectively turning the first join back into a inner join and the resulting virtual table (which preserves all rows from C) is then inner joined onto D.

So your initial query can be simplified as

SELECT *
FROM TBLA A
INNER JOIN TBLB B ON A.Col1 = B.Col2
RIGHT JOIN TBLC C ON B.Col3 = C.Col4
      JOIN TBLD D ON C.Col5 = D.Col6

Which is effectively the same as (A IJ B) ROJ (C IJ D)

The order of the ON clauses is not necessarily the same as the order the tables appear in the query. This could also be rewritten as (C IJ D) LOJ (A IJ B)

SELECT *
FROM   TBLC C
       INNER JOIN TBLD D
         ON C.Col5 = D.Col6
       LEFT JOIN TBLA A
                 INNER JOIN TBLB B
                   ON A.Col1 = B.Col2
         ON B.Col3 = C.Col4

The position of the on clauses means that the outer join is carried out between the two virtual tables resulting from  (C IJ D) and (A IJ B) rather than just on a single table.

In your second query conceptually the virtual table A x C is left joined onto B preserving the entire cartesian product then the result of that is joined onto D with the predicate C.Col5 = D.Col6. This eliminates any rows from the final result that do not inner join between C and D meaning it is equivalent to

SELECT *
FROM   TBLC C
       JOIN TBLD D
         ON C.Col5 = D.Col6
       CROSS JOIN TBLA A
       LEFT JOIN TBLB B
         ON A.Col1 = B.Col2
            AND B.Col3 = C.Col4


Copying Oracle 11g express Database from one server to another

You do not state which version you use, so I assume you use version 10.x or 11.x.

Do a full export of your database using Oracle Data Pump, existing since Oracle 10: expdp (export dump) and then re-import it into another database using impdb (import dump).

Use expdp help=y to get the full set of parameters. Parameters are given in the form param=value, not --param=value as one would expect.


MongoDB to MySQL Replication Automatically

You can use ETL tools like Talend,pentaho,informatica and other tools..my suggestion is that use Talend ETL tool it provides lots of features and its a open source tool.


Execution Plans Differ for the Same SQL Statement

The query optimizer has a number of choices when constructing an execution plan for this query. Among the many strategies available, it can choose between hash join and nested loops join. Which one it decides to use depends sensitively on the statistical information available, and other factors like the amount of memory configured for SQL Server to use.

It just so happens that the optimizer chooses a nested loops strategy in one case, and a hash join in the others. If you were to force the use of a hash join (e.g. using the query hint OPTION (HASH JOIN) where a nested loops join is currently being used, you would find that the estimated cost of the nested loops plan appears to be the cheaper option to the optimizer.

This is not a bug. It is a fairly routine example of plan choice being sensitive to the statistical information available (among other things). The fact that the nested loops join performs so poorly in reality is a consequence of the query and database design not being very optimizer-friendly. Given very low-quality information to work with, the optimizer's plan selection is barely better than a guess.

Anyway, assuming you need to avoid the poor-performing plan shape without changing the source code (a reason to prefer stored procedures over in-lining SQL, by the way) you have two main options:

The first is to use a plan guide to force the 'good' plan shape for the target query. This is reasonably advanced work if you haven't worked with plan guides before. Extra steps will be involved if the literal values specified in the example could ever be different.

A second option is to present the optimizer with a more useful index to use. In this case, this involves adding a computed column (a fast metadata-only operation) and then indexing the new column:

-- Metadata-only operation
ALTER TABLE dbo.InHouse_CSV_Backup
ADD MERSNUMBER_CC AS
    REPLACE(LTRIM(RTRIM([MERSNUMBER])),'-', '');

-- Index on computed column
CREATE NONCLUSTERED INDEX
    IX_dbo_InHouse_CSV_Backup__MERSNUMBER_CC
ON dbo.InHouse_CSV_Backup (MERSNUMBER_CC)
INCLUDE (MERSNUMBER);

The query is very likely to use the index, resulting in better plan stability and most likely good performance too. It is not a perfect solution by any means, but it is a relatively simple and unobtrusive one, given the information available.

Potential areas for future improvement:

Consider stored procedures instead of embedding SQL in the application
Consider NOT EXISTS instead of outer join with null rejection
Avoid the unnecessary RTRIM when comparing strings with trailing spaces
Be careful with implicit type conversion. For example, MERSMin is varchar(300) but MERSNUMBER is nvarchar(31) (Unicode). The results of expressions have similar derived types, e.g. the REPLACE is deemed to produce an nvarchar(4000).


Which Database Version do you have?

From Oracle 11g onward you should be using the SCAN listener. The SCAN listener knows where the database is located in the RAC and how much load is on that node.

Further more you should define services for long ops and short ops for better load balancing.


Why is it considered that a set unnormalizes a database absolutely?

The reference for this post is an amazing book called Database System Concepts 6th Edition which I recommend you read.

In the book, page 328, it states:

  A domain is atomic if elements of the domain are considered to be indivisible units. We say that a relation schema R is in first normal form (1NF) if
  the domains of all attributes of R are atomic.

You might be wondering "but why!?", it's best explained using a practical example.

Let's look at your example with colors. Lets say we have 2 scenarios, 1.) where the table is not in 1NF, 2.) where the table is in 1NF.

1.)

Id          Painting_name                  Author                         Used_colors
----------- ------------------------------ ------------------------------ ---------------
1           Some_Painting                  John                           Blue, red, Yellow
2           Monalisa                       Leonardo da Vinci              orange, black, White, red, Yellow

Although this might seem intuitive to you, consider what happens when you want to query this table. One, you have inconsistent upper and lower case (which you would have to check with your query for both cases) two, if used_colors isn't an array, you would either have to convert it to an array or use extra steps just to check the data you need (like using a string_split function in SQL Server 2014 and later).

This causes performance issues and ends up being a hassle every time you want to check for something. If you are wondering what stops a person from typing in black_white_yellow instead? That question is answered in 2NF and 3NF, foreign key constraints etc...

2.)

Id          Painting_name                  Author                         Used_colors
----------- ------------------------------ ------------------------------ ---------------
3           Some_Painting                  John                           Blue
4           Some_Painting                  John                           red
5           Some_Painting                  John                           Yellow
6           Monalisa                       Leonardo da Vinci              orange
7           Monalisa                       Leonardo da Vinci              black
8           Monalisa                       Leonardo da Vinci              White
9           Monalisa                       Leonardo da Vinci              red
10          Monalisa                       Leonardo da Vinci              Yellow

In this case, every single row is atomic and unique. What did we gain by doing this? You don't have to think about handling arrays, since every single row now is atomic you can clearly check for what you need quickly and efficiently.

And just to give you an idea of how big of a hassle it could become, here are some posts related to the issue of searching for values in a comma separated column:

How can I check whether a number is contained in a comma separated
list
Using Comma Separated String Value in a Select Statement

There are also many different approaches, none of which are really elegant to say the least (at least pertaining to the problem of 1NF). So, basically by using the 1NF you go from using code like the ones mentioned in those posts above to simple stuff like:

SELECT * FROM Paintings WHERE Used_colors LIKE 'BLUE'

Which helps in both readability and performance.

There is one thing you need to keep in mind, 1NF is only the starting point of the normalization process. The 1NF alone is practically never used in any DB out there, after the 1NF comes the 2NF where you would have to split this table into two separate tables. Used_colors will be made into its own table called Colors. At this point you will reach the cardinality issue which is also covered in the book mentioned above.

One last thing, there are many cases where you're going to come across databases that break the 1NF on one or more tables while adhering to the rules of 2NF, 3NF and even 4NF. For example PostgreSQL has json data type which immediately breaks the 1NF rule (as you can save multiple keys and values in json). The general rule of thumb goes like this: Always follow the normal forms unless you really know what you're doing. Since the moment you introduce such variables you could cause inconsistencies throughout the database and you will most likely lose on performance.

Also, as Paul said in the comment below, there is another point of view which Christopher. J. Date supports(who is a famous researcher and writer on relational DB theory as well as being one of the people with Ted Codd who helped push the relational model). This outlook stabs at the idea of atomic values in 1NF, saying that the whole term "atomic" is ambiguous. The idea behind this is simple, you can say that almost no data type is atomic under the current definition of 1NF. To explain this, lets take a look at an example:

Lets say you have a string Hello World. You have functions in every DBMS that decomposes this string into smaller chunks (like the SUBSTRING or LEFT / RIGHT functions) which means that string isn't really an atomic value as everything can be decomposed. This outlook is similar to data types such as json, you can decompose both string and json, so why is one considered atomic while the other not? That's why C. J. Date considers the current definition of 1NF as ambiguous, since almost all data types can be decomposed in one way or another. If you access json or xml data types as a whole unit without decomposing them, there is nothing to say that json or xml data type isn't atomic.

You can find an interesting paper on The Third Manifesto, they (C. J. Date and Hugh Darwin) made their paper on Databases, Types and the Relational Model The Third Manifesto publicly available. It's also an interesting read which will send you to interesting other articles and topics in general.


How to mark the default row to select in a many to many relationship

A UNIQUE constraint on (product_id, default) won't work since you can have several images that are not default for each product. A similar approach is to add a display_order attribute:

CREATE TABLE product_images
( product_id ... NOT NULL
      references ...
, image_id ... NOT NULL
      references ...
, display_order SMALLINT NOT NULL
,     PRIMARY KEY (product_id, display_order) )

To get the default image for a product choose the one with the smallest display_order for that product.

Depending on your business requirements you may add an alternative key. Examples:

-- An image may not be reused between products
ALTER TABLE product_images ADD CONSTRAINT ...
    UNIQUE (image_id)

-- An image may not be used several times for each product
ALTER TABLE product_images ADD CONSTRAINT ...
    UNIQUE (image_id, product_id)

To get the default image for a product:

SELECT image_id
FROM product_images
WHERE product_id = ?
ORDER BY display_order
LIMIT 1;

EDIT: I keep forgetting this bug in MySQL ...

To assign a new default image for a product:

UPDATE product_images x
    SET display_order = (SELECT min from (SELECT MIN(display_order)-1 as min                                        FROM product_images
                                          WHERE product_id = ?) as y)
WHERE product_id = ?
  AND image_id = ?;

or possibly:

UPDATE product_images x
    SET display_order = (SELECT min
                         FROM (
                             SELECT display_order-1 as min
                             FROM product_images
                             WHERE product_id = ?
                             ORDER BY display_order LIMIT 1
                         ) as y
                        )
WHERE product_id = ?
  AND image_id = ?;


Help installing SQL Server 2017 - VS Shell installation has failed with exit code 1638

I experienced this problem as well with SQL Server 2017 Developer and it appears to be just bad planning on the part of the SQL Server installation package people.  The problem is that Visual Studio 2017 installs the Microsoft Visual C++ 2017 Redistributable (x86) and (x64) and the SQL Server installation tries to install the Microsoft Visual C++ 2015 Redistributables, which it can't because the SQL Server installation doesn't allow you to install an older version with the newer version installed.

This Microsoft Support article presents their explanation of the problem, and their recommended workarounds.

Solution 1: Install SQL Server first before installing Visual Studio 2017.

Solution 2 (what I did):

Uninstall the Microsoft Visual C++ 2017 Redistributable (x86) and (x64),
install SQL Server,
then reinstall the Microsoft Visual C++ 2017 Redistributable (x86) and (x64).

They are a little hard to find, so here are the direct links:

X64: https://go.microsoft.com/fwlink/?LinkId=746572

x86: https://go.microsoft.com/fwlink/?LinkId=746571

Note: If you don't have Microsoft Visual C++ 2017 Redistributable installed at all, just install it from one of the above links, and retry installing SQL Server. It should work now.

You may have to repeat these steps when installed additional named instances of SQL Server 2017.


how to replace user's names with random names?

If you decide that the Update is too slow, I suggest the following will be about 1000 times as fast.

Loop (can be done in a Stored Proc)...

[Re]Create a table with randomly ordered set first_names, with a `PRIMARY KEY of 1..5000.  Ditto for last_names (a second table).
Multi-table UPDATE the 'next' 5000 rows joined to the two random tables.  Use ON Users.id % 5000 = RandomFirstNames.id (etc)

End Loop

Shuffling the table (step 1 of the loop) is something like

CREATE TABLE RandomFirstNames (
    id SMALLINT UNSIGNED AUTO_INCREMENT,
    first_name VARCHAR(...),
    PRIMARY KEY(id) )
SELECT first_name FROM FirstNames ORDER BY RAND();

After OP's UPDATE

Don't do

SELECT count(id) INTO count_names FROM _RandomFirstNames;

Instead, do this once:

SELECT @mask_ct := COUNT(*) FROM _masked_names;

and use @mask_ct instead of count_names;

As for the skipped ids, CREATE TABLE _RandomFirstNames without an id, then ALTER TABLE _RandomFirstNames ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY; to get the ids.  This should give you ids without gaps (unless you are on a multi-Master cluster of any kind).


How do I rebuild single full text catalog (or why is "rebuild" full text catalog option disabled)

Unless there's something weird going on, this state occurs when the catalog is currently being populated.

You can see the current state of the catalog by looking in the Properties window of the catalog.

So... wait until the catalog has finished populating, and then refresh the node in SSMS.


How to migrate a huge application to CONCAT_NULL_YIELDS_NULL ON

Writing a parser to change specific bits of your queries would indeed require some effort. Adding just a single line to every one of them, on the other hand, seems a relatively easy task. So, you could mass-update all existing SQL queries/batches in your application with this line added to the beginning of each batch:

SET CONCAT_NULL_YIELDS_NULL OFF;

and re-configure the DB layer to issue SET CONCAT_NULL_YIELDS_NULL ON; (or just omit it if ON is the default) on connection from then on.

That would allow you to review/modify each existing query in your own time while forcing all new queries, if any, to comply with the new behaviour.

Each existing query, once reviewed, would be stripped off the added SET statement. You could review the queries in groups based on the objects they work with, so that new features could be rolled out on those objects sooner (because all the relevant queries would then be executing in the context of CONCAT_NULL_YIELDS_NULL = ON).


Unable to find "Local" window while debugging SSIS package

You're looking in the correct menu for the Locals (Ctrl+Alt+V, L) window. The issue is that this is a context sensitive menu.

With an SSIS project open, I see a similar menus as you under Debug/Windows

However, if I set a breakpoint and launch the debugger, then I'll get the full suite of debugging windows available under the Debug/Windows menu


What configuration to use for PGbouncer over a WAN link

Well, applications staying '<IDLE> in transaction' is really an application-level problem, not something pgBouncer can do much about.

First, you should figure out how long your applications are remaining '<IDLE> in transaction' for, and go after the egregious offenders. Since your clients are far away on a WAN link, it is perfectly normal for clients to be '<IDLE> in transaction' for, say, a few hundred milliseconds between short queries (i.e. network latency plus some processing time on the client side between subsequent queries). Check CURRENT_TIMESTAMP - query_start in pg_stat_activity, and sort by the largest differences to find the real offenders. A minute or two should cause some concern, hours-long you should get rid of immediately.

If you have a large codebase and many clients, it can be tricky to nail down exactly where in your code such '<IDLE> in transaction' connections are originating (and may even be a framework problem instead of from your own codebase -- Django had a bad history of causing mysterious '<IDLE> in transaction' connections). I recommend setting a different application_name for separate applications within your codebase, to better help you track where these connections are coming from.

Finally, there is a pending patch to allow Postgres to kill off '<IDLE> in transaction' connections you might be interested in:
http://www.postgresql.org/message-id/538DC843.2070608@dalibo.com

If we're lucky, we might have that idle_in_transaction_timeout (or whatever name it ends up with) in Postgres 9.5.


If I understand correctly, the query does a count in greatest-n-per-row type of query where n takes 2 values (40 and 100).

This can probably be solved better by rewriting the subquery. The easier to write might be to do a join of two queries:

SELECT
    a.motif AS cntA, b.motif AS cntB, a.crossvalnum
FROM
    (query with limit = 40) AS a
  JOIN
    (query with limit = 100) AS b
      ON b.crossvalnum = a.crossvalnum ;

Here is another way by moving the counts inside the subquery:

SELECT   MIN(motif) AS cntA,
         MAX(motif) AS cntB,
         crossvalnum
FROM     (SELECT    ROW_NUMBER() OVER
                        ( PARTITION BY crossvalnum
                          ORDER BY crossvalnum, pvalue DESC, margstat
                        ) AS r,
                    COUNT(*) OVER
                        ( PARTITION BY crossvalnum
                        ) AS c,
                    COUNT(seqindex) OVER
                        ( PARTITION BY crossvalnum
                          ORDER BY crossvalnum, pvalue DESC, margstat
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                        ) AS motif,
                    crossval.crossvalnum
         FROM       data
                    INNER JOIN datasubgroup
                    ON         data.datasubgroup_id=datasubgroup.id
                    INNER JOIN crossval
                    ON         datasubgroup.crossval_id=crossval.id
         WHERE      data.seqindex                      =ANY(crossval.sample)
         OR         data.seqindex                  IS NULL
         )
         AS q
WHERE    r IN (LEAST(c, @limitA), LEAST(c, @limitB))
GROUP BY crossvalnum ;


extract() the epoch from the time component after casting to time (effectively removing the date component):

SELECT extract(epoch FROM ts::time) AS seconds_of_day

You get the "number of seconds", including fractional seconds if there are any.
Very short and fast.

Test (with timestamps in unambiguous ISO format):

SELECT extract(epoch FROM ts::time) AS seconds_of_day
FROM  (
   VALUES
     ('2014-10-12 00:00:30'::timestamp)   --    30
   , ('2014-10-12 00:01:30')              --    90
   , ('2014-10-12 00:02:00')              --   120
   , ('2014-10-12 12:00:00')              -- 43200
   , ('1999-12-23 23:59:59')              -- 86399
   , ('1999-12-23 23:59:59.123456')       -- 86399.123456
  ) t(ts);

db<>fiddle here


Log file shrinking automatically due to UserShrinkSize property?

I couldn't add a comment so I will try to comment here:

There are 2 ways how SQL Server clear the t-log:

database using SIMPLE recovery model - when a checkpoint occurs, log will clear
database using FULL or BULK_LOGGED recovery model - when you issue a log backup

the only time the t-log will shrink if you issue SHRINKFILE. Maybe you are confused about log shrinking and log clearing?..

Update:

I was able to reproduce the positive value on UserShrinkSize column. The value on UserShrinkSize is the number of (8kb) pages needed to be shrink. -1 means default (none). So when you see a positive value, that means it tries to shrink the log but it can't because there's active transaction going on.

Tested on SQL Server 2016:

USE master
GO
DROP DATABASE IF EXISTS [UserShrinkSizeTest];
GO
CREATE DATABASE [UserShrinkSizeTest]
 ON  PRIMARY
( NAME = N'UserShrinkSizeTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER16\MSSQL\DATA\UserShrinkSizeTest.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'UserShrinkSizeTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER16\MSSQL\DATA\UserShrinkSizeTest_log.ldf' , SIZE = 8192KB , FILEGROWTH = 8192000KB )
GO
ALTER DATABASE [UserShrinkSizeTest] SET COMPATIBILITY_LEVEL = 130
GO
ALTER DATABASE [UserShrinkSizeTest] SET RECOVERY FULL
GO

Build your table:

USE [UserShrinkSizeTest];
GO

DROP TABLE IF EXISTS [dbo].[RecordsToProcess];
GO
CREATE TABLE dbo.RecordsToProcess
(AccountID char(8000)
,UserID int
,ServerID int
,HomeID int
,UserTypeID int
,UTCDateCreated DATETIME2(7))
GO

open a new query window and execute the insert:

USE [UserShrinkSizeTest];
GO

BEGIN TRAN
INSERT INTO dbo.RecordsToProcess (AccountID, UserID, ServerID) VALUES (REPLICATE('abcd', 5000000),1,1)
GO 50000

While the insert window is executing, open another window for shrink and execute it at the same time:

USE [UserShrinkSizeTest]
GO
DBCC SHRINKFILE (N'UserShrinkSizeTest_log' , 80)
GO

Now, you can check the UserShrinkSize:

DBCC TRACEON(3604)
DBCC fileheader([UserShrinkSizeTest])
--DBCC PAGE ([UserShrinkSizeTest],1,0,3)
DBCC TRACEOFF(3604)

as you can see below, the value is now positive. 65024 pages needed to be shrink.

you will see the change in value when you issue the SHRINKFILE and the t-log can't be shrink due to active transaction.

you can also check the changes using the fn_dblog function:

USE [UserShrinkSizeTest]
GO
SELECT Operation, Context, [Description] FROM fn_dblog(NULL,NULL)
WHERE [Description] LIKE '%Shrink%' AND Context = 'LCX_FILE_HEADER'

There is no magic about the UserShrinkSize, the value have changed because we issued/attempted DBCC SHRINKFILE during active transaction. Before shrinking the log, make sure to check the log_reuse_wait_desc and see what's holding the t-log. Investigate on why the t-log did not clear and why t-log expanded unexpectedly.

We should not worry about the UserShrinkSize value rather we should focus on how to properly manage the t-log. properly size the log, check long running transaction that cause log to grow, etc. If we take care of t-log the value will always be -1. :)

To bring back the value to -1, from my example I need to rollback the inserts. Then issue a SHRINKFILE on t-log (pre-size it to an appropriate size) and the log_reuse_wait_desc is now showing as NOTHING.

HTH


The only group that failed over was the Available Storage group, which you aren't using, so it is something you can just ignore.  If you want to increase the number of missed heartbeats that will cause it to fail over, change the SameSubnetThreshold accordingly.

(get-cluster).SameSubnetThreshold = 15

See Clustering and High-Availability for details.


Select statement on a table with constant inserts

It depends on transaction isolation level.

Your class of problem is called "phantom read", and will only be circumvented when your transaction isolation level is "serializable" or above. SQL Server has a default level of "read comitted", so you can read "too few" rows.

See https://en.wikipedia.org/wiki/Isolation_(database_systems)#Phantom_reads .


Optimize query to filter active IDs for a given day from big table

You could try not exists:

select distinct user_id
from users u1
where u1.timestamp='date x'
  and u1.value='on'
  and not exists (
      select 1
      from users u2
      where u1.user_id = u2.user_id
        and u2.value='off'
  );

If you want more qualified guesses, edit your question with CREATE TABLE statement including keys and indexes.


Elastic queries across subscriptions in Azure SQL

Yes Elastic queries across subscription is possible.

There are several limitations to this feature. For example:

  You must possess ALTER ANY EXTERNAL DATA SOURCE permission. This
  permission is included with the ALTER DATABASE permission. ALTER ANY
  EXTERNAL DATA SOURCE permissions are needed to refer to the underlying
  data source.

  Authentication using Azure Active Directory with elastic queries is
  not currently supported.

Read this for details: Azure SQL Database elastic query overview (preview)

Create 2 resource groups in two different subscriptions.
Create one Azure SQL Server and one empty Azure SQL database in each resource group.
Then you can follow along with the following code.

I used the following names in my setup:

# The logical server name: Use a random value or replace with your own value (do not capitalize)
$SqlServerName1 = "taiobeqdemo1"
$SqlServerName2 = "taiobeqdemo2"

# The database name
$customerDatabase = "taiobCustomer"
$orderDatabase = "taiobOrders"

--Connect taiobeqdemo1.database.windows.net
--Change database context to taiobCustomer

SET NOCOUNT ON;
CREATE TABLE [dbo].[CustomerInformation](
    [CustomerID] [int] NOT NULL,
  [CustomerName] [varchar](50) NULL,
  [Company] [varchar](50) NULL
CONSTRAINT [CustID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
)
GO
INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (1, 'Jack', 'ABC');
INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (2, 'Steve', 'XYZ');
INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (3, 'Lylla', 'MNO');
GO

--Connect taiobeqdemo2.database.windows.net
--Change database context to taiobOrders

CREATE TABLE [dbo].[OrderInformation](
  [OrderID] [int] NOT NULL,
  [CustomerID] [int] NOT NULL
  )
GO
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (123, 1);
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (149, 2);
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (857, 2);
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (321, 1);
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (564, 8);
GO

--Connect taiobeqdemo1.database.windows.net

--Change database context to taiobCustomer

CREATE USER taiobelasticquery WITH PASSWORD = 'taiobelasticque$0';
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'taiobelastic$0';
GO

CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred
    WITH IDENTITY = 'taiobelasticquery',
    SECRET = 'taiobelasticque$0';
GO
--Create External data source
CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH
    (TYPE = RDBMS,
  LOCATION = 'taiobeqdemo2.database.windows.net',
  DATABASE_NAME = 'taiobOrders',
  CREDENTIAL = ElasticDBQueryCred,
);
GO

CREATE EXTERNAL TABLE [dbo].[OrderInformation]
( [OrderID] [int] NOT NULL,
  [CustomerID] [int] NOT NULL)
WITH
( DATA_SOURCE = MyElasticDBQueryDataSrc);
GO

--Connect taiobeqdemo2.database.windows.net
--Change database context to taiobOrders

CREATE USER taiobelasticquery WITH PASSWORD = 'taiobelasticque$0';
GO
EXEC sp_addrolemember 'db_datareader', 'taiobelasticquery';
GO

Now you are to do run elastic query from taiobeqdemo1.database.windows.net servers taiobOrders database.

--Change context to taiobOrders

SELECT
    OrderInformation.CustomerID,
    OrderInformation.OrderId,
    CustomerInformation.CustomerName,
    CustomerInformation.Company
FROM OrderInformation
INNER JOIN CustomerInformation
ON CustomerInformation.CustomerID = OrderInformation.CustomerID;
GO


Do Triggers in the Active Secondary Database of an AlwaysOn Group Execute?

The secondary is readonly.  Triggers can't fire on the tables in the secondary because there are no ON SELECT triggers and DML triggers cannot be executed on a readonly database without a successful DML operation (update, insert, delete).

Updates made on the primary and then moved to the secondary do not fire triggers on the secondary.  Instead, data changes made by the triggers on the primary are propagated to the secondary in the same manner as the direct changes made to the table.


Can I restore a SQL Server .bak files without SQL Server?

Here is what I suggest:

build a virtual machine running Windows, with enough disk space to hold the backup. Copy the backup file there. If you don't already have the ability to build virtual machines, you could do so with free products like Oracle VirtualBox.
download and install the evaluation edition of SQL Server. Make sure you include both the database engine and Management Tools - Complete.
if the VM has enough space to hold the backup but not enough space to also restore it, you can
perform a "virtual restore" using the trial version of a product
from Red-Gate with the same name (which allows you to interact with the backup file as if it had been restored). Otherwise, restore the database the normal way.
Once the database is available (either through a normal restore or virtual restore), you can generate scripts for the schema and data in the following way:

Open Management Studio and connect to your instance.
Open Object Explorer.
Right-click your newly restored database, choose Tasks > Generate Scripts...
Click Next, Click Next
On the "Choose Script Options" page, scroll down and set "Script Data" to True
Click Next
Check all of the relevant objects and click Next
Check the tables you want and click Next
Choose to script to a file. Now you'll have a file that contains all your objects and data using SQL Server insert syntax, you will have to play with the output to get it in a format that works for Postgres (I am not privy to any minor syntax differences).

Alternatively you can try and play with the bcp utility to extract data to CSV files or similar, but you'll have to do this table-by-table or use some clever scripting (PowerShell, T-SQL, C#/SMO, etc.) to generate all of the bcp commands for you. Once in CSV files, it should be trivial to bulk load the data into Postgres (but you will still have some work to generate the tables).

As a final suggestion, if the .bak file is not ginormous, and the data is not confidential, I am more than willing to try and generate files for you in the format you need. I have plenty of Windows VMs with space, the challenge would be getting the .BAK file to a place where I can retrieve it - especially if it's larger than most of the file-sharing services support.


Does row versioning increase the size of indexes?

It seems the 14 bytes must be added to all index records as well

Why should it be added to all index records? Do you mean all indexes, or all rows of all indexes?

Only the affected rows of the affected indexes will get 14 bytes tag.

Here are 3 examples.

Suppose we have clustered table of 100 pages (with id int PK) and 1 nonclustered index on it (on another column Col1), and we change 10 keys (id) of clustered index.

All 10 rows of clustered table will get row version tag (10 rows, not 100 rows), and the 10 affected rows of nonclustered index will have this tag because every nonclustered index contains clustered index key at least at the leaf level, so 10 rows of nonclustered index will have their versions too.

You can prove it to yourself by changing 10 rows in clustered table having nonclustered index in db with RCSI and by checking sys.dm_tran_version_store: there will be 20 rows and you'll be able to distinguish clustered index rows from nonclustered index rows by their size.

...................................................

Now suppose we have the same table with 100 rows and nonclustered index on the same Col1 column. Now we make an update of id column of this heap.

If you check now sys.dm_tran_version_store you'll see only 10 rows, they are 10 rows of a heap, there is no index rows at all. This is because our index on Col1 was not changed when we updated 10 id. Our id in this case has no affect on nonclustered index that has RID as back pointer to a heap at the leaf level.

Of course if our nonclustered index had id as key field or included field, update of id would affect this index too, in this case we'd found 20 rows in row version store again.

...................................................

Example N3: table with structure Col1, Col2, Col3.
Indexes: ix_1 (Col1), ix_3(Col3).

We update Col2 that is not presented in any index. Only affected rows of the table will get row version tags, ix_1 and ix_3 will not be affected at all.

As Solomon Rutzky pointed out in his comment, these version tags will be presented in indexes until INDEX REBUILD.

Below is the cite from this book: Pro SQL Server Internals By Dmitri Korotkevitch

  Optimistic isolation levels contribute to index fragmentation. When a
  row is modified, SQL Server increases the row size by 14 bytes due to
  the versioning tag pointer. If a page is tightly packed and a new
  version of the row does not fit into the page, it will lead to a page
  split and further fragmentation. This is very similar to the
  insert/update pattern we discussed in Chapter 6, “Index
  Fragmentation.” Those 14 bytes will stay in the row, even after
  records are removed from the version store, until the index is
  rebuilt.

  If optimistic isolation levels are used, it is recommended that you
  reserve some space on the pages by using a  FILLFACTOR  of less than
  100. It reduces page splits due to row-size increases because of the version store pointers.


I believe the basic issue is that the query is doing multiple heavy table scans due to no index support.

Try adding the following indexes:

CREATE NONCLUSTERED INDEX TEST ON STOCKDEBUGTRIGGERED (ChangeDate)

CREATE NONCLUSTERED INDEX TEST ON STOCKDEBUG(ProductID, StockOld, StockNew)

There may be further tweaks, so please post the stats and execution plan with these indexes added.


It's better to have a separate table. That way your data will be normalized.

In your specific case you most likely want a list of countries the user can select from (eg. it's not a free form field). By having it be a separate table and referencing the country id you can setup a foreign key constraint to your countries table. This way your data will always be in sync as user's will not be able to enter a country that does not exist.


No, there is no knob for clearing just missing_index DMVs. You'll have to restart the service or, if you want to exclude specific tables, you could create a view against the DMVs with a filter.

The only DMVs you can clear manually, using DBCC SQLPERF, are sys.dm_os_wait_stats and sys.dm_os_latch_stats:

DBCC SQLPERF
(
     [ LOGSPACE ]
     |
          [ "sys.dm_os_latch_stats" , CLEAR ]
     |
     [ "sys.dm_os_wait_stats" , CLEAR ]
)
     [WITH NO_INFOMSGS ]


Availability Group cluster memory issue after network issues. How to dump HADR Log Block Msg Pool?

I have noticed after every WAN issue where the WAN connection is flapping, and the off site nodes constantly disconnect and reconnect, the memory of the primary server gets consumed by the "HADR Log Block Msg Pool"

Yes, this is currently by design. It is expected that the network between the two sites can handle the traffic and is available. Since it seems that's not the case, SQL Server really isn't the problem here but is manifesting as an issue. If you're going to continue to work over an unreliable and possibly extremely high latency low bandwidth connection then I wouldn't use availability groups. In fact, I'm not sure what you'd want to use as nothing would have a solid and reliable connection which seems to be the root cause of the issue.

  Is there any way to dump this HADR Log Block Msg Pool?

Inside of SQL Server? No.

  Or stop it from growing so large in the first place?

Yes, fix the connectivity issue and it won't grow. If it's prolonged connectivity issues, then remove the remote replicas from the AG and it'll stop growing. Since there are two remote replicas, the data will be sent twice which could be exacerbating the issue as it might not have been taken into account for the infrastructure available when architected.

  Server MEM: 12GB

This is an awful small amount of server memory for 364 GB of databases + OS + Cluster + AG + all of the antivirus and agents installed.


How to DELETE duplicate entries on a field

I would use a temp table

#
# Collect All IDs Your Intend to Keep
#
CREATE TABLE KeepIDList ENGINE=MyISAM SELECT id FROM users;
CREATE TABLE KeepIDList ENGINE=MyISAM SELECT id FROM users;
INSERT INTO KeepIDList (id) SELECT min_id FROM
(SELECT email,MIN(id) min_id FROM users GROUP BY email) A;
ALTER TABLE KeepIDList ADD PRIMARY KEY (id);
#
# Collect All IDs Your Intend to Delete
#
CREATE TABLE ZapIDList ENGINE=MyISAM SELECT id FROM users;
INSERT INTO ZapIDList (id)
SELECT A.id FROM users A LEFT JOIN KeepIDList B WHERE B.id IS NULL;
ALTER TABLE KeepIDList ADD PRIMARY KEY (id);
#
# From Collected Keys to Delete, Remove Keys Whose chips > 0
#
DELETE B.* FROM users A INNER JOIN ZapIDList B WHERE A.chips > 0;
#
# From Collected Keys Left to Delete, Perform DELETE JOIN
#
DELETE A.* FROM users A INNER JOIN ZapIDList B;
#
# Drop Temp Tables
#
DROP TABLE KeepIDList;
DROP TABLE ZapIDList;

I have a very good reason for using temp tables as opposed to doing a Direct DELETE JOIN of a table against itself: Performing a DELETE using a subquery that references the same table experiencing the DELETE tends to make keys disappear intermittently as the query is being optimized. I wrote about this back on Feb 22, 2011 : Problem with MySQL subquery

CAVEAT

Please test this out with a smaller dataset to make sure my answer indeed performs what you asked. Make sure you make s backup of the users table. To coin a phrase from 1960's  Mission Impossible: Should any of your data be caught or killed, I will disavow any knowledge of your actions (Start From 1:50 of the YouTube Video).


StorageEngineUsed set to TabularMetadata. For databases in this mode, you must use Tabular APIs to administer the database

It sounds like you are trying to process your tabular model (which is of version >= 1200) with XMLA script (so all in all command is in XML). That kind of tabular model has to be processed with JSON command. I had the same error message and it just turned out that I was trying to refresh the newer tabular model (of version >=1200) with XMLA command. After using JSON command - it all worked. Probably for some reason while moving the tabular model from one server to another - the version of that tabular model was somehow upgraded to >= 1200. Or you are using old SSMS which is only capable of producing XMLA commands.


Which reporting method has better performance, using linked server or using lookup in SSRS?

If you are really executing the exact same query, then there shouldn't be much of a difference in executing and returning results. I'd expect SQL server with the linked server to have an edge in the actual lookups into the results.

But this won't mean a thing if the queries you are executing against the sources are different. The differences could go either way depending on the specifics of your queries. With the linked server you could be retrieving exactly the data you need from the linked server, so things would probably be faster. But if your query is too general in its join, or retrieves an excessive number of rows from the linked server, that will cost you.

So I vote to go with whichever is more convenient for you. If you need the subquery dataset in SSRS for some other summaries, keep the separate dataset. If you comfortably think in SQL terms, go with the linked server. Then optimize if things are slow.

(It would be easy to make two situations where I could get either answer to be faster, depending on my lookups and queries even when ultimately returning the same results. The query optimizations would easily more than make up for any performance difference between the two methods. For example, with an SSRS lookup, you could use dataset caching, which would avoid waiting for other server entirely.)


SQL Database, how to set up users saving favorite songs

I would pull down MusicBrainz's recording table and simply link it to your users with either a m2m table, or as an array of their favorite recordings (1:n). Depending on whether or not you're querying on the data or just displaying it for the user.


How do I use the IF...ELSE condition in a WHERE clause?

I think the question is a little misleading & causes people to not think properly. Looks like the 3 conditions won't overlap, so all you need to do is OR the 3 statements together:

create or replace package body If_Else_Pack is

  Procedure Moving(obj_A IN varchar2,
                   obj_B  IN varchar2,
                   obj_C IN varchar2,
                   obj_D    IN varchar2,
                   cur_Result     OUT T_CURSOR) is

   begin

        open cur_Result for
        select w.assetid,
                 w.vehiclenumber,
                 w.LatLong,
                 w.CurrentSpeed,
                 w.timeOfMovement,
                 w.CurrentPlace,
                 w.curTime,
                 w.motion,
                 w.fuelR,
                 w.VehicleStart
        from waypoints1 w
        where
        (((obj_D= '0' or obj_D is null) and (obj_C= '0' oR  obj_C is null))
                 and w.customer_id =obj_A
                 and w.delegate_user_id = obj_B)
        or
        (((obj_D= '0 'or obj_D is null) and (obj_C<> '0' or obj_C is not null))
           and w.customer_id = obj_A
           and w.category_id = obj_C
           and w.delegate_user_id = obj_B)
        or
        (((obj_D<> '0' or obj_D is not null) and (obj_C= '0' or obj_C is null))
           and w.customer_id = obj_A
           and w.fleet_id = obj_D
           and w.delegate_user_id = obj_B);
END MOVING;
END IF_ELSE_PACK;

The WHERE condition can be further simplified to:

        WHERE
              w.customer_id = obj_A
          AND
              w.delegate_user_id = obj_B
          AND
              ( (obj_D = '0' or obj_D IS NULL) AND (obj_C= '0' OR obj_C IS NULL)
             OR
                (obj_D = '0' or obj_D IS NULL) AND (w.category_id = obj_C)
             OR
                (w.fleet_id = obj_D) AND (obj_C= '0' OR obj_C IS NULL)
              ) ;


What about using BETWEEN by taking the first and last month of the ranges:

SELECT *
FROM `table` `t`
WHERE
  '2018-01-01' BETWEEN
     DATE_FORMAT(start_date ,'%Y-%m-01') AND
     LAST_DAY(end_date)

As this won't use indexes an alternate is to use generated columns:

ALTER TABLE `table`
ADD start_month DATE GENERATED ALWAYS AS (DATE_FORMAT(start_date ,'%Y-%m-01')),
ADD end_month DATE GENERATED ALWAYS AS (LAST_DATE(end_date)),
ADD INDEX start_end_month(start_month,end_month)

Then the query will become the following and use the start_end_month index:

SELECT *
FROM `table` `t`
WHERE
  '2018-01-01' BETWEEN
     start_month AND end_month


512 Bytes are not being used from SQL Server's 8 KByte data page

While it is true that SQL Server uses 8k (8192 bytes) data pages to store 1 or more rows, each data page has some overhead (96 bytes), and each row has some overhead (at least 9 bytes). The 8192 bytes is not purely data.

For a more detailed examination of how this works, please see my answer to the following DBA.SE question:

SUM of DATALENGTHs not matching table size from sys.allocation_units

Using the information in that linked answer, we can get a clearer picture of the actual row size:

Row Header = 4 bytes
Number of Columns = 2 bytes
NULL Bitmap = 1 byte
Version Info** = 14 bytes (optional, see footnote)
Total Per Row Overhead (excluding Slot Array) = 7 bytes minimum, or 21 bytes if version info is present
Total Actual Row Size = 263 minimum (256 data + 7 overhead), or 277 bytes (256 data + 21 overhead) if version info is present
Adding in the Slot Array, the total space taken per row is actually either 265 bytes (without version info) or 279 bytes (with version info).

Using DBCC PAGE confirms my calculation by showing: Record Size  263 (for tempdb), and Record Size   277 (for a database that is set to ALLOW_SNAPSHOT_ISOLATION ON).

Now, with 30 rows, that is:

WITHOUT version info

30 * 263 would give us 7890 bytes. Then add in the 96 bytes of page header for 7986 bytes used. Finally, add in the 60 bytes (2 per row) of the slot array for a total of 8046 bytes used on the page, and 146 remaining. Using DBCC PAGE confirms my calculation by showing:

m_slotCnt    30  (i.e. number of rows)
m_freeCnt    146   (i.e. number of bytes left on the page)
m_freeData   7986  (i.e. data + page header -- 7890 + 96 -- slot array is not factored into the "used" bytes calculation)

WITH version info

30 * 277 bytes for a total of 8310 bytes. But 8310 is over 8192, and that didn't even account for the 96 byte page header nor the 2 byte per row slot array (30 * 2 = 60 bytes) which should give us only 8036 usable bytes for the rows.

BUT, what about 29 rows? That would give us 8033 bytes of data (29 * 277) + 96 bytes for page header + 58 bytes for slot array (29 * 2) equaling 8187 bytes. And that would leave the page with 5 bytes remaining (8192 - 8187; unusable, of course). Using DBCC PAGE confirms my calculation by showing:

m_slotCnt    29  (i.e. number of rows)
m_freeCnt    5   (i.e. number of bytes left on the page)
m_freeData   8129  (i.e. data + page header -- 8033 + 96 -- slot array is not factored into the "used" bytes calculation)

Regarding Heaps

Heaps fill data pages slightly differently. They maintain a very rough estimate of the amount of space left on the page. When looking at the DBCC output, look at the row for: PAGE HEADER:    Allocation Status   PFS (1:1). You will see the VALUE showing something along the lines of 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL (when I looked at the Clustered table) or 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL when looking at the Heap table. This is evaluated per Transaction, so doing individual inserts such as the test being performed here could show different results between Clustered and Heap tables. Doing a single DML operation for all 30 rows, however, will fill the heap as expected.

However, none of these details regarding Heaps directly affect this particular test since both versions of the table fit 30 rows with only 146 bytes remaining. That isn't enough space for another row, regardless of Clustered or Heap.

Please keep in mind that this test is rather simple. Calculating the actual size of a row can get very complicated depending on various factors, such as: SPARSE, Data Compression, LOB data, etc.

To see the details of the data page, use the following query:

DECLARE @PageID INT,
        @FileID INT,
        @DatabaseID SMALLINT = DB_ID();

SELECT  @FileID = alloc.[allocated_page_file_id],
        @PageID = alloc.[allocated_page_page_id]
FROM    sys.dm_db_database_page_allocations(@DatabaseID,
                            OBJECT_ID(N'dbo.TestStructure'), 1, NULL, 'DETAILED') alloc
WHERE   alloc.[previous_page_page_id] IS NULL -- first data page
AND     alloc.[page_type] = 1; -- DATA_PAGE

DBCC PAGE(@DatabaseID, @FileID, @PageID, 3) WITH TABLERESULTS;

** The 14-byte "version info" value will be present if your database is set to either ALLOW_SNAPSHOT_ISOLATION ON or READ_COMMITTED_SNAPSHOT ON.


With A CTE and ROW_NUMBER in conhunction with Cross JOIN you can get your wanted result.
The CTE creates a temporary table, which has the row number for all all master ids and of course the wanted member id.
The SELECTmakes a Cartesian product of the first (latest) member id and the rest of the rows(member ids) for every master id

CREATE TABLE master
    ("id" int, "member_id" int)
;

INSERT ALL
    INTO master ("id", "member_id")
         VALUES (545634, 5655656)
    INTO master ("id", "member_id")
         VALUES (545634, 653363)
    INTO master ("id", "member_id")
         VALUES (545634, 545454)
SELECT * FROM dual

CREATE TABLE members
    ("member_id" int, "audit_id" int)
;

INSERT ALL
    INTO members ("member_id", "audit_id")
         VALUES (5655656, 656566)
    INTO members ("member_id", "audit_id")
         VALUES (653363, 656565)
    INTO members ("member_id", "audit_id")
         VALUES (545454, 545245)
SELECT * FROM dual

CREATE TABLE audits
    ("audit_id" int, "update_time" DATE )
;

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

INSERT ALL
    INTO audits ("audit_id", "update_time")
         VALUES (656566, '2021-01-15 17:01:34')
    INTO audits ("audit_id", "update_time")
         VALUES (656565, '2020-05-13 17:01:34')
    INTO audits ("audit_id", "update_time")
         VALUES (545245, '2020-08-11 17:01:34')
SELECT * FROM dual;

WITH CTE AS (SELECT ma."id",me."member_id",
   Row_NUMBER() OVER (
        PARTITION BY ma."id"
        ORDER BY "update_time" ASC
                ) "rn"
from master ma
    INNER JOIN members me on ma."member_id" = me."member_id"
    INNER JOIN audits au ON me."audit_id" = au."audit_id")
SELECT a."member_id", b."member_id"
FROM (SELECT "member_id","id" FROM CTE WHERE "rn"= 1) a
CROSS JOIN (SELECT "member_id","id", "rn" FROM CTE WHERE "rn" > 1)  b
WHERE a."id" = b."id"
ORDER BY b."rn";

member_id | member_id
--------: | --------:
   653363 |    545454
   653363 |   5655656

db<>fiddle here


Moving Local PostgreSQL 8.4.20 Database to Amazon EC2 or RDS

Amazon's RDS only offers PostgreSQL versions 9.3.x, and it seems unlikely that they'll ever offer to host older versions of Postgres. So by jumping from a local 8.4 install directly to RDS, you would in effect be making two significant changes at once (jumping up several Postgres versions, as well as switching to managed hosting). That may be alright or not -- it all depends on what features you're using and depending on.

You should do some reading on RDS's limitations (no external hot standby, limited extensions, no shell access to the database instance, etc.) and benefits (hopefully much less maintenance work) and decide whether it's right for you.  Also, I suggest you walk through the steps of dumping and restoring your data into RDS and ensure your application works OK, as well as reading through the Postgres major-version release notes for 9.0, 9.1, 9.2, and 9.3, paying particular note to the incompatibilities listed to see if any of them would affect you.


Why does querying for records that violate a foreign key return incorrect results?

This is a fairly active database

If you can restore a static backup somewhere, and do analysis on that unchanging copy, I think you'll find that the weird behavior you're seeing goes away.

Read Committed

The default isolation level in SQL Server is READ COMMITTED. Under this isolation level, you read the data that is currently committed to the database. The only guarantee that it provides is that when SQL Server reads a given page, it will never return an uncommitted result (known as a dirty read).

In your case you're scanning two tables and comparing them. While SQL Server is doing that scan, there is data movement.

You read the first few pages of data and have the first bit of data.
Some of the rows you haven't read yet are updated. Perhaps the MessageSubject or some other column is updated to a longer value, and this results in a page split. Or perhaps the key column is updated and the row is physically moved "up" the index into the portion you already read.
In both cases, it's possible for a row (or many rows) to move from a place you haven't yet read to a place you already read. You'll never see the row because it moved around. This causes the row to be "missing" from your scan.
It's also possible a row moves in the other direction: from a place you've already read to a spot you haven't gotten to yet. In this case, you'll see that row twice and your results will have a mysterious double result.

Your examples use integers, but in your question you mention uniqueidentifier. Since uniqueidentifiers are random, the randomness means rows are constantly being inserting into random places on the table. This increases the chances that a page fills up and has to be split to accommodate the new page, and increases the chances you see these phenomena.

Read the section titled "Locking Read Committed Behaviours" in the above linked article for a thorough explanation.

The fix?

If you want to avoid these off phenomena, don't use the default isolation level in SQL Server. My favorite & default isolation level is READ COMMITTED SNAPSHOT, which avoids these problems. (Read about all the isolation levels here to decide which one is right for you.)

Or if it's just for a one-time analysis, you could use a database snapshot to create a static image to query, or you could stop writing to the database, or restore a copy elsewhere. Stopping writes will stop data movement, and you won't run into the problem.


Strategy to handle an SQL Server DB with too many files (BLOBs) in it?

I look after a very similar database, currently 3TB and growing 5GB per day.

Filestream (2008+) doesn't solve the backup/restore challenge.
Filestream performs better than LOB storage for files >1MB, so says Paul Randal's testing. It's workload dependent at 256KB-1MB and generally worse at <256KB.
A big plus for Filestream in some environments is that it bypasses the buffer pool and uses the Windows system cache instead.
If you put the files on a filesystem, you lose transactional consistency with the database record. You've also added the overhead of backing up millions of individual files, which can be troublesome.

Weigh up the pro's and con's for Filestream and see if it fits in your case. In our case, we've taken a different route and opted for partitioning the database so we can make use of partial availability/piecemeal restore.

One option that wasn't available to us, which you may have, is to mark older/archive filegroups as read-only. The read-only filegroup(s) can then be backed up infrequently.

If your stuck on 2005 Standard (partitioning is an Enterprise edition feature) and you have the option of read-only for history, you could tackle this the old fashioned way.

Split your table. You could consider the active/history route or date based e.g. table per month.
Put the historic data on a read-only filegroup and back it up only when you archive further data. Ensure your users understand that this cuts backup time only. Restore might take a while when you haven't got the partial availability feature.
Create a partitioned view over the tables.

One final option (which we're considering for our 3TB blobber) is to move the file data to a document database or cloud storage (e.g. AmazonS3, Azure BLOB Storage). This does introduce the transactional consistency problem I mentioned earlier but it takes the load away from those very expensive SQL Servers.


As well as management hierarchy, large businesses sometimes have divisional structures (country, area, region, city, branch) of fixed or variable depth. Geographic classification of places more generally could be a useful example too. Any taxonomy (books, living creatures, ...) or most other classification systems, depending on any additional constraints you have been given.

Or family trees. If you ignore complications where parentage is not known or legally changes, both the maternal and paternal lines are potentially infinite tree structures. If you consider them both together rather than as two separate structures connecting the same set of nodes, they form a directed graph which might also be within your spec.

If graphs generally are allowed then that opens up the field even more: for instance places connected by roads, more general relationships between people, or links between articles on something like Wikipedia or the web more generally. Heading back to the more specific (and back to a tree/forest structure) the posts on forums like Reddit of HackerNews.

Recursive structures can appear everywhere that you model things connected or related to each other.

If you are looking for existing data sets to analyse rather than things to model, then there are a great many out there to be found. /r/datasets might be a good place to start, for instance this post links to a dump of HN data.


SSRS email subscription with custom file name on the attachment

This is the first way I could think of to make this happen:

Create a data-driven subscription for the report
Specify Windows File Share as the delivery method (this would imply that you had a file share location available)
Connect to a data source
Write a query to generate the name
Specify that name as the report name in the location of your file share
Have a process by which the end-users are emailed this report - perhaps a SQL Agent job?

As a matter of personal exploration, I wonder if it'd be possible to modify the SQL Agent job that the subscription creates such that you could add a step to send the file via email.


How do I create a user-defined aggregate function? (Part 2)

Use MIN() instead of trying to invent PROD().  That way, any 0 will lead to a zero result (assuming all the values are non-negative).


How to force order of WHERE clauses in PostgreSQL?

This information is crucial:

  the first row matched is the winner (limit 1)

You do not actually need any sort order for a single result. ORDER BY is just one idea how to solve the task. I suggest a completely different approach, probably (much) faster:

SELECT * FROM tbl WHERE A1 AND B1
UNION ALL
SELECT * FROM tbl WHERE A1 AND B2 OR A2 and B1
UNION ALL
SELECT * FROM tbl WHERE A1 AND B3 OR A3 and B1
UNION ALL
SELECT * FROM tbl WHERE A2 AND B3 OR A3 and B2
UNION ALL
SELECT * FROM tbl WHERE A3 AND B3
LIMIT 1;

This is assuming each SELECT can only return a single row. Else you get an arbitrary pick or you need to add ORDER BY to individual SELECTs enclosed in parentheses for a deterministic pick. (See linked answers below.)

The beauty of it: Postgres stops evaluating as soon as the first row is found. Test with EXPLAIN ANALYZE to see "never executed" for trailing SELECTs. So you can use indexes, which might mean orders of magnitude in performance.

And you can conveniently return a default row if nothing matches. Just append one more SELECT before LIMIT 1.

BTW, this is a single query.

Related:

Optimize a query on two big tables
Select first record if none match


The user that has access problems is the member of many win groups,
even if he's a member of db_owner he has limited access if someone revoke/deny some permissions to him or to his group.

To find out restrictions you should use this query:

execute as user = 'NETONE\İbrahim.tackin';
select user_name(grantee_principal_id) as user_,
       permission_name,
       object_name(major_id) as obj_name,
       class_desc,
       state
from sys.database_permissions p
     join sys.user_token t
        on p.grantee_principal_id = t.principal_id
where state in ('D', 'R');
revert;

Here I limit the result to those roles/win groups of which problem user is a member of.

So your user has 3 DENY of UPDATE on all database:

NETONE\sql-ict-ad-rms UPDATE NULL DATABASE D
NETONE\SQLDDL UPDATE NULL DATABASE D
NETONE\SQLQRY UPDATE NULL DATABASE D

Your user will be unable to perform any UPDATE until you revoke these denies:

revoke update to [NETONE\İbrahim.tackin],[NETONE\SQLDDL],[NETONE\SQLQRY]

If you don't deny by error and the members of these 3 groups should not be able to update, you should exclude all your users that should be able to update from all these 3 groups.

Note you have same 3 deny to make a delete.

The permissions of any user is given by the "sum" of all the permissions that were granted/denied to him and his groups/roles.

Until your user is a member of win group that has update denied he will not be able to update. To make him  be able to update you should exclude him from grous with deny or revoke deny from these groups.


The most common error codes when tracing regular query execution will be 0, 1 or 2.

0 = OK, 1 = Error, 2 = Abort

Source

In your case, error code 2 = abort, meaning that a query was cancelled due to such things as a timeout or a user action.

You can test this by starting a trace, running a query and stopping it before execution end:

The official definition shows that other error messages might be shown dependent on which events you select:

The same is true for the docs on traces.

These errors and their definition can be found in:

SELECT *
FROM sys.sysmessages;

Testing this could be done by selecting error events:

Running a divide by zero query to produce an error:

Finding the exception error number:

And then querying sys.sysmessages:

SELECT *
FROM sys.sysmessages
where error = 8134;

Result:


How to clear gcache pages in Percona PXC

shutdown the node while maintaining quorum. so that there will be no downtime for application. manually move those all gcache files to some other location(don't delete them for safer side).Restart the server. You will get new gcache file.


Why should foreign keys be implemented in my relational database?

I don't have enough points to comment, so an answer.

OMG. I feel your pain ;-)

Yeah, people find foreign key constraints a real pain. But without them, you have to then audit the data for widows and orphans, otherwise you're entirely likely to have bad summary results. At the very least, you don't really know the validity of your results. Are they off by 0.001%? By 2%? It's hard to say...you have go go in and figure out the level of garbage data.

On a human level, okay, I get it, constraints can be a pain. It's an acute pain as it blocks you right at the point of insertion. (Kind of the whole point.) With sloppy rules, then you get the pain, but it's deferred. And speaking of which, since I see a Postgres tag on here, a couple of things to know about. First up deferrable constraint. This is an enormously valuable feature for this very situation. It really can be a pain to insert a child only to get it kicked back because the parent doesn't exist yet when you're going to add that parent shortly, within the same transaction. It's kind of fussy and tedious to sort that out. (Or it can feel that way.) With a deferred constraint, the FOREIGN KEY is still enforced, but not until the end of the transaction. So, you can add your records in less strict order, if that's a help. Here's a piece on the subject that looks pretty thorough, you can find many others if you look:

https://begriffs.com/posts/2017-08-27-deferrable-sql-constraints.html

And, secondly, and for what it's worth, you've also got ON UPDATE CASCADE support, although needing that is often a sign of a problem in the design.

Oh, and for bullet points, a formally defined constant goes into the meta data about your system. So, any automatic tool that knows how to interpret that data can do much, much more for you. For example:

Class and code generators.
Structure diagrams.
Test cases.
Query constructors.
Etc.

Adding an edit here with a suggestion that could help. Opinions are all well and good, but evidence sometimes is better. To that end, what about running some exploratory queries to find out the state of play in the data today? You may find that there are no problems in the data, even without server-side constraints. Some people are just freakishly good at not screwing up. Then again, there might be a total mess. Here's a query off the top of my head which, I'm sure, can be improved:

     select count(*)
       from child
tablesample bernoulli(5) -- If you're only after an estimate, TABLESAMPLE is your friend.
  left join parent on (child.parent_id = parent.id)
      where parent.id is null

P.S. I lost this very same argument once, largely because I was unaware of the deferred option at the time.


PostgreSQL synchronous replication time out?

That isn't a problem, it's by design.

If you don't want transactions to wait until a synchronous replica acknowledges a commit, then you don't want synchronous replication. You want async replication and should simply remove the synchronous_standby_names option.

What good would synchronous replication be if it gave up when no replica was reachable? "I promise your transaction is safe on another node - oh, unless the replicas are down, in which case I'll silently break my promise."

Generally what you should be doing is setting synchronous_commit to local in transactions that don't care if the commit is synchronous, which the docs say means:

  However, the special value local is available for transactions that wish to wait for local flush to disk, but not synchronous replication.

and leaving it as on for important transactions you can't afford to lose. So only those will wait until a standby is available to replicate the changes.

See also the docs on synchronous_standby_names.

Now, you might want a way to say "Wait for up to 5 minutes for a standby to acknowledge, but proceed anyway if it hasn't by then." That's not currently supported. I'm not really convinced it's useful either.


The correct answer would be to say yes - however, a quick win is to close your solution, then open the .rdl file up with notepad and do a find-and-replace. Then save your .rdl file and re-open your solution - you should find all of your references within the report have been updated. Remember that the .rdl file is essentially an xml file, open it up and have a look around - it will help you understand the inner workings better. You will of course need to update the data source yourself though.

I hope this helps you.


If I understand this correctly, what you want to do is successively subtract the value of SUM(v1 + v2 + v3) OVER (PARTITION BY year) from m for each row where st is 1, until m is exhausted; once the result drops to zero or below, it should show just 0.

Since the subtracted value is the same, you could just enumerate all st=1 rows and subtract sum * row_number for each row, like this:

WITH calculated AS (
  SELECT
    mytable.*,
    m - SUM(v1 + v2 + v3) OVER (PARTITION BY yr)
      * ROW_NUMBER()      OVER (PARTITION BY yr, st ORDER BY id ASC) AS res
  FROM
    mytable
)
SELECT
  id,
  yr,
  m,
  v1,
  v2,
  v3,
  st,
  CASE WHEN st = 1 AND res > 0 THEN res ELSE 0 END AS my
FROM
  calculated
;

The above assumes that m is the same across all rows of a partition.


Postgres 9.6.2 not stopping on Windows. Any idea?

Connect with psql and type this commmand

show log_destination;

this will display where logs are. If the output is EVENTLOG, then check Windows Event Viewer for any message from postgresql. I don't think log_destination is CSVLOG nor SYSLOG, so the last possible value is STDERR. In this case I don't think you may get the messages.
In order to change your configuration and use event log, see https://www.postgresql.org/docs/9.6/static/event-log-registration.html .

Once you know where the log is, check its content and you should be able to find the cause of your problem.


Using pt-table-checksum to compare a slave's DB to another slave

If I'm not mistaken the pt-table-checksum uses report_host value to find slaves.

May I suggest:

stop slave2. Note its current position.
Temporarily set up slave2 to replicate slave1 (binglog needs to be enabled on slave1)
Start only the io thread to get replication chain (i.e. start slave io_thread;)
Run pt-table-checksum from slave1
After check is done, stop slave and restore original replication on slave2 with saved position

This way you don't run the risk of messing up any slave.


No, currently there is no such feature in Postgres.

Some work has been done to prepare for that in future releases. One result of that is the view pg_stat_progress_vacuum introduced in 9.6 which makes use of that new infrastructure.

But currently (2018) nothing of that infrastructure is used for regular SQL queries (including the upcoming Postgres 11).

Postgres Pro Standard (a fork of PostgreSQL) provides the extension pg_query_state which can display the execution plan of a running statement and the information which step is currently executed. But you need to run Postgres Pro, the extension is not compatible with the "normal" PostgreSQL version.


Database design for tracking referral dollars and referred dollars

It's a Multi-Level Marketing system!  Jeff Moden has written a a pair of articles here and here on efficient implementation of Hierarchical Reporting against a SQL Server database. There are a number of ways to store the hierarchical information but the two main ones are Adjacency List (each child has a parent foreign key) and Nested Sets (each parent stores details of its child hierarchy). Adjacency List is more intuitive and faster to update, while Nested Sets provides faster reporting.

Jeff has explained this topic far better than I can, and developed efficient SQL Server algorithms for converting an large Adjacency List tree into a Nested Set representation,

CREATE PROCEDURE dbo.RebuildNestedSets AS
/****************************************************************************
 Purpose:
 Rebuilds a "Hierarchy" table that contains the original Adjacency List,
 the Nested Sets version of the same hierarchy, and several other useful
 columns of data some of which need not be included in the final table.

 Usage:
 EXEC dbo.RebuildNestedSets

 Progammer's Notes:
 1. As currently written, the code reads from a table called dbo.Employee.
 2. The Employee table must contain well indexed EmployeeID (child) and
    ManagerID (parent) columns.
 3. The Employee table must be a "well formed" Adjacency List. That is, the
    EmployeeID column must be unique and there must be a foreign key on the
    ManagerID column that points to the EmployeeID column. The table must not
    contain any "cycles" (an EmployeeID in its own upline). The Root Node
    must have a NULL for ManagerID.
 4. The final table, named dbo.Hierarchy, will be created in the same
    database as where this stored procedure is present.  IT DOES DROP THE
    TABLE CALLED DBO.HIERARCHY SO BE CAREFUL THAT IT DOESN'T DROP A TABLE
    NEAR AND DEAR TO YOUR HEART.
 5. This code currently has no ROLLBACK capabilities so make sure that you
    have met all of the requirements (and, perhaps, more) cited in #3 above.

 Dependencies:
 1. This stored procedure requires that the following special purpose HTally
    table be present in the same database from which it runs.

--===== Create the HTally table to be used for splitting SortPath
 SELECT TOP 1000 --(4 * 1000 = VARBINARY(4000) in length)
        N = ISNULL(CAST(
                (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)*4+1
            AS INT),0)
   INTO dbo.HTally
   FROM master.sys.all_columns ac1
  CROSS JOIN master.sys.all_columns ac2
;
--===== Add the quintessential PK for performance.
  ALTER TABLE dbo.HTally
    ADD CONSTRAINT PK_HTally
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;

 Revision History:
 Rev 00 - Circa 2009  - Jeff Moden
        - Initial concept and creation.
 Rev 01 - PASS 2010   - Jeff Moden
        - Rewritten for presentation at PASS 2010.
 Rev 02 - 06 Oct 2012 - Jeff Moden
        - Code redacted to include a more efficient, higher performmance
          method of splitting the SortPath using a custom HTally Table.
****************************************************************************/
--===========================================================================
--      Presets
--===========================================================================
--===== Suppress the auto-display of rowcounts to prevent from returning
     -- false errors if called from a GUI or other application.

SET NOCOUNT ON;

--===== Start a duration timer
DECLARE @StartTime DATETIME,
        @Duration  CHAR(12);
 SELECT @StartTime = GETDATE();

--===========================================================================
--      1.  Read ALL the nodes in a given level as indicated by the parent/
--          child relationship in the Adjacency List.
--      2.  As we read the nodes in a given level, mark each node with the
--          current level number.
--      3.  As we read the nodes in a given level, convert the EmployeeID to
--          a Binary(4) and concatenate it with the parents in the previous
--          level's binary string of EmployeeID's.  This will build the
--          SortPath.
--      4.  Number the rows according to the Sort Path.  This will number the
--          rows in the same order that the push-stack method would number
--          them.
--===========================================================================
--===== Conditionally drop the final table to make reruns easier in SSMS.
     IF OBJECT_ID('FK_Hierarchy_Hierarchy') IS NOT NULL
        ALTER TABLE dbo.Hierarchy
         DROP CONSTRAINT FK_Hierarchy_Hierarchy;

     IF OBJECT_ID('dbo.Hierarchy','U') IS NOT NULL
         DROP TABLE dbo.Hierarchy;

RAISERROR('Building the initial table and SortPath...',0,1) WITH NOWAIT;
--===== Build the new table on-the-fly including some place holders
   WITH cteBuildPath AS
( --=== This is the "anchor" part of the recursive CTE.
     -- The only thing it does is load the Root Node.
 SELECT anchor.EmployeeID,
        anchor.ManagerID,
        HLevel   = 1,
        SortPath =  CAST(
                        CAST(anchor.EmployeeID AS BINARY(4))
                    AS VARBINARY(4000)) --Up to 1000 levels deep.
   FROM dbo.Employee AS anchor
  WHERE ManagerID IS NULL --Only the Root Node has a NULL ManagerID
  UNION ALL
 --==== This is the "recursive" part of the CTE that adds 1 for each level
     -- and concatenates each level of EmployeeID's to the SortPath column.
 SELECT recur.EmployeeID,
        recur.ManagerID,
        HLevel   =  cte.HLevel + 1,
        SortPath =  CAST( --This does the concatenation to build SortPath
                        cte.SortPath + CAST(Recur.EmployeeID AS BINARY(4))
                    AS VARBINARY(4000))
   FROM dbo.Employee      AS recur WITH (TABLOCK)
  INNER JOIN cteBuildPath AS cte
          ON cte.EmployeeID = recur.ManagerID
) --=== This final INSERT/SELECT creates the Node # in the same order as a
     -- push-stack would. It also creates the final table with some
     -- "reserved" columns on the fly. We'll leave the SortPath column in
     -- place because we're still going to need it later.
     -- The ISNULLs make NOT NULL columns
 SELECT EmployeeID = ISNULL(sorted.EmployeeID,0),
        sorted.ManagerID,
        HLevel     = ISNULL(sorted.HLevel,0),
        LeftBower  = ISNULL(CAST(0 AS INT),0), --Place holder
        RightBower = ISNULL(CAST(0 AS INT),0), --Place holder
        NodeNumber = ROW_NUMBER() OVER (ORDER BY sorted.SortPath),
        NodeCount  = ISNULL(CAST(0 AS INT),0), --Place holder
        SortPath   = ISNULL(sorted.SortPath,sorted.SortPath)
   INTO dbo.Hierarchy
   FROM cteBuildPath AS sorted
 OPTION (MAXRECURSION 100) --Change this IF necessary
;
RAISERROR('There are %u rows in dbo.Hierarchy',0,1,@@ROWCOUNT) WITH NOWAIT;

--===== Display the cumulative duration
 SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Cumulative Duration = %s',0,1,@Duration) WITH NOWAIT;

--===========================================================================
--      Using the information created in the table above, create the
--      NodeCount column and the LeftBower and RightBower columns to create
--      the Nested Sets hierarchical structure.
--===========================================================================
RAISERROR('Building the Nested Sets...',0,1) WITH NOWAIT;

--===== Declare a working variable to hold the result of the calculation
     -- of the LeftBower so that it may be easily used to create the
     -- RightBower in a single scan of the final table.
DECLARE @LeftBower INT
;
--===== Create the Nested Sets from the information available in the table
     -- and in the following CTE. This uses the proprietary form of UPDATE
     -- available in SQL Serrver for extra performance.
   WITH cteCountDownlines AS
( --=== Count each occurance of EmployeeID in the sort path
 SELECT EmployeeID = CAST(SUBSTRING(h.SortPath,t.N,4) AS INT),
        NodeCount  = COUNT(*) --Includes current node
   FROM dbo.Hierarchy h,
        dbo.HTally t
  WHERE t.N BETWEEN 1 AND DATALENGTH(SortPath)
  GROUP BY SUBSTRING(h.SortPath,t.N,4)
) --=== Update the NodeCount and calculate both Bowers
 UPDATE h
    SET @LeftBower   = LeftBower = 2 * NodeNumber - HLevel,
        h.NodeCount  = downline.NodeCount,
        h.RightBower = (downline.NodeCount - 1) * 2 + @LeftBower + 1
   FROM dbo.Hierarchy h
   JOIN cteCountDownlines downline
     ON h.EmployeeID = downline.EmployeeID
;
RAISERROR('%u rows have been updated to Nested Sets',0,1,@@ROWCOUNT)
WITH NOWAIT;

RAISERROR('If the rowcounts don''t match, there may be orphans.'
,0,1,@@ROWCOUNT)WITH NOWAIT;

--===== Display the cumulative duration
 SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Cumulative Duration = %s',0,1,@Duration) WITH NOWAIT;

--===========================================================================
--      Prepare the table for high performance reads by adding indexes.
--===========================================================================
RAISERROR('Building the indexes...',0,1) WITH NOWAIT;

--===== Direct support for the Nested Sets
  ALTER TABLE dbo.Hierarchy
    ADD CONSTRAINT PK_Hierarchy
        PRIMARY KEY CLUSTERED (LeftBower, RightBower) WITH FILLFACTOR = 100
;
 CREATE UNIQUE INDEX AK_Hierarchy
     ON dbo.Hierarchy (EmployeeID) WITH FILLFACTOR = 100
;
  ALTER TABLE dbo.Hierarchy
    ADD CONSTRAINT FK_Hierarchy_Hierarchy FOREIGN KEY
        (ManagerID) REFERENCES dbo.Hierarchy (EmployeeID)
     ON UPDATE NO ACTION
     ON DELETE NO ACTION
;
--===== Display the cumulative duration
 SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Cumulative Duration = %s',0,1,@Duration) WITH NOWAIT;

--===========================================================================
--      Exit
--===========================================================================
RAISERROR('===============================================',0,1) WITH NOWAIT;
RAISERROR('RUN COMPLETE',0,1) WITH NOWAIT;
RAISERROR('===============================================',0,1) WITH NOWAIT;

and then to report hierarchical subtotals (as you require here) from the Nested Set representation:

--===== Start a "Timer" to see how long this all takes.
DECLARE @StartTime DATETIME;
 SELECT @StartTime = GETDATE();

--===========================================================================
--      1.  Read ALL the nodes in a given level as indicated by the parent/
--          child relationship in the Adjacency List.
--      2.  As we read the nodes in a given level, mark each node with the
--          current level number.
--      3.  As we read the nodes in a given level, convert the EmployeeID to
--          a Binary(4) and concatenate it with the parents in the previous
--          level’s binary string of EmployeeID’s.  This will build the
--          SortPath.
--===========================================================================
--===== Conditionally drop the work table to make reruns easier in SSMS.
     IF OBJECT_ID('dbo.Hierarchy','U') IS NOT NULL
         DROP TABLE dbo.Hierarchy;

--===== Build the new table on-the-fly including some place holders
   WITH cteBuildPath AS
( --=== This is the "anchor" part of the recursive CTE.
     -- The only thing it does is load the Root Node.
 SELECT anchor.EmployeeID,
        anchor.ManagerID,
        HLevel   = 1,
        SortPath =  CAST(
                        CAST(anchor.EmployeeID AS BINARY(4))
                    AS VARBINARY(4000)) --Up to 1000 levels deep.
   FROM dbo.Employee AS anchor
  WHERE ManagerID IS NULL --Only the Root Node has a NULL ManagerID
  UNION ALL
 --==== This is the "recursive" part of the CTE that adds 1 for each level
     -- and concatenates each level of EmployeeID's to the SortPath column.
 SELECT recur.EmployeeID,
        recur.ManagerID,
        HLevel   =  cte.HLevel + 1,
        SortPath =  CAST( --This does the concatenation to build SortPath
                        cte.SortPath + CAST(Recur.EmployeeID AS BINARY(4))
                    AS VARBINARY(4000))
   FROM dbo.Employee      AS recur WITH (TABLOCK)
  INNER JOIN cteBuildPath AS cte
          ON cte.EmployeeID = recur.ManagerID
) --=== This final INSERT/SELECT creates an iterim working table to hold the
     -- original Adjacency List, the hierarchal level of each node, and the
     -- SortPath which is the binary representation of each node's upline.
     -- The ISNULLs make NOT NULL columns
 SELECT EmployeeID = ISNULL(sorted.EmployeeID,0),
        sorted.ManagerID,
        Sales      = ISNULL(CAST(0 AS BIGINT),0), --Place Holder
        HLevel     = ISNULL(sorted.HLevel,0),
        SortPath   = ISNULL(sorted.SortPath,sorted.SortPath)
   INTO dbo.Hierarchy
   FROM cteBuildPath AS sorted
 OPTION (MAXRECURSION 100) --Change this IF necessary
;
--===== You'll be tempted to add the following index because it seems so
     -- logical a thing to do for performance, but DON'T do it! It will
     -- actually slow the rest of the code down by a factor of 2!!!!
 --ALTER TABLE dbo.Hierarchy
 --  ADD CONSTRAINT PK_Hierarchy PRIMARY KEY CLUSTERED (EmployeeID)
--;
--===== Populate the Hierarchy table with current Sales data.
 UPDATE h
    SET h.Sales = s.Sales
   FROM dbo.Hierarchy h
  INNER JOIN dbo.CurrentMonthlySales s
     ON h.EmployeeID = s.EmployeeID
;
--===== Conditionally drop the final table to make reruns easier in SSMS.
     IF OBJECT_ID('dbo.PreAggregatedHierarchy,'U') IS NOT NULL
        DROP TABLE dbo.PreAggregatedHierarchy
;
--===== Now, build "Everything" into the PreAggregatedHierarchy table.
WITH
cteSplit AS
(--==== Splits the path into elements (including Sales and HLevel)
     -- so that we can aggregate them by EmployeeID and HLevel.
     -- Can't aggregate here without including the SortPath so we don't.
 SELECT EmployeeID = CAST(SUBSTRING(h.SortPath,t.N,4) AS INT),
        h.HLevel, h.Sales
   FROM dbo.HTally         AS t
  CROSS JOIN dbo.Hierarchy AS h
  WHERE t.N BETWEEN 1 AND DATALENGTH(SortPath)
),
cteAggregate AS
(--==== Creates the aggregates and introduces the "Relative Level" column.
     -- NodeCount = Count of nodes in downline for each EmployeeID by Level
     -- Sales = Total Sales in downline for each EmployeeID by Level.
 SELECT EmployeeID,
        HLevel,
        RLevel    = ROW_NUMBER() OVER (PARTITION BY EmployeeID
                                           ORDER BY EmployeeID, HLevel),
        NodeCount = COUNT(*),
        Sales     = SUM(CAST(Sales AS MONEY))
   FROM cteSplit
  GROUP BY EmployeeID, HLevel
)
--===== Adds a "Rollup" to create all the subtotals that we need.
     -- We couldn't do this in the previous step because we didn't know what
     -- the "Relative Level" was for each row, yet.
     -- The HAVING eliminates unnecessary subtotals that are created.
 SELECT EmployeeID = ISNULL(a.EmployeeID,0), --Convert NULL total lines to 0
        HLevel     = MIN(a.HLevel), --Just so we don't have to GROUP BY
        RLevel     = ISNULL(CAST(a.RLevel AS TINYINT),0),
        NodeCount  = SUM(a.NodeCount), --Just so we don't have to GROUP BY
        Sales      = SUM(a.Sales) --Just so we don't have to GROUP BY
   INTO dbo.PreAggregatedHierarchy
   FROM cteAggregate a
  GROUP BY EmployeeID, RLevel WITH ROLLUP
 HAVING EmployeeID > 0 --Eliminates the NULL total lines for cleaner output
;
--===== Add the Clustered Index as a Primary Key
  ALTER TABLE dbo.PreAggregatedHierarchy
    ADD CONSTRAINT PK_PreAggregatedHierarchy
        PRIMARY KEY CLUSTERED (EmployeeID, RLevel) WITH FILLFACTOR = 100
;
--===== Display how long it all took
  PRINT 'Duration: ' + CONVERT(CHAR(12),GETDATE()-@StartTime,114) + ' (hh:mi:ss:mmm)';