SARGability Week: Using Temp Tables To Fix Non-SARGable Query Performance Problems In SQL Server


This post is especially interesting because it highlights the oddball performance situations you can run into when you write the type of All-In-One™ queries that the optimizer is typically not very good at handling, and also rely on expressions …

When Do Non-SARGable Predicates Not Cause Performance Problems In SQL Server?

Short Answer

If you have selective predicates earlier in the index that filter a lot of rows, the SARGability of trailing predicates matters less.

	CREATE INDEX shorty ON dbo.a_table(selective_column, non_selective_column);

	SELECT COUNT(*) AS records
	FROM dbo.a_table AS a
	WHERE selective_column 

Implicit Conversion Is A SARGability Problem, That’s Why It’s A Performance Problem In SQL Server


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