make sargable

Question

Post reply

make sargable

smallmoney

SSCarpal Tunnel

Points: 4378
More actions
July 7, 2011 at 4:13 pm

#254201

Hello,
I remember reading functions in WHERE clauses aren't sargable or something like that.
I found a lot of WHERE ISNULL(attribute, 'cancelled') <> 'cancelled' where I work at. Been trying find a way to improve the queries.
I was wondering if the below will give same results:
DECLARE @table table (
foodVARCHAR(50),
caloriesVARCHAR(4)
)
INSERT INTO @table VALUES ('Spam', '100')
INSERT INTO @table VALUES ('Beer', '')
INSERT INTO @table VALUES ('Kolaches', NULL)
SELECT * FROM @table WHERE (calories <> '' AND calories IS NOT NULL)
SELECT * FROM @table WHERE ISNULL(calories, '') <> ''
If I replace the AND with an OR, why won't it work? It just validates the first matching condition? Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

master.dbo (7/7/2011)
I remember reading functions in WHERE clauses aren't sargable or something like that.

Take a look at the following for an explanation.

http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable

http://www.sql-server-performance.com/2007/t-sql-where/2/

http://msmvps.com/blogs/robfarley/archive/2010/01/22/sargable-functions-in-sql-server.aspx

I hope this helps! 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You can change this

SELECT * FROM @table WHERE (calories <> '' AND calories IS NOT NULL)

to this

SELECT * FROM @table WHERE calories <> ''

and it will still do what you want.

The NULLs will be eliminated by any check, they're not = anything, they're not <> anything.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Thank you Gail. 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

smallmoney SSCarpal Tunnel Points: 4378 More actions · Answer 4

smallmoney

SSCarpal Tunnel

Points: 4378

July 7, 2011 at 4:43 pm

#1350092

THANKS Everyone!