Posted by Erland Sommarskog on 10/01/82 11:36

manstein (jkelly.admin@gmail.com) writes:
> It is actually a commonly held myth that columns wrapped in
> expressions are not indexable. Sql server 2000, and later, has an
> optimization strategy called "Folding" which allows the optimizer to
> identify certain types of indexable expressions even when the column is
> nested in a function. The optimizer is able to use Folding only in
> certain circumstances but it is numeric and date columns were it is
> most likely to be used.

Do you have an example? I tried this in both SQL 2000 and SQL 2005:

select * from Orders where OrderID = 11000 - 1
select * from Orders where OrderDate = '19970808'
select * from Orders where OrderID + 1 = 11000
select * from Orders where
convert(varchar(50), OrderDate, 120) = '1997-08-08 00:00:00'

(Database: Northwind)

The first two seek an index to find the result, the other two do
not. The example with OrderID is an apparent case where the optimizer
could be able to figure out how to use the index.

The second example with OrderDate, which is taken from the post earlier
in the thread, is tricker. The optimizer would have to rewrite this into

OrderDate >= '19970808 00:00:00' AND
OrderDate < '19970808 00:00:01'

This is also an exercise with several special cases. Consider:

convert(varchar(50), OrderDate, 113) = '05 jan 2006 00:00:00'

This can be recast as above if these two conditions are fulfilled:

1) The current language has a month "Jan".
2) The database collation is case-insensitive.

(If these are not fulfilled, it's easier, as the condition then is
known to be false.)

Or this:

convert(varchar(16), OrderDate, 120) = '1997-08-08 00:00'

Now the comparison should be cast to an interval of entire minute.

Then instead of a constant, add a column from another table instead,
for an even higher degree of complexity.

Maybe unfolding of columns from expressions will come in some later
version of SQL Server, but it will take a good understanding to know
when it can happen, when it can't, and when it doesn't although it
theoretically could.

My guess is that the feature you are thinking of is constant folding,
so that constant expressions are computed at compile time.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]