Rowstore to columnstore is a change of the clustered index, and one command works in only one case. This post builds two tables of 600,000 rows, shows the error from DROP_EXISTING on a primary key, and converts each table the working way. It compares the sizes before and after.
JDBC and sendStringParametersAsUnicode: The Hidden Index Scan
Diagnose sendStringParametersAsUnicode conversions, align JDBC parameters with varchar indexes, and preserve required Unicode data.
Top 1 and Index Scan: A Scan That Stops After One Row
Top 1 and index scan can sit in the same plan, and the scan still reads only a few pages. This post runs six queries on a table of 100,000 rows and reads their logical reads. It shows why TOP 1 reads three pages in one case and every page in another.
Maximum Columns in an Index: 32 Keys and the Way Around
The maximum columns in an index is 32 key columns, and a second limit on key size can stop an index earlier. This post builds a 40 column table, shows the error for a 33rd key column, and adds included columns to get around it. It also shows the 900 and 1,700 byte key limits and tests statistics on 33 and 40 columns.
SQL SERVER – Data and Index Compression – Real-World Conversation
Index Compression needs workload measurements of storage, I/O and CPU. A customer conversation prompted my earlier advice.
Index Build Speed: Which Rebuild Settings Change the Time
Index build speed depends on rebuild or reorganize, MAXDOP, ONLINE, SORT_IN_TEMPDB and the recovery model. This post times each choice on a million row table and measures the log under each recovery model. It shows which one changes the clock and which changes only the log.
Room for an Index Build: Free Space and TempDB Placement
Room for an index build is the free space that your database and tempdb need before the build starts. This post estimates the index, checks both places, breaks a build on purpose, and shows what SORT_IN_TEMPDB moves and what it leaves in your database.