Part 2 compares two ways to remove key lookup and remove RID lookup: a covering non-clustered index versus an index with included columns.
SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup
A quick tip to remove bookmark lookup, RID lookup or key lookup: why it happens when a query needs columns missing from the non-clustered index.
SQL SERVER – Interesting Observation – Query Hint – FORCE ORDER
A junior developer used the FORCE ORDER query hint without knowing what it does. Here is how it works and how much this one hint can change performance.
Filtered Indexes and Where They Help
Filtered indexes help SQL Server target active rows in a larger table; learn useful predicates, parameter limitations, and how to verify plan choices.
ALLOW_PAGE_LOCKS OFF Stops REORGANIZE
ALLOW_PAGE_LOCKS OFF prevents a rowstore index from being reorganized. A controlled example shows the maintenance error, checks the index setting and verifies that enabling page locks allows REORGANIZE without changing the stored rows.
SQL SERVER – Interesting Observation – Execution Plan and Results of Aggregate Concatenation Queries
Reader Bob explains why aggregate concatenation results depend on expression location, using an ANSI point of view, and shares a similar example to run.
SQL SERVER – Outer Join in Indexed View – Question to Readers
Why can’t you use an outer join in indexed view definitions? The SQL Server 2008 white paper answer, plus my question to you: can you show an example?