Avoid using functions in a predicate. This is general advice in the same way as “avoid using cursors” is (we’ll cover this in a later blog post). It’s not always correct but generally, a function means that the query is non-Search ARGument ABLE (from here on I will refer to this as SARGable).

Let’s have a look at why. Here’s some sample data.

USE tempdb;
SET NOCOUNT ON;
 
IF object_id('dbo.testEnvironment') IS NOT NULL
BEGIN;
    DROP TABLE dbo.testEnvironment;
END;

--1,000,000 Pseudo-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())) % 366 /*(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 dbo.testEnvironment
FROM master.dbo.syscolumns sc1
CROSS JOIN master.dbo.syscolumns sc2
CROSS JOIN master.dbo.syscolumns sc3;
 
INSERT INTO dbo.testEnvironment(randomDateTime,randomDate)
VALUES('19550101','19550101');
 
CREATE UNIQUE CLUSTERED INDEX PK_testEnvironment ON dbo.testEnvironment(ID);
CREATE INDEX IX_dateTime_testEnvironment ON dbo.testEnvironment(randomDateTime);
GO

Now, we’ll turn on statistics so that we can take a look at what is going on.

SET STATISTICS IO ON;
GO

Now we’ll write two queries that return all of the data from 1985.

PRINT 'Non-SARGable';
SELECT ID, randomDateTime
FROM dbo.testEnvironment
WHERE YEAR(randomDateTime) = 1985;
GO
PRINT 'SARGable';
SELECT ID, randomDateTime
FROM dbo.testEnvironment
WHERE randomDateTime >= '19850101' AND randomDateTime < '19860101';
GO

The results tab shows the same data.

The messages tab shows similar results, but slightly different.

Non-SARGable
Table 'testEnvironment'. Scan count 1, logical reads 2240, physical reads 0, read-ahead reads 42, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SARGable
Table 'testEnvironment'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And the execution plan shows you where the difference resulted from. alt text

The function caused an index scan rather than an index seek. We can check the difference that this made to the query execution by executing this.

DECLARE @Duration CHAR(12), @StartTime DATETIME = GETDATE(), @HOLDER1 INT, @HOLDER2 DATETIME;
PRINT 'Non-SARGable';
SELECT @HOLDER1 = ID, @HOLDER2 = randomDateTime
FROM dbo.testEnvironment
WHERE YEAR(randomDateTime) = 1985;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Non-SARGable Duration: %s',0,1,@Duration) WITH NOWAIT;
GO
DECLARE @Duration CHAR(12), @StartTime DATETIME = GETDATE(), @HOLDER1 INT, @HOLDER2 DATETIME;
PRINT 'SARGable';
SELECT @HOLDER1 = ID, @HOLDER2 = randomDateTime
FROM dbo.testEnvironment
WHERE randomDateTime >= '19850101' AND randomDateTime < '19860101';
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('SARGable Duration: %s',0,1,@Duration) WITH NOWAIT;
GO

The results, on my system show the SARGable query as almost zero.

Non-SARGable
Table 'testEnvironment'. Scan count 1, logical reads 2242, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Non-SARGable Duration: 00:00:00:090
SARGable
Table 'testEnvironment'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SARGable Duration: 00:00:00:003

I appreciate that in this case, the time difference is minimal but that is down to how simple our queries are. This sort of thing has a habit of spiraling out of control if we let it.

This rule is true of pretty much all functions in SQL Server (CONVERT, CAST, UPPER, LOWER, etc).

What about arithmetical expressions?

PRINT 'Non-SARGable';
SELECT *
FROM dbo.testEnvironment
WHERE ID * 2 = 10000;
GO
PRINT 'SARGable';
SELECT *
FROM dbo.testEnvironment
WHERE ID = 10000 / 2;
GO

Both return a single row.

The message tab shows some differences.

Non-SARGable
Table 'testEnvironment'. Scan count 1, logical reads 2245, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SARGable
Table 'testEnvironment'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And the execution plan shows you how the different queries have been evaluated. alt text

The expression has caused an index scan rather than the preferred index seek in the same way that a function does.

The thing to take from this is that you need to free your column. You only have one argument but you may have many rows, so perform your function or expression against the smallest set.

Share on: