October 12, 2010 at 8:57 am
Brad's article is written in 2007. So I'd think he'd have known about any SQL 2005 stuff.
He does add that covering indexes might explain a seek instead of a scan. But if these checks are sargable (which makes sense to me), then I don't know why Brad says they aren't.
October 12, 2010 at 9:00 am
WayneS (10/12/2010)
I would think that for the evaluation of IS NULL, it would depend greatly on the distribution of data... if you have a million plus rows of data with 90% of that column being null, I don't think that any index on that column would help out much. The best you could hope for there would be an index scan, but I would have to classify that as non-sargable.
Nevertheless, the IS NULL expression is still SARGable, even if the optimizer chooses to scan instead (for cost reasons).
One could still force an index seek with the aptly-named FORCESEEK table hint - it just wouldn't be optimal...
October 12, 2010 at 9:01 am
Paul White NZ (10/12/2010)
Gianluca Sartori (10/12/2010)
You will see that both IS NULL and IS NOT NULL are implemented with an index seek. I can be in doubt about IS NULL, but IS NOT NULL is for sure non-SARGable.IS NULL and IS NOT NULL are indeed both SARGable. Before seeing this thread, I'd never considered that anyone might think otherwise. Perhaps things used to be different in some older version?
Don't be mislead into thinking that the 'scan count' reported by STATISTICS IO relates to an index or table scan - it doesn't.
Well, I must admit that I'm in big trouble today, on this topic and on many other ones. It think this is not the right day for me to make big assumptions.
I've always been taught that IS NULL is not SARGable and I've never taken the time to verify it.
Glad to see that I'm plain wrong.
In the end, if Brad was tricked into this, I can definitely be. 😛
-- Gianluca Sartori
October 12, 2010 at 9:03 am
Wait a sec... Read that page carefully.
Brad states:
Non-sargable search arguments in the WHERE clause, such as "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'" generally prevents (but not always) the query optimizer from using an index to perform a search.
....
In some cases, it may be possible to rewrite a non-sargable WHERE clause into one that is sargable. For example, the clause:
WHERE SUBSTRING(firstname,1,1) = 'm'
Can be rewritten like this:
WHERE firstname like 'm%'
Both of these WHERE clauses produce the same result, but the first one is non-sargable (it uses a function) and will run slow, while the second one is sargable, and will run much faster.
Perhaps Brad was just having an off day when writing this article?
October 12, 2010 at 9:06 am
hallidayd (10/12/2010)
Before seeing this thread, I'd never considered that anyone might think otherwise.
Brad McGhee did in 2007, and he's no thicky pants.
I was referring to my personal experience, not Brad's 🙂
October 12, 2010 at 9:14 am
The OP is gonna hate us when (s)he checks back in to get a nice, straightforward answer to their nice, straightforward question 😛
October 12, 2010 at 9:21 am
OK, it might be wrong, but the myth has spread a lot:
http://www.dotnet4all.com/snippets/2008/04/performance-tip-1-avoid-non-sargable.html
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbugen9/00000463.htm
http://www.devarticles.com/c/a/SQL-Server/How-to-Perform-a-SQL-Server-Performance-Audit/8/
http://www.truthsolutions.com/midlandspass/presentations/20070301_MidlandsPASS_Bkelley.pdf
... and many others.
I find hard to believe that this myth can survive in many technologies (SQL Server, Sybase, SQLAnywhere...) and fool people like Brad McGehee and Brian Kelley.
But then again I might be wrong and I'm really willing to learn how things actually work. Or, at least, where this misconception comes from.
-- Gianluca Sartori
October 12, 2010 at 9:24 am
hallidayd (10/12/2010)
The OP is gonna hate us when (s)he checks back in to get a nice, straightforward answer to their nice, straightforward question 😛
:hehe::hehe::-D:-D:hehe::hehe:
Oh, come on! (S)He's got a wonderful chance to learn something!
Or, like me, question everything he pretends to know! 😛
-- Gianluca Sartori
October 12, 2010 at 9:24 am
Gianluca Sartori (10/12/2010)
What I find surprising, is the predicate "IS NOT NULL" using an index seek.
If you look in the Properties window when the Index Seek iterator is selected, and expand the (apparently blank) Seek Predicates attribute, you will see that the seek uses a special function 'IsNotNull' on the column. The way this works has a lot in common with <> or NOT BETWEEN seeks.
BTW, the more I read Brad's statement:
"Non-sargable search arguments in the WHERE clause, such as "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'" generally prevents (but not always) the query optimizer from using an index to perform a search."
...the more I think he was confused. Almost all of those 'exceptions' are SARGable.
October 12, 2010 at 9:26 am
It depends 🙂
Both are sargable, depending on the data being selected and the estimated rows
Try this, commenting and uncommenting the different insert statements
IF OBJECT_ID('Tempdb..#sargTest') IS NOT NULL DROP TABLE #sargTest
CREATE TABLE #sargTest (
number INT NOT NULL PRIMARY KEY CLUSTERED,
indexedColumn INT NULL ,
OtherColumn int not null
)
CREATE NONCLUSTERED INDEX IX_SARGTest ON #sargTest (indexedColumn)
go
INSERT INTO #sargTest
SELECT DISTINCT number, 0,number
FROM master.dbo.spt_values
/*
INSERT INTO #sargTest
SELECT DISTINCT number, NULL,number
FROM master.dbo.spt_values
*/
SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT number,otherColumn
FROM #sargTest
WHERE indexedColumn IS NULL
SELECT number,otherColumn
FROM #sargTest
WHERE indexedColumn IS NOT NULL
October 12, 2010 at 9:28 am
I wonder if the confusion arises due to the ISNULL function being non-SARGable?
October 12, 2010 at 9:37 am
Dave Ballantyne (10/12/2010)
It depends 🙂Both are sargable, depending on the data being selected and the estimated rows
No it doesn't depend at all.
An expression is either SARGable or it's not.
SARGable means an index seek is possible.
The optimizer will still make a cost-based decision between seek and scan alternatives.
October 12, 2010 at 9:41 am
HowardW (10/12/2010)
I wonder if the confusion arises due to the ISNULL function being non-SARGable?
Quite possibly, but now I have to show you ISNULL being 'SARGed'...
IF OBJECT_ID('Tempdb..#sargTest') IS NOT NULL DROP TABLE #sargTest
CREATE TABLE #sargTest (
number INT NOT NULL PRIMARY KEY CLUSTERED,
indexedColumn INT NULL,
bob AS ISNULL(indexedColumn, 9999)
)
CREATE NONCLUSTERED INDEX IX_SARGTest ON #sargTest (indexedColumn)
CREATE NONCLUSTERED INDEX IX_SARGTest2 ON #sargTest (bob)
INSERT INTO #sargTest
SELECT DISTINCT number, NULLIF(number % 5,0)
FROM master.dbo.spt_values
SELECT number
FROM #sargTest
WHERE ISNULL(indexedColumn, 9999) != PI();
(Dynamic) Index seek on 2005 and 2008.
Paul
October 12, 2010 at 9:53 am
Ok, I give up.
Everything I used to know (or thought that I knew) falls apart.
-- Gianluca Sartori
October 12, 2010 at 9:54 am
Gianluca Sartori (10/12/2010)
Or, like me, question everything he pretends to know! 😛
While I'm messing with your head, how about this:
"LIKE is not SARGable".
It really isn't 🙂
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply