Oops. A bit of an oversight there. Those two queries are not logically identical. If you make them <= and >= they are the same.
However, the point I was trying to illustrate still stands. The first one cannot use an index regardless of the projection list. The reason it can't use an index is that you are performing a function (conversion of the data into a new form) on the data and the index is not organized according to the function.
Function based indexes are something Oracle has had for years, I'd like to see MS do a bit of catching up there.
Thanks. Originally we were using the Microsoft JDBC driver. After this problem was resolved, several developers wanted to try a different driver, I'm not exactly sure which one(s) they tried. The performance was a little better (about 10%), not much, but enough to decide to do more testing.
I'll check to see which driver they are using.
I've noticed this behaviour as well on one of my production databases :-(
There is a good explanation on MS site: http://support.microsoft.com/kb/271566/ and a related article about a fix in SP4. Suppose you can't change the application to insert the correct data type to avoid the table scan, then you can add an index, right? Well, in SP3 and earlier this can lead to incorrect results: http://support.microsoft.com/default.aspx/kb/899976
ORMs DO NOT force your Database to reflect your Domain Model / Object Oriented Application Architecture. It’s quite opposite. They allow you to MAP your normalized relational, Foreign Keyed data to your objects....A table is NOT an object, nor should it be. Relation Data contains a different model than a domain/business model object (Business Logic should be wrapped up in a Domain Model on the Application side or work in conjunction with a solid OO Developed Domain layer that serves application Entities). This is where ORMs come in. They grab your relational data and Map it to your business Entity. So that you can have your backend DB modeled anyway you want.
While Hibernate or NHibernate do not map to stored procedures, IBatis or IBatis.NET do, which are not “True” (Speaking Purist ORM here) ORMs, but functionally equivalent.
Now, if a person is using a code generator, then YES, they are going to try to make their tables Look their Business object so they can auto generate all Business objects and Data Access Layer classes and any Crud SQL...But while this is fast and easy, it is not the right way to go, Which is why I prefer IBatis. Don’t confuse Code Generation with ORM.