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?
commented on Jul 5 2012 5:16AM