From the following code, it is verified that the Index is used with or without function on the column...

Create Table #t1 (mid int,gdate datetime)

CREATE CLUSTERED INDEX [IX_ALIEN_MISSILE] ON [#t1]

(

[gdate] ASC

) ON [PRIMARY]

Insert into #t1 Values ( 1,'01-01-2009 06:00:00' )

Insert into #t1 Values ( 2,'01-01-2009 07:00:00' )

Insert into #t1 Values ( 3,'01-01-2009 04:00:00' )

Insert into #t1 Values ( 4,'01-01-2009 03:00:00' )

Insert into #t1 Values ( 5,'01-02-2009 04:00:00' )

Insert into #t1 Values ( 6,'01-02-2009 05:00:00' )

Insert into #t1 Values ( 7,'01-02-2009 06:00:00' )

Insert into #t1 Values ( 8,'01-02-2009 06:00:00' )

Insert into #t1 Values ( 9,'01-03-2009 04:00:00' )

Insert into #t1 Values ( 10,'01-03-2009 04:00:00' )

Insert into #t1 Values ( 11,'01-03-2009 05:00:00' )

Insert into #t1 Values ( 12,'01-03-2009 06:00:00' )

Insert into #t1 Values ( 13,'01-03-2009 07:00:00' )

Insert into #t1 Values ( 14,'01-04-2009 08:00:00' )

Insert into #t1 Values ( 15,'01-04-2009 09:00:00' )

Insert into #t1 Values ( 16,'01-04-2009 10:00:00' )

Insert into #t1 Values ( 17,'01-04-2009 10:00:00' )

Select * from #t1 where Datediff(day,gdate,Convert(datetime,'2009-01-01 00:00:00.000')) = 0

Select * from #t1 where Convert(datetime,Convert(varchar(20),gdate,110)) = Convert(datetime,'2009-01-01 00:00:00.000')

Select * from #t1 where gdate = Convert(datetime,'2009-01-01 00:00:00.000')

drop table #t1

On checking the execution plan, it is clear that the Inde Scan is Used on the Convert of DateDiff Function and Index Seek is Used if used without function on the column. In either case, Index is Used...