NIce article

I don't think it's only relative to comparisons involving a constant.

For instance, I call such a clause NON SARGABLE as well: SELECT [...] WHERE DATEPART(YYYY, Table1.MyDate) = Table2.Year -- Where MyDate is indexed

But this one is (still according to me): SELECT [...] WHERE Table1.MyDate BETWEEN DATEADD(YYYY, Table2.Year - 1900, 0) AND DATEADD(YYYY, Table2.Year - 1899, 0)

Using non-sargable is a performance killer - and can be a reason why an optimizer will use a Clustered Index Scan . Why does Query Optimizer choose a Clustered Index Scan?

But not every WHERE clause that has a non-sargable expression in it is doomed to a table/index scan. If the WHERE clause includes both sargable and non-sargable clauses, then at least the sargable clauses can use a useful index (if one exists) to help access the data quickly.

You need to qualify this statement. While it may be true for predicates combined using the AND operator, it's incorrect for those combined with OR ie:

    WHERE [SARGABLE PREDICATE] AND [NON-SARGABLE PREDICATE]   -- MAY result in a seek

while

    WHERE [SARGABLE PREDICATE] OR [NON-SARGABLE PREDICATE]   -- ALWAYS results in a scan

Take a look at the plans for Query 3 and Query 4 when you execute the following:

    SELECT name INTO #SARG FROM master.sys.columns;
    CREATE CLUSTERED INDEX ixSARGname ON #SARG(name);

    -- Query 3: results in index seek
    SELECT * FROM #SARG
    WHERE 
        name LIKE 'ACTIONID%'         -- sargable predicate
        AND name LIKE '%ACTIONID';    -- non-sargable predicate

    -- Query 4: results in index scan as OR operator used to combine predicates
    SELECT * FROM #SARG
    WHERE 
        name LIKE 'ACTIONID%'       -- sargable predicate
        OR name LIKE '%ACTIONID';   -- non-sargable predicate

    DROP TABLE #SARG;

Check this out as well "What does Avoid non SARGable where clause mean?" - http://vadivel.blogspot.in/2011/11/what-does-avoid-non-sargable-where.html

Nice blog Vadivel.

I'm not sure that Example 4:

SELECT COUNT(*) FROM tblSARGTest WHERE ProductID = ProductID;

is the best illustration of the same column appearing on both sides of the predicate. It is a tautology (will always be true) so is logically equivalent to there being no predicate at all:

SELECT COUNT(*) FROM tblSARGTest;

which would result in a table scan. While the optimizer doesn't recognise the tautology, the example doesn't demonstrate this and may leave some readers wondering. Perhaps include the Clustered Index Scan tooltip in the screenshot and highlight the 'Predicate' section of the index scan operator to illustrate that the plan really is scanning for rows where PredicateID equals PredicateID. Just a suggestion.

You may think I'm knit-picking (well, I am a bit) :-) but consider the example of the absurdity (will never be true):

SELECT COUNT(*) FROM tblSARGTest WHERE ProductID <> ProductID OPTION(RECOMPILE);

This results in a constant scan (!) and neither the table or its stats objects are referenced at all! This is because, early in the optimization process, the optimizer performs 'contradiction detection' and removes sections of the query which have been written in a way that will always return no rows (for any and all data in the database). Check the execution plan if you don't believe me.

This brings me on to a question I have on Example 5, Implicit Type Conversion...

I understand and expect the results you present in the example, but note that implicit conversion doesn't occur in the following case (assume the table is populated):

CREATE TABLE strange (n tinyint identity primary key); -- using a single byte, n has a mximum value of 255

SELECT n FROM strange WHERE n = 256;

which results in an index seek! Why is this SARGable? The predicate n = 256 compares a tinyint with a constant of higher type-precedence than tinyint yet no implicit conversion is performed. Is due to the way the tinyint and int are stored? If so, why isn't there an optimization for this? In the case of a SARGable expression, the index seek is probably efficient enough but what about non-SARGable expressions? Why perform an index scan when the expression you're looking for won't be found as it's too big to fit in the index key?

SELECT COUNT(*) FROM tblSARGTest WHERE ProductID = ProductID;

is actually equivallent to

SELECT COUNT(*) FROM tblSARGTest WHERE ProductID IS NOT NULL

In the case of a SARGable expression, the index seek is probably efficient enough but what about non-SARGable expressions? Why perform an index scan when the expression you're looking for won't be found as it's too big to fit in the index key?

If you're thinking "if the optimizer was able to determine that the expression was too big to fit in the index key, the expression would be SARGable not non-SARGable", what about scans caused by pattern matching predicates such as:

someCol LIKE '%some pattern'

for example? '%some pattern' is non-SARGable but if someCol had type varchar(3), the predicate would never be true yet an index scan would be performed all the same. Seems like an aweful waste!

SELECT COUNT(*) FROM tblSARGTest WHERE ProductID = ProductID;

is actually equivallent to

SELECT COUNT(*) FROM tblSARGTest WHERE ProductID IS NOT NULL

Interestingly, while it generates the same plan, the row size of the Index Scan operators differ. Also, why is the scan over the nonclustered index rather than the clustered one?

While the operators used to be a big deal in SQL Server 7, the relevance and optimization of the query optimizer is not longer closely established.

For example, it used to be faster to create a join table when multiple arguments were desired for an attribute rather than using OR or IN. ex. Select ... From SomeTable where SomeValue in (1,2,3). Historically, using this example the query optimizer would make three passes through the table or index. Since SQL Server 2000, this is no longer true. The query optimizer builds an effective query plan.

!=, <> and others also are less relevant.

Something that appears to be missed is the manipulation of the data being compared. That is definitely something that the query optimizer cannot tune.

For example,

-- Get a list of all records from SomeTable that were produced on the 5th day of the month.

SELECT ... FROM SomeTable WHERE DATEPART(day, RecordDateTime) = 5

This example is easy to see how the query optimizer must scan an entire index (clustered or non) extract the day, and then compare it to the literal 5.

These are the wordt kind of queries that are not SARGable.