Let’s Design An Index Together Part 1

Just One Index


I want both of these queries to be fast.

SELECT TOP (5000)
       p.LastActivityDate,
       p.PostTypeId,
       p.Score,
       p.ViewCount
FROM dbo.Posts AS p
WHERE p.PostTypeId = 4
AND   p.LastActivityDate >= '20120101'
ORDER BY p.Score DESC;


SELECT TOP (5000)
       p.LastActivityDate,
       p.PostTypeId,
       

Implicit Conversion Is A SARGability Problem

Concerns


If you compare the things that non-SARGable queries cause issues with alongside the things that bad implicit conversions cause issues with, it’s an identical list.

  • Increased CPU
  • Inefficient use of indexes
  • Poor cardinality estimation
  • Maybe a bad memory grant

What Kind Of Indexes Can You Create On Temporary Objects?

That, There


Intermediate result materialization is one of the most successful tuning methods that I regularly use.

Separating complexity is only second to eliminating complexity. Of course, sometimes those intermediate results need indexing to finish the job.

Not always. But …