Functions & sargable queries

Using functions improperly in your where clauses always prevents index usage right? I’ve been reviewing some queries generated by linq and I’ve found out this isn’t always the case. A quick demo…

Create a temp tables and insert some data

CREATE TABLE #test
(
	id INTEGER NOT NULL PRIMARY KEY CLUSTERED,
	[name] VARCHAR(100) NOT NULL
);

INSERT INTO #test
(
	id,
	[name]
)
VALUES
(
	1, 'Rhys Campbell'
),
(
	2, 'Rhys J Campbell'
),
(
	3, 'R J Campbell'
),
(
	4, 'R Campbell'
),
(
	5, 'Mr J Campbell'
);

Create an index on name.

CREATE INDEX UIX_name
ON #test
(
	[name]
);

You can see this query here uses an index seek despite the use of the the CONVERT function.

SELECT *
FROM #test
WHERE CONVERT(INT, id) = 4;

[caption id=”attachment_1568” align=”alignnone” width=”430”] SQL Server Execution Plan[/caption]

But here you can see the optimiser has been force to perform an index scan to locate our row.

SELECT *
FROM #test
WHERE CAST(id AS CHAR(1)) = '4';

[caption id=”attachment_1569” align=”alignnone” width=”450”] SQL Server Execution Plan[/caption]

You can see this query using an index seek.

SELECT *
FROM #test
WHERE [name] = 'Rhys Campbell';

[caption id=”attachment_1570” align=”alignnone” width=”460”] SQL Server Execution Plan[/caption]

The next two queries produce the same execution plan. Note the index scan despite the conversion matching the underlying data type in query #1.

SELECT *
FROM #test
WHERE CAST([name] AS VARCHAR(100)) = 'Rhys Campbell';

SELECT *
FROM #test
WHERE CONVERT(CHAR(13), [name]) = 'Rhys Campbell';

[caption id=”attachment_1571” align=”alignnone” width=”604”] SQL Server Execution Plan[/caption]

So the Query Optimiser can do some magic with integers but not so much with string data type columns. It seems the function / sargable rule is not absolute and perhaps we will see improvements to this in the future. Even so, I’d like to see this unnecessary conversions removed from queries. At the very least it makes the SQL more readable but it should help give the optimiser a better chance of producing a good plan.