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.
Case-Sensitive WHERE Clause: COLLATE Without Losing the Seek
A case-sensitive WHERE clause needs a COLLATE on the comparison, but the obvious version scans the index. A demo measures 286 reads against 2, and shows two fixes: keep the plain test beside it, or give the column a case-sensitive collation.
Memory-Optimized Tables as a Key-Value Cache
Build a key-value cache with SCHEMA_ONLY memory-optimized tables, native get and set procedures, expiry checks, and bounded cleanup.
Top Three Wait Stats and What Each One Means
The top three wait stats in the files people sent in were CXPACKET, SOS_SCHEDULER_YIELD and ASYNC_IO_COMPLETION. A small demo makes each wait happen and measures it with session wait statistics. The post explains what each one means and what to check next.
Wait Stats for Performance – SQL in Sixty Seconds #157
I finally decided to create a video on that topic on my SQL in Sixty Seconds Channel. I will create more videos on the topic Wait Stats for Performance.
COUNT(*), COUNT(column) and COUNT(DISTINCT): Basics That Trip People
Why do two counts of the same table disagree? Five tiny demos show NULL, blank text, LEFT JOIN placeholders, bigint and join multiplication.
Ten Plan Operators Explained: An Operator Cheat Sheet
Seek, scan, lookup, three joins, sort, aggregate, spool and parallelism, each shown with a tiny demo. Learn to judge a plan by rows and executions.