SARGable

Question

Post reply

SARGable

agrawal.prakriti

Mr or Mrs. 500

Points: 572
More actions
February 25, 2010 at 10:37 pm

#230316

Comments posted to this topic are about the item SARGable
A missing column name a typo? Hard to believe.
No info about indices on the table/view to be queried? Who could potentially know about SARGable operators. And even if there were this information, who is able to predict the optimizers way to treat a query.

Just choose whatever you want. It may be right or wrong anyways. Leaves a QotD without value for the community.

Best regards,
Dietmar Weickert.

WayneS SSC Guru Points: 95409 More actions · Answer 1

The "correct" answer doesn't have a column in the where clause... how can that be SARGable?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

WayneS (2/25/2010)
The "correct" answer doesn't have a column in the where clause... how can that be SARGable?

I would have to agree here. The lack of a column in the where clause makes none of the answers correct.

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

dduncan Right there with Babe Points: 772 More actions · Answer 3

WayneS (2/25/2010)
The "correct" answer doesn't have a column in the where clause... how can that be SARGable?

Agreed. If the query is not RUNable then it can't be SARGable. 😀

Arjun SreeVastsva SSCertifiable Points: 7137 More actions · Answer 4

Agreed. ..The Answers was not properly printed wrong

A condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query (using index seeks, not covering indexes). The term is derived from a contraction of Search Argument.

Fal Hall of Fame Points: 3143 More actions · Answer 5

Hmmm. Either assume the pseudo code has a typo and choose the "right" answer, or take the question literally and choose the "wrong" answer. A 50/50 chance. I'm gonna have to start using dice to answer the QotD. 🙂

S.

Krishnraj SSCrazy Points: 2027 More actions · Answer 6

Yeah, I agree with CirquedeSQLeil, The lack of a column in the where clause makes none of the answers correct

SandyDB Grasshopper Points: 15 More actions · Answer 7

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.

How can where clause without column name can be SARGable while index can not be performed on this?

Please correct me in case I am wrong.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 8

I noticed it only now. The first option has a column in the Where Clause. It must be a typo for sure. Anyways i got it right:-)

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

LythamsFinest SSCommitted Points: 1786 More actions · Answer 9

SandyDB (2/26/2010)
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.
How can where clause without column name can be SARGable while index can not be performed on this?
Please correct me in case I am wrong.

The content of your reply looks remarkably like paragraph 2 of http://www.sql-server-performance.com/tips/t_sql_where_p2.aspx) .. so I'm not gonna argue with Brad MacGehee 😉

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 10

Joy Smith San

SSC-Insane

Points: 24877

February 26, 2010 at 12:44 am

#1125397

...I too got it right..

Am sure, it's a just a typo..

Tao Klerks SSCarpal Tunnel Points: 4297 More actions · Answer 11

Hmm, I was more bothered by the fact that NOT IN() is assumed to NOT be SARGable... In SQL 2000 this was always true when there was more than one value in the NOT IN() parentheses, but as of SQL 2005 the optimizer does a pretty good job of using indexes even then!

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

Martin_Burton SSCommitted Points: 1850 More actions · Answer 12

So my answer was wrong according to what the question should have said. In that case my answer was a typo, can i have the point please.

Martin

Dietmar Weickert SSCrazy Points: 2398 More actions · Answer 13

Dietmar Weickert

SSCrazy

Points: 2398

February 26, 2010 at 3:44 am

#1125468

Once again a crap QotD.

mtassin SSC-Insane Points: 23099 More actions · Answer 14

I have to agree with the complainers here.

When one of the answers is none of the above, how do I know the abscence of a column name is accidental or intentional?

I could have picked the one with LIKE 'A%', but the first thing I thought was... you can't do that... so it can't be SARGable and the None of the above is there.

If I had picked it, I'd just as likely have hit a "None of the above, because WHERE LIKE 'A%' is invalid SQL syntax and is thus non-SARGable'

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]