Advertisement
This month I’m going to tell you about a simple change that is in regard to behavior that has been a problem in one form or another since the earliest version of SQL Server. Some improvements have been made, but not enough for me to stop being concerned about this behavior. The change has to do with search arguments (SARGs). The loose definition of a SARG is any predicate used in your WHERE clause to specify what rows you’re interested in. The formal definition of a SARG is a predicate in the following form:
WHERE <column> <operator> <value> So the following predicates are SARGs: WHERE name = ‘Joe’
WHERE 10 > price
WHERE Total BETWEEN 10 and 20
WHERE 10 > price
WHERE Total BETWEEN 10 and 20
WHERE substring(name, 1,2) = ‘Mc’
WHERE Total * 10 BETWEEN 100 and 200
WHERE Total * 10 BETWEEN 100 and 200
USE AdventureWorks;
GO
SELECT * INTO dbo.Product
FROM Production.Product
WHERE ListPrice > 0;
GO
CREATE INDEX fname_index on Product(Name);
CREATE INDEX price_index on Product(ListPrice);
GO
GO
SELECT * INTO dbo.Product
FROM Production.Product
WHERE ListPrice > 0;
GO
CREATE INDEX fname_index on Product(Name);
CREATE INDEX price_index on Product(ListPrice);
GO
USE AdventureWorks;
SET STATISTICS IO ON;
GO
SELECT * FROM Product
WHERE Name LIKE 'Half-Finger%';
GO
SELECT * FROM Product
WHERE SUBSTRING(Name, 1, 11) = 'Half-Finger';
GO
SET STATISTICS IO ON;
GO
SELECT * FROM Product
WHERE Name LIKE 'Half-Finger%';
GO
SELECT * FROM Product
WHERE SUBSTRING(Name, 1, 11) = 'Half-Finger';
GO
WHERE Name >= 'Half-Finger' AND Name < 'Half-Finges' If you used a LIKE comparison and a string with a wildcard at the beginning, there would be no way to translate the comparison into a SARG. Now what about the <value> component? In much older versions of SQL Server, the value had to actually be a constant, but SQL Server has gotten smarter over the years and now the value can be an expression, so the following predicates are SARGs: WHERE Name = SUBSTRING(‘Half-Fingered Gloves’, 1, 11)
WHERE Price > 16/4
WHERE Price > 16/4
WHERE Price > sqrt(16) So try executing the following three queries, all of which return exactly the same results: SET STATISTICS IO ON;
GO
SELECT * FROM Product
WHERE ListPrice < 4;
GO
SELECT * FROM Product
WHERE ListPrice < 16/4;
GO
SELECT * FROM Product
WHERE ListPrice < sqrt(16);
GO
GO
SELECT * FROM Product
WHERE ListPrice < 4;
GO
SELECT * FROM Product
WHERE ListPrice < 16/4;
GO
SELECT * FROM Product
WHERE ListPrice < sqrt(16);
GO
SELECT * FROM Product
WHERE ListPrice < 100;
GO
WHERE ListPrice < 100;
GO
Although there’s a SARG, there are too many rows returned to make the index worthwhile.
So how do you know whether your expression is acceptable as a SARG? There’s no list of what functions you can use in your SARGs and which you can’t. Microsoft is making improvements to the optimizer in every service pack, and a function that isn’t allowed in a SARG today might be allowed tomorrow. So my advice is always to test it and see. If you think SQL Server should be using an index seek on a column and it isn’t, check your SARGs to at least make sure they follow the format described here. Try rewriting the predicates to use different functions and constructs that give the same result. And always make sure your column is isolated; that requirement never changes!
