Achievements

« New site sections | Main | How to work with large char data types in a script component »

PrintView Printer Friendly Version

EmailEmail Article to Friend

References (5)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
    A recent discussion on Twitter about a query that Denny Cherry was looking at led to this post by Josef
  • Response
    (Reposted from my msmvps.com blog ) A recent discussion on Twitter about a query that Denny Cherry was
  • Response
    Response: Acai Berry Select
    Is a Case statement considered SARGable - Journal - SSIS - SQL Server Tidbits
  • Response
    Is a Case statement considered SARGable - Journal - SSIS - SQL Server Tidbits
  • Response
    Response: http://google.com
    Is a Case statement considered SARGable - Journal - SSIS - SQL Server Tidbits

Reader Comments (3)

I think you guys got lucky with this specific execution plan. Inequality operations are not considered SARGable operations. I would hazard a guess, based on the query snippet you referenced, that the tables/virtual tables in question were small enough that SQL Server was able to perform a seek operation regardless of the inequality operator. If you were to try this on a much larger table, I suspect that you would see an index or table scan going on.

January 30, 2010 | Unregistered CommenterJeremiah Peschka

Rob Farley had also mentioned that this might not be a valid test. I was trying to replicate it as is he. If you can help us come up with a similar 'case' (no pun intended) that shows this, I'd love to post a new article about it.

January 30, 2010 | Registered CommenterJosef Richberg

As I try to duplicate this using Northgale (download and run Northgale.sql) I'm running into an interesting situation where I can't duplicate it no matter how hard I try. Instead, I'm always getting clustered index scans. What's really interesting, though, is that the execution plans always resort to using a Nested Loops join.

Erland Sommarskog outlines some of the potential issues in Dynamic Search Conditions in T-SQL. What's interesting is that he has two versions of the article - one for everything before SQL Server 2008 SP1 CU5 and one for everything including SQL Server 2008 SP1 CU5 and later.

I'm going to try messing around with something more complex than Northwind/Northgale to see if I can duplicate these results or throw them in the garbage as totally anomalous.

January 30, 2010 | Unregistered CommenterJeremiah Peschka

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>