I have what is, to me, an interesting question on SARGability. In this case, it's about using a predicate on the difference between two date columns. Here's the setup:
USE [tempdb] SET NOCOUNT ON IF OBJECT_ID('tempdb..#sargme') IS NOT NULL BEGIN DROP TABLE #sargme END SELECT TOP 1000 IDENTITY (BIGINT, 1,1) AS ID, CAST(DATEADD(DAY, [m].[severity] * -1, GETDATE()) AS DATE) AS [DateCol1], CAST(DATEADD(DAY, [m].[severity], GETDATE()) AS DATE) AS [DateCol2] INTO #sargme FROM sys.[messages] AS [m] ALTER TABLE [#sargme] ADD CONSTRAINT [pk_whatever] PRIMARY KEY CLUSTERED ([ID]) CREATE NONCLUSTERED INDEX [ix_dates] ON [#sargme] ([DateCol1], [DateCol2])
What I'll see pretty frequently, is something like this:
/*definitely not sargable*/ SELECT * , DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) FROM [#sargme] AS [s] WHERE DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) >= 48;
...which definitely isn't SARGable. It results in an index scan, reads all 1000 rows, no good. Estimated rows stink. You'd never put this in production.
It would be nice if we could materialize CTEs, because that would help us make this, well, more SARGable-er, technically speaking. But no, we get the same execution plan as up top.
/*would be nice if it were sargable*/ WITH [x] AS ( SELECT * , DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) AS [ddif] FROM [#sargme] AS [s]) SELECT * FROM [x] WHERE [x].[ddif] >= 48;
And of course, since we are not using constants, this code changes nothing, and is not even half SARGable. No fun. Same execution plan.
/*not even half sargable*/ SELECT * , DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) FROM [#sargme] AS [s] WHERE [s].[DateCol2] >= DATEADD(DAY, 48, [s].[DateCol1])
If you're feeling lucky, and you're obeying all the ANSI SET options in your connection strings, you could add a computed column, and search on it...
ALTER TABLE [#sargme] ADD [ddiff] AS DATEDIFF(DAY, DateCol1, DateCol2) PERSISTED CREATE NONCLUSTERED INDEX [ix_dates2] ON [#sargme] ([ddiff], [DateCol1], [DateCol2]) SELECT [s].[ID] , [s].[DateCol1] , [s].[DateCol2] FROM [#sargme] AS [s] WHERE [ddiff] >= 48
This will get you an index seek with three queries. The odd man out is where we add 48 days to DateCol1. The query with
DATEDIFF in the
WHERE clause, the
CTE, and the final query with a predicate on the computed column all give you a much nicer plan with much nicer estimates, and all that.
Which brings me to the question: in a single query, is there a SARGable way to perform this search?
No temp tables, no table variables, no altering the table structure, and no views.
I'm fine with self-joins, CTEs, subqueries, or multiple passes over the data. Can work with any version of SQL Server.
Avoiding the computed column is an artificial limitation because I'm more interested in a query solution than anything else.