Temporary table with index question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Temporary table with index question

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2008-08-28 : 04:19:31

I'm trying to use temporary tables in Sql Server 2005. I tried this code:


CREATE TABLE #temp(
	[id] [int] NOT NULL
)
ALTER TABLE #temp ADD  CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)

insert into #temp
select distinct top 10  ID from SomeTable

select * from #temp where id<1000

drop table #temp

I checked the execution plan and it uses Table Scan on the select from #temp line, not an index scan. What am I doing wrong and what should I do to make it use an index?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-28 : 08:26:18

a couple points:
an index SCAN is not necessarily any better than an table scan. It is still a scan. The optimizer may have decided based on the STATISTICS that a scan is a better plan. ie: if your 10 values are 1-10 then <1000 will return the entire table anyway. Check the plan if you say "WHERE [id] = 1000" I suspect you will see an INDEX SEEK.

Be One with the Optimizer
TG

kensai
Posting Yak Master

172 Posts

Posted - 2008-08-28 : 08:48:10

quote:
Originally posted by TG

a couple points:
an index SCAN is not necessarily any better than an table scan. It is still a scan. The optimizer may have decided based on the STATISTICS that a scan is a better plan. ie: if your 10 values are 1-10 then <1000 will return the entire table anyway. Check the plan if you say "WHERE [id] = 1000" I suspect you will see an INDEX SEEK.

Be One with the Optimizer
TG

Yes the table is small.

I tried "WHERE id = 10000" with around 13000 rows and it's still a Table Scan.

Well this was in Display Estimated Execution Plan. I tried again with Include Actual Execution Plan and it was entirely different. The plan now displayed Clustered Index Seek. Does this means that it works ok? Is this normal to be the estimated plan and the actual plan very different?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-28 : 13:19:57

>>Does this means that it works ok?
Yes

>>Is this normal to be the estimated plan and the actual plan very different?
They can be different. Because of this difference I normally view the "actual" plan.

Be One with the Optimizer
TG

kensai
Posting Yak Master

172 Posts

Posted - 2008-08-29 : 02:56:25

Alright, thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources