November 2, 2011 at 11:01 am
can someone point me to a list of non-SARGable expresions?
November 2, 2011 at 11:03 am
It's an infinite list, since you can include UDFs in it.
What is it you're trying to solve?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 11:10 am
Thats actually what i was looking for. So is it just udfs?
November 2, 2011 at 11:11 am
Actually i found a pretty good refference
http://www.sql-server-pro.com/sql-where-clause-optimization.html
November 2, 2011 at 11:13 am
Definitely not just UDFs.
It's pretty much anything other than an equality comparison between a column and another column or between a column and a variable, and in both cases it requires a certain degree of comparability between data types. Same data type is best.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 11:16 am
The reference you linked to is a good intro to it.
It doesn't mention the effects of implicit conversion that can kill SARGability between data types.
And it uses IN (x,y,z) as an example of SARGability, but links to an article at the end that lists SARGability problems with OR statements, without mentioning that IN() is just a shorthand for multiple OR statements.
Other than that, it's a good intro.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 11:25 am
GSquared (11/2/2011)
It's pretty much anything other than an equality comparison between a column and another column or between a column and a variable
Inequalities are SARGable. It's not just equality comparisons.
WHERE SomeColumn > @AParameter is perfectly SARGable (that expression can be used as a seek predicate for an index seek)
Comparisons of a column and a parameter or column and constant are fine too.
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 2, 2011 at 11:45 am
So are there any functions in sql server who are SARGable?
left(), reverse()....?
November 2, 2011 at 11:48 am
GilaMonster (11/2/2011)
GSquared (11/2/2011)
It's pretty much anything other than an equality comparison between a column and another column or between a column and a variableInequalities are SARGable. It's not just equality comparisons.
WHERE SomeColumn > @AParameter is perfectly SARGable (that expression can be used as a seek predicate for an index seek)
Comparisons of a column and a parameter or column and constant are fine too.
I'm playing a little fast and loose with the definition, Gail, just to get the idea across.
Some inequality comparisons are sometimes SARGable seemed a bit out of scope of what he was looking for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 11:48 am
No. Left should be, but it isn't. Reverse couldn't possibly be SARGable because it completely modified the value
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 2, 2011 at 11:54 am
I've seen some Convert()/Cast() uses that were SARGable, like from DateTime to Date. Does that count?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply