A Jr. developer asked if a clustered index can live on a separate Drive from its table. The answer is no, and the clustered vs non clustered difference shows why.
SQL SERVER – Effect of Normalization on Index and Performance
Normalization organizes data dependencies, while indexes support access paths. I evaluate their performance effects through the actual workload.
SQL SERVER – List All Objects Created on All Filegroups in Database
A reader asked which object belongs to which filegroup. Here is how to list all objects created on all filegroups in a database, with a sample database.
Ordered Columnstore Indexes in SQL Server 2022
Build an ordered columnstore index in SQL Server 2022, inspect segment bounds, and compare range-query reads on your own data.
SQL SERVER – Maximum Columns per Primary Key – Fix : Error : Msg 1904, Level 16, The index on table has column names in index key list. The maximum limit for index or statistics key column list is 16
What are the maximum columns per primary key? It is 16, and going past it raises Msg 1904. The same limit applies to foreign keys and index keys.
user_lookups: Verify the Plan Before Adding Coverage
user_lookups helps identify where to investigate missing coverage. Compare complete query results and actual plans before adding included columns. Weigh the read benefit against storage and write costs.
SQL SERVER – 2008 – SCOPE_IDENTITY Bug with Multi Processor Parallel Plan and Solution
SCOPE_IDENTITY can return a wrong value when a query uses a parallel plan on a multi processor server. Run it on one processor or, better, use the OUTPUT clause.