Functions as predicates and SARGable queries

Question

Post reply

Functions as predicates and SARGable queries

Viewing 13 posts - 16 through 27 (of 27 total)

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

SqlMel SSCrazy Points: 2901 More actions · Answer 1

Very good question.

Thanks!

---------------
Mel. 😎

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Nice question.

I like how it underscores the "It depends" with SArg-ability and why in the forums it really is good to have both DDL and execution plans when trying to help performance tune a query.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

Great question. Thanks!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

david.gugg SSCertifiable Points: 6032 More actions · Answer 4

Nice question, I learned something. I assumed the ISNULL function would make it non-Sargable.

[font="Tahoma"]Personal blog relating fishing to database administration:[/font]
[font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]

stephen.long.1 SSCrazy Points: 2582 More actions · Answer 5

stephen.long.1

SSCrazy

Points: 2582

August 26, 2014 at 8:12 am

#1741029

Nice question and explanation, Uwe. Thank you.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 6

Thank you for the post, very very interesting one, never new the word "SARGable" really exists, learnt a good amount of new stuff today. thank you.:-)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

SqlMel SSCrazy Points: 2901 More actions · Answer 7

SQLRNNR (8/26/2014)
Nice question.
I like how it underscores the "It depends" with SArg-ability and why in the forums it really is good to have both DDL and execution plans when trying to help performance tune a query.

+1

---------------
Mel. 😎

Guy Stephens Old Hand Points: 308 More actions · Answer 8

I expected the optimiser to use the index on code on the basis that the expression was sargible, however I did not expect a SEEK. I was expecting a SCAN because the expression was WHERE [highlight=#ffff11]!=[/highlight] rather than =. Didn't realise the optimiser would convert != into a SEEK < and SEEK >.

Interesting. Thanks for the question.

Hany Helmy SSChampion Points: 13921 More actions · Answer 9

Ok, that was new for me, never encountered a case like this before, so definitly learnt somthing new today, thx.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

mbahnu9977 SSC Rookie Points: 37 More actions · Answer 10

mbahnu9977

SSC Rookie

Points: 37

August 27, 2014 at 6:26 am

#1741297

Good Question 🙂

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 11

I am torn between two opinions on this question.

It is a good question because of the educational value on how the optimizer works and how indexes are (can be) used.

But it is also dangerous - in my experience, the optimizer can be very hard to predict, and things like this can change between versions, or even depending on the weather. This case is pretty obvious and safe, but as a matter of principle I would personally not submit such a question.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

TomThomson SSC Guru Points: 104773 More actions · Answer 12

Hugo Kornelis (8/27/2014)
I am torn between two opinions on this question.
It is a good question because of the educational value on how the optimizer works and how indexes are (can be) used.
But it is also dangerous - in my experience, the optimizer can be very hard to predict, and things like this can change between versions, or even depending on the weather. This case is pretty obvious and safe, but as a matter of principle I would personally not submit such a question.

I can see your point, but I think it's reasonable tosubmit the question. People will learn something, and if they don't realise it may change from one release to the next thy should perhaps learn that from other questions. It would of course be nice for some questions if the explanation explained that things might change, but change for this one is so unlikely that I don't think it should in this case.

Tom

DamianC SSCrazy Eights Points: 8015 More actions · Answer 13

DamianC

SSCrazy Eights

Points: 8015

January 5, 2017 at 6:06 am

#1920849

good explaination

- Damian