What makes a SQL statement sargable?

The most common thing that will make a query non-sargable is to include a field inside a function in the where clause: SELECT ... FROM ... WHERE Year(myDate) = 2008 The SQL optimizer can't use an index on myDate, even if one exists. It will literally have to evaluate this function for every row of the table. Much better to use: WHERE myDate >= '01-01-2008' AND myDate = 30 Fixed: Select ... WHERE OrderDate.

Don't do this: WHERE Field LIKE '%blah%' That causes a table/index scan. Don't do this: WHERE FUNCTION(Field) = 'BLAH' That causes a table/index scan. The database server will have to evaluate FUNCTION() against every row in the table and then compare it to 'BLAH'.

If possible, do it in reverse: WHERE Field = INVERSE_FUNCTION('BLAH') This will run INVERSE_FUNCTION() against the parameter once and will still allow use of the index.

Your suggestion with flipping the function would really only work when the function round-trips data (meaning that f(f(n)) = n). – Adam Robinson Apr 28 '09 at 20:16 1 True. I considered adding INVERSE_FUNCTION but didn't want to be confusing.

I'll change it. – beach Apr 28 '09 at 20:17.

In this answer I assume the database has sufficient covering indexes. There are enough questions about this topic. A lot of the times the sargability of a query is determined by the tipping point of the related indexes.

The tipping point defines the difference between seeking and scanning an index while joining one table or result set onto another. One seek is of course much faster than scanning a whole table, but when you have to seek a lot of rows, a scan could make more sense. So among other things a SQL statement is more sargable when the optimizer expects the number of resulting rows of one table to be less than the tipping point of a possible index on the next table.

You can find a detailed post and example here.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.