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;
SQL Server Execution Plan

SQL Server Execution Plan

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';
SQL Server Execution Plan

SQL Server Execution Plan

You can see this query using an index seek.

SELECT *
FROM #test
WHERE [name] = 'Rhys Campbell';
SQL Server Execution Plan

SQL Server Execution Plan

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';
SQL Server Execution Plan

SQL Server Execution Plan

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.


2 Comments

  1. ENOTTY says:

    I would hazard a guess that the ‘magic’ with integers is in fact a result of the optimizer simply throwing away the CONVERT(INT, 4) since the underlying column type of the same type, it does nothing.

  2. Rhys says:

    Yep, that’s right, shame it didn’t work with VARCHAR as well. Still, no need for it so it’s best removed from the query.

Leave a Reply