Common Query Plan Patterns: Sorts From Nowhere

Choices, Choices


The optimizer has a lot of choices. As of SQL Server 2019, there are 420 of them.

You can see how many are available in your version of SQL Server by doing this:

SELECT
    total_transformations 
        = COUNT_BIG(*)
FROM 

Common Query Plan Patterns For Joins: Prefetching Lookups

Counterpoint


Shockingly, there hasn’t been a lot written about Prefetching. Hard to imagine why.

Women want it, men want to be it, James Bond goes to see movies about it, and so forth.

The few reliable sources of information out …

Common Query Plan Patterns For Joins: Index Intersection

Incomplete


The optimizer has a few different strategies for using multiple indexes on the same table:

  • Lookups (Key or Bookmark)
  • Index Intersection (Join)
  • Index Union (Concatenation)

Lookups are between a nonclustered index and either the clustered index or heap, and …