What is fragmentation, and how do you detect fragmentation and eliminate it? I explain the internal and external types and what each one does to your pages.
SQL SERVER – Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON – A Transactional Behavior
When is IGNORE_DUP_KEY = ON useful? Jacob Sebastian and I built an example that shows its transactional behavior when you insert only nonduplicate rows.
SQL SERVER – Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON
What does IGNORE_DUP_KEY = ON do on a unique nonclustered index? Duplicate inserts give a warning instead of an error, and my example shows both options.
Prefix Searches: LIKE, LEFT and CHARINDEX
LIKE, LEFT and CHARINDEX can match the same prefix through different access paths. Compare complete matching rows and measured plans, then test literal wildcard characters and collation before choosing a search expression.
SQL SERVER – Fillfactor, Index and In-depth Look at Effect on Performance
Where in SQL Server does 100 equal 0? In Fillfactor settings. An in-depth look at how the Fillfactor of an index affects page fill and performance.
SQL SERVER – Size of Index Table for Each Index – Solution
Two reader solutions to my puzzle: queries that show the size of the index table for each index, accurate in nearly all of my tests.
SQL SERVER – Remove Bookmark Key Lookup – 4 Different Ideas
A bookmark key lookup slows down any query. Here are my four posts on removing it, gathered in one place so you can read them in order.