DDD 8 Entity framework follow up - Will cast(column as int) allow use of an index

I pointed out yesterday that the EntityFramework doesn't like smallints. Well it does and it doesn't The model handles them fine and the objects have datatypes of int16 which is great.

However the queries that are generated do this odd thing with any predicates on such columns. It applies a cast to column.i.e

where cast(col2 as int)= @d

At first glance I thought oh now not another screw up with the SQL from Entity framework, but on inspection of a query plan where the column in question was indexed I still got a seek operation. Very nice, well done optimisation team.

When I pointed this out, I was asked whether this worked on SQL2005. Having only got a SQL2008 instance on my laptop I took the question away for testing. The reason of the question could be related to the fact that in SQL 2008 the introduced the ability to cast a datetime to a date to be able to look for specific days of data and still use an index.

I have now tested and can state that it does work on SQL 2005.


Published Sunday, January 31, 2010 6:53 PM by simonsabin
Filed under: ,

Comments

Monday, February 1, 2010 1:15 PM by SqlServerKudos

# DDD 8 Entity framework follow up - Will cast(column as int) allow use of an index

Kudos for a great Sql Server article - Trackback from SqlServerKudos

Tuesday, February 2, 2010 8:41 AM by The Morning Brew - Chris Alcock » The Morning Brew #530

# The Morning Brew - Chris Alcock » The Morning Brew #530

Pingback from  The Morning Brew - Chris Alcock  » The Morning Brew #530