We have dedicated Sr. People for optimization and they have been doing it for more than a decade for some good clients. So we trust their understanding for indexes...

Compressed backups are available on every edition except Express Edition ...

Awesome article, Johan!  Thanks a million!

If you move tables to another database, remember that DRI (Declared Referential Integrity, like FKs, etc) won't work across databases.

I totally agree with Page/Row Compression, compressed columns using the COMPRESS/DECOMPRESS...

You don't really need a pk or any other column from the main table.  Also, I assumed you're rather see blank than NULL for missing levels, but...

Although the PK is frequently also the clustered index, it's not necessarily so.  The keys of non-clustered indexes include the keys of the clustered index, which isn't necessarily...

Just as a side bar, a search engine search for "backup database to multiple files sql server" yields a large number of articles on the subject.

From that lot, the simplest...

Although the PK is frequently also the clustered index, it's not necessarily so.  The keys of non-clustered indexes include the keys of the clustered index, which isn't necessarily the column(s)...

You don't really need a pk or any other column from the main table.  Also, I assumed you're rather see blank than NULL for missing levels, but if not,...

Sorry... missed your previous post about the PK.

I have one in the code I posted using ROW_NUMBER() but that's not going to help you.  Help me help you by adding...

p.s. Like I said, STRING_SPLIT() isn't going to do the job correctly because it doesn't return the ordinal position of the elements nor does MS guarantee the sort order it...

Unfortunately, I can not use DelimitedSplit8K as I can not add functions on the server and I keep getting "'string_split' is not a recognized built-in function name." when I...

In the mean time, here's the code to do the given problem with the given data.  There are some caveats, though...

   WITH 
 cteEnumerate AS...

@sqldummy79 ,

No matter what we use, the test table is incomplete.  Each row needs to have something that uniquely identifies each row as it appears in the original source table. ...

The trouble with STRING_SPLIT() is that it does not return the ordinal position of the elements that...