November 13, 2015 at 6:54 am
I have a query that includes the predicate
where NOT (P.EXPIRATION IS NOT NULL)
Now, obviously this should have been written as
where (P.EXPIRATION IS NULL)
Clearly the original version is a non-SARGable predicate. However, searching the internet returns mixed results on the SARGability of IS NULL. I have found some authors who say that form is SARGable. However, using it does not result in the use of the index that exists on the EXPIRATION column.
Is there a definitive answer on whether or not the use of IS NULL results in a SARGable predicate?
Gordon Pollokoff
Wile E. is my reality, Bugs Bunny is my goal - Chuck Jones
Walking on water and developing software from a specification are easy if both are frozen. - E. Berard
Doing more things faster is no substitute for doing the right things. - S. R. Covey
Any sufficiently advanced bug is indistinguishable from a feature.- R. Kulawiec
November 13, 2015 at 9:24 am
Using IS NULL results in a SARGable predicate. Using ISNULL() results in a non-SARGable predicate. Maybe that's the confusion.
To be fair, a predicate checking for IS NULL might result in an index scan depending on the data. If there's a unique index, there will definitively be an index seek proving that the query is SARGable.
Here's a test, you can either use the graphic plans or uncomment the lines to show the text plan.
CREATE TABLE SampleData(
anInt int
);
CREATE NONCLUSTERED INDEX IX_Sample ON SampleData(anInt);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
INSERT INTO SampleData
SELECT NULLIF( NULLIF( NULLIF( n, 13), 130), 1300)
FROM cteTally
GO
--SET SHOWPLAN_ALL ON
--GO
SELECT *
FROM SampleData
WHERE anInt IS NULL
GO
--SET SHOWPLAN_ALL OFF
--GO
DROP TABLE SampleData
November 13, 2015 at 9:33 am
You are correct. Using IS NULL does result in a SARGable predicate. Must be something with the data that is making it use a clustered index scan when I modify it to use IS NULL.
Thanks.
Gordon Pollokoff
Wile E. is my reality, Bugs Bunny is my goal - Chuck Jones
Walking on water and developing software from a specification are easy if both are frozen. - E. Berard
Doing more things faster is no substitute for doing the right things. - S. R. Covey
Any sufficiently advanced bug is indistinguishable from a feature.- R. Kulawiec
November 13, 2015 at 9:41 am
An index scan is not a bad thing. You might have many nulls which would make the scan a better approach, maybe the column is not part of the index which would cause the query to scan the clustered index. It's hard to know without any details.
November 13, 2015 at 9:51 am
The question was more for general knowledge. The actual query is coming from vendor software, resulting in a large number of deadlocks. Unfortunately, I can't change the query so I was hoping a new index would solve the issue. But it won't :angry:
Gordon Pollokoff
Wile E. is my reality, Bugs Bunny is my goal - Chuck Jones
Walking on water and developing software from a specification are easy if both are frozen. - E. Berard
Doing more things faster is no substitute for doing the right things. - S. R. Covey
Any sufficiently advanced bug is indistinguishable from a feature.- R. Kulawiec
November 13, 2015 at 12:55 pm
Generally speaking, any time you have a function calculate something in the predicate of a WHERE clause, the predicate is called non-SARGable. It's because the filter can't be applied until the value is known, which means that the function has to be calculated for every row in the table. Once that's done, the comparison can be applied and the rows that don't match can be filtered out.
November 13, 2015 at 1:38 pm
pollokoff (11/13/2015)
Must be something with the data that is making it use a clustered index scan when I modify it to use IS NULL.
This, perhaps?
http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2015 at 12:26 pm
pollokoff (11/13/2015)
However, using [IS NULL] does not result in the use of the index that exists on the EXPIRATION column.
...
The question was more for general knowledge. The actual query is coming from vendor software, resulting in a large number of deadlocks. Unfortunately, I can't change the query so I was hoping a new index would solve the issue. But it won't :angry:
"IS NULL" is not the reason SQL is not using that index. It's because that index (alone) is not enough to resolve the query. If it were a covering index, SQL would use that index, and a seek on that index if applicable. Keep in mind that a seek does not necessarily mean only a few rows are being read: there is such a thing as a seek and scan. For example, for WHERE clus_key_column_datetime >= '20151115' AND clus_key_column_datetime < '20151116'. SQL would seek to the 11/15/2015, then read forward until it got to 11/16/2015.
As to the tables and the performance issue, the single most likely thing is that the tables don't have the best clustering index. Until/unless a table has that, you will have to do extra fighting deadlocks and performance issues on adjusting nonclustered indexes, perhaps constantly.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply