-1

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 DATETIME2(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 ?!?!?

Community
  • 1
  • 1
dani
  • 17
  • 2

1 Answers1

0

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.

Andrew
  • 5,215
  • 1
  • 23
  • 42
  • The `DATEADD` calculation won't be evaluated every row nor will it cause a table scan. The formula doesn't contain any references to columns. To see that this type of formula just gets evaluated once you can try `create table #t (i int); INSERT INTO #t select CHECKSUM(NEWID())%2 from sys.all_columns select i, COUNT(*) from #t group by i; select COUNT(*) from #t WHERE i=CHECKSUM(rand())%2 drop table #t ` – Martin Smith Jan 13 '11 at 12:23