Using a temporary table

[solo1234]

Dear all,

Is there any difference in speed using a temporary table or a normal table in sql server 2000.

for example;
select * into #temp from any_table

When any table has a lot of records, is it faster to use #temp or the any_table.

Nice regards,
Michelle.

 

[TysonLPrice]

Take a look at this:

http://msjawahar.wordpress.com/2007...s-and-their-effect-on-sql-server-performance/

 

[PDreyer]

2 reads (read any_table and insert into #temp then read #temp)
will be slower than 1 read from any_table

 

[ca8msm]

In the example you have provided, it will be much faster to use the real table. I don't see any valid reasons for you to contemplate creating a temporary table when all you are doing is making an exact copy of the table and it's data.

[!]
-------------------------------------------------------

Mark,

Join me at the new IT Community of the 21st Century: [!][URL unfurl="true"]http://lessthandot.com[/!][/url]

[/!]

 

[TysonLPrice]

I don't see any valid reasons for you to contemplate creating a temporary table when all you are doing is making an exact copy of the table and it's data."

In the shop I work in it can be useful on the production server. I can't create tables there but they are not always linked so I can get to production from test. It wouldn't be a straight copy though so I agree with what you are saying. It would be a subset used further down. I think the OP used that as an example to illustrate the question.

 

[ca8msm]

Yes, I can see how creating temporary tables are useful. It was in context of the original posters example that it makes no sense to create one.

[!]
-------------------------------------------------------

Mark,

Join me at the new IT Community of the 21st Century: [!][URL unfurl="true"]http://lessthandot.com[/!][/url]

[/!]

 

[solo1234]

Thanks for all the usefull information.

Michelle.

 

[SQLSister]

I wouild also suggest that if you are filtering the data, a derived table can be faster than either a temp table ora table vaiable. But as with all performance questions, the real anaswer depends greatly on your own database design and the actual data and the frequency the data is being accessed. I would try all three and see which works best in your own particular situation.

"NOTHING is more important in a database than integrity." ESquared