davidandrews13 (12/21/2012)
talking of being SARGable, i was reading this article:http://en.wikipedia.org/wiki/Sargable
and didn't quite understand this line
The typical thing that will make a sql query non-sargable is to include a function in left part of a condition of a Where clause.one of the examples given was:
Non-Sargable: Select ... WHERE DateDiff(mm,Date,GetDate()) >= 20
does that mean that if it was written like so:
Select ... WHERE 20 < DateDiff(mm,Date,GetDate())
that it would be Sargable, as the function is now on the Right hand side of a condition in a where clause?
No. A general rule (although not always true) is that if you wrap the column you are searching in a function then it is not SARGABLE, instead you need to wrap your search argument in the function.
Try this example: -
--Standard TestEnvironment of 1,000,000 rows of random-ish data
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 /*(Number of days in range)*/ + CAST('1945' AS DATETIME) /*(Start date, e.g. '1945-01-01 00:00:00*/ AS randomDateTime,
DATEADD(DAY,((ABS(CHECKSUM(NEWID())) % 30000 /*(Number of days in range)*/) + 1),CAST('1945' AS DATE) /*(Start date, e.g. '1945-01-01*/) AS randomDate,
ABS(CHECKSUM(NEWID())) AS randomBigInt,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt,
RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,
RAND(CHECKSUM(NEWID())) AS randomTinyDec,
RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,
CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoney
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
CREATE CLUSTERED INDEX cl_testEnvironment_ID ON #testEnvironment(ID ASC);
CREATE NONCLUSTERED INDEX nc_testEnvironment_randomDate ON #testEnvironment(randomDate ASC);
So, we have 1,000,000 rows of sample data.
Let's take a look at your queries: -
DECLARE @TODAY DATE = GETDATE();
-- NON-SARGABLE
SELECT COUNT(*)
FROM #testEnvironment
WHERE DateDiff(mm,randomDate,@TODAY) >= 20;
-- NON-SARGABLE
SELECT COUNT(*)
FROM #testEnvironment
WHERE 20 <= DateDiff(mm,randomDate,@TODAY);
Both perform an index scan on the "nc_testEnvironment_randomDate" index.
I would re-write that as something like this: -
-- SARGABLE
SELECT COUNT(*)
FROM #testEnvironment
WHERE randomDate < DATEADD(mm,-20,DATEADD(MONTH, DATEDIFF(MONTH, 0, @TODAY)+1, 0));
Which is now able to perform an index seek on the "nc_testEnvironment_randomDate" index.