Different query plan to the same query ! [duplicate]

This question already has answers here: Closed 12 years ago.

Possible Duplicate:

how that happen SP sql server

hello, I get something weird. i ran this sql:

SELECT   Id , GameTypeId , PlayerId , BetAmount , Profit ,          
         DateAndTime 
FROM     Results 
WHERE    DateAndTime >= DATEADD (DAY , -1 , SYSDATETIME ())        
         AND          
         DateAndTime < SYSDATETIME () 
ORDER BY DateAndTime ASC;

i have noncluster index on the date column and the actual number of rows that return is 672 row from 1600016 rows in the table. (the estimated row was 1)

after that i ran this sql:

declare @d DATETIM开发者_开发百科E2(7)  
set @d = DATEADD (DAY , -1 , SYSDATETIME ()) 
declare  @d2 DATETIME2(7) 
set @d2  = SYSDATETIME ()  

SELECT   Id , GameTypeId , PlayerId , BetAmount , Profit ,         
         DateAndTime FROM     Results 
WHERE    DateAndTime >= @d          
         AND          
         DateAndTime < @d2 
ORDER BY DateAndTime ASC; 

and the actual execution plan was TABLE SCANE !!! and the actual number of rows that return is 672 row from 1600016 rows in the table. (the estimated row was 144000 r0ws)

some 1 know what happend here ?!?!?

---

Are you sure that you haven't muddle the execution plans. I would have assumed from a quick glance at the sql that the first query would require a table scan. I would attribute this to the fact that you have a calculation (DATEADD) in your where clause that would need to be evaluated on every row. This is something to wacth out for when trying to write performant queries.