Index Scan with using a variable, Seeks without

[MartinCroft]

Hi

Got a peice of code that if you put the datetime value into a variable and use in a where clause you get a table scan on the cluster (column ID) (Code B below), but if you dont put the value into the variable as in code A below you get an index seek on the index on the createdatetime column.


DECLARE @startDate DATETIME
DECLARE @minQuoteId INT



SET @startDate = DATEADD(hh, -1, GETDATE())

select @startdate


--A index seek
SELECT @minQuoteId = MIN(ID)
FROM TableA WITH (NOLOCK)
WHERE CreateDateTime >= DATEADD(hh, -1, GETDATE())


--B index clustred scan
SELECT @minQuoteId = MIN(ID)
FROM TableA WITH (NOLOCK)
WHERE CreateDateTime >= @startDate

The scan takes ages and the seek is instant, this is on a table with 26 million rows and statistics fully updated (FULLSCAN). The indexes have been rebuild on a completely new table , cluster (on ID) and 1 NC on Createdatetime column, and the same element recurring. This is on SQL2005, covering indexes have been created on CreateDateTime and ID, & tried include but SQL still chooses the cluster on code B. I realise index hints could be used but not recommended. Not sure why it seems to be doing a conversion when the variable is specified. This is just running from Mangement studio, same results as a proc and on another server on same data.

Any one got any bright ideas as to why this is occuring

 

[ZenRaven]

Try checking this out...

[URL unfurl="true"]http://sqlblogcasts.com/blogs/jasonmassie/archive/2008/01/25/the-problem-with-local-variables.aspx[/url]