SARGable

Shane O’Neill explains what SARGable means and why it’s important:

So now that 1). we have our table and b). we have an index we can use, we can run the developer’s query and be SARGable right?

1
2
3
DECLARE @Year INT = 2016;
SELECT [Test_Date] FROM [dbo].[DateTest] WHERE YEAR([Test_Date]) = @Year;
GO

Nope! Table scan, ignores our Index and reads all 15M (too lazy for all the zeros) for a measely 127,782 rows! It’s not the slowest, taking around 3.960 seconds but still, we want SARGable!!!

Watch for the surprise twist at the end.

Related Posts

Interleaved Execution And Compatibility Levels

Arun Sirpal gives us some helpful information regarding interleaved execution in SQL Server 2017: I have read-only T-SQL that references the MSTVF. I did have some code that use both data modifications and cross apply but interleaved execution does not occur in those scenarios. So on my SQL Server 2017 instance I set the database […]

Read More

Window Function Sort Performance

Lukas Eder explains one potential issue with window functions against large data sets: Usually, this blog is 100% pro window functions and advocates using them at any occasion. But like any tool, window functions come at a price and we must carefully evaluate if that’s a price we’re willing to pay. That price can be […]

Read More

Categories

April 2016
MTWTFSS
« Mar May »
 123
45678910
11121314151617
18192021222324
252627282930