-
Apr 16th, 2012, 11:24 AM
#1
Thread Starter
PowerPoster
SQL Server Temporary Tables - General Question
If this is true:
Any temporary table created inside a stored procedure is automatically dropped when the stored procedure finishes executing.
Then why should you do this?:
It's generally considered good coding practice to explicitly drop every temporary table you create. If you are running scripts through SQL Server Management Studio or Query Analyzer the temporary tables are kept until you explicitly drop them or until you close the session.
Source: http://www.sqlteam.com/article/temporary-tables
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Apr 16th, 2012, 12:13 PM
#2
Re: SQL Server Temporary Tables - General Question
For the same reason you set object variable to nothing in VB or C#. It's considered good practice to clean up after yourself in just about every programming enviroment and in some it's necessary if you don't want 'ghosts' left floating around.
There are a few concrete benefits (things get cleaned up quicker so it helps performance) but, IMO, the real benefit is that it forces you to be disciplined as a developer. You'll start to think along the lines of: do I really want to keep this memory space tied until the end of the procedure or could I release it half way through.
Basically, it's not necessary, but it's desirable.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Apr 16th, 2012, 12:34 PM
#3
Thread Starter
PowerPoster
Re: SQL Server Temporary Tables - General Question
Then may I ask you this? My stored procedure with the temporary table was tested over the weekend with a load simulation of multiple users. I am concerned with the statement in post#1 - "If you are running scripts...the temporary tables are kept until you explicitly drop them". If my sproc demonstrated poor performance and was not explicitly dropping the table, could that be the reason? And if it were truly real multiple users, would it have performed better because the tables would've been dropped sooner?
I am having a hard time seeing where I need to tune my query. It only performed poorly with this load on it, and unless I can simulate the load myself, I won't know if any of my changes are making a difference (such as dropping the query explicitly).
Thanks.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Apr 16th, 2012, 12:51 PM
#4
Re: SQL Server Temporary Tables - General Question
could that be the reason?
Yes, particularly given the nature of your bosses load test (which I find dubious).
I've pretty much covered it in the other thread though so I won't go through it all again here. Atually, it might be worth asking a mod to merge the because the topics have converged.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Apr 16th, 2012, 01:03 PM
#5
Thread Starter
PowerPoster
Re: SQL Server Temporary Tables - General Question
Yeah, that wasn't intentional but I did end up overlapping. I intended this to be more general. Sorry about that.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Apr 16th, 2012, 01:24 PM
#6
Re: SQL Server Temporary Tables - General Question
Originally Posted by MMock
If this is true:
Any temporary table created inside a stored procedure is automatically dropped when the stored procedure finishes executing.
Then why should you do this?:
It's generally considered good coding practice to explicitly drop every temporary table you create. If you are running scripts through SQL Server Management Studio or Query Analyzer the temporary tables are kept until you explicitly drop them or until you close the session.
Source: http://www.sqlteam.com/article/temporary-tables
I don't think that applies to global temporary tables (they start with ##). I think they are available until the connection is closed.
-
Apr 16th, 2012, 01:46 PM
#7
Re: SQL Server Temporary Tables - General Question
Just FYI... there are also variable tables. Those are like regular tables but the data is stored in RAM only, making the processing faster in some cases (if you use it properly). Since it's a variable, you can't un-define it, therefore you can't drop the variable table, it will be dropped when your procedure ends.
-
Apr 16th, 2012, 01:54 PM
#8
Thread Starter
PowerPoster
Re: SQL Server Temporary Tables - General Question
Right. And I believe those are best used for a small recordset? So if I can possibly be selecting 20,000 records, it's best to out them in a temp table not a variable table. Please comment if I am wrong, but I thought that was what i read.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Apr 16th, 2012, 01:57 PM
#9
Re: SQL Server Temporary Tables - General Question
You got it right... for small recordsets, variable table is better, and for big recordsets temp table is better.
-
Apr 16th, 2012, 02:05 PM
#10
Re: SQL Server Temporary Tables - General Question
Ow, and another very usefull thing to know about variable tables, is that variable tables are not included in a transaction. You can use this for debugging your procedure, for example:
declare @tmp table (........)
begin transaction
do some processing
insert into @tmp values (some debug data)
do some more processing
etc...
rollback transaction
select * from @tmp // this will display data inserted while in the transaction
-
Apr 16th, 2012, 02:22 PM
#11
Re: SQL Server Temporary Tables - General Question
Those are like regular tables but the data is stored in RAM only
Actually that's not true. I believed it for years and ended up with egg all over my face at a conference. It was particularly annoying as I'd mercilessly drilled it into my team of developers. They never let me hear the end of it
Table variables and Temp tables can both exist either on disk in TempDB or in memory. The difference is that a Temp Table behaves as a "real" table while a table variable behaves as a variable. So you can index a temp table but you can't index a table variable. Also a table variable is only available in the current scope where as a temp table is available across the entire session. I think the only other difference is how they're handled in transactions which you mentioned. Because a table variable isn't "real" changes to it don't get written to the transaction log, which is why the rollback doesn't have any effect. There are probably other differences I've overlooked.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Apr 16th, 2012, 02:34 PM
#12
Re: SQL Server Temporary Tables - General Question
Well, what I know is from reading online articles, and apparently not all of them are accurate
-
Apr 16th, 2012, 02:36 PM
#13
Re: SQL Server Temporary Tables - General Question
Yep, that describes exactly where I was right up until the moment the egg hit
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Apr 17th, 2012, 05:01 AM
#14
Re: SQL Server Temporary Tables - General Question
The difference is that a Temp Table behaves as a "real" table while a table variable behaves as a variable. So you can index a temp table but you can't index a table variable
Not exactly true...you can create a primary key column which is implemented through an index.
Code:
Declare @VBForums table(pkKey Int Primary Key, Blah VarChar(20))
insert into @VBForums(pkKey,blah) Values(1,'blah')
insert into @VBForums(pkKey,blah) Values(2,'blah')
insert into @VBForums(pkKey,blah) Values(3,'blah')
insert into @VBForums(pkKey,blah) Values(4,'blah')
insert into @VBForums(pkKey,blah) Values(5,'blah')
select* from @VBForums
Look at the execution plan for that.
-
Apr 17th, 2012, 05:25 AM
#15
Re: SQL Server Temporary Tables - General Question
Originally Posted by FunkyDexter
Actually that's not true. I believed it for years and ended up with egg all over my face at a conference. It was particularly annoying as I'd mercilessly drilled it into my team of developers. They never let me hear the end of it
Table variables and Temp tables can both exist either on disk in TempDB or in memory. The difference is that a Temp Table behaves as a "real" table while a table variable behaves as a variable. So you can index a temp table but you can't index a table variable. Also a table variable is only available in the current scope where as a temp table is available across the entire session. I think the only other difference is how they're handled in transactions which you mentioned. Because a table variable isn't "real" changes to it don't get written to the transaction log, which is why the rollback doesn't have any effect. There are probably other differences I've overlooked.
You got one out of three
Nothing personal...when I see positive statements I like to check them out. I didn't try the examples but this link tries to dispel three common myths:
Table Variables are memory-only: False
Table Variables cannot be indexed: False -- I knew that off the top of my head and had that example.
Changes to Table Variables are not logged: False
Check it out:
http://sqlserverpedia.com/blog/sql-s...ble-variables/
Last edited by TysonLPrice; Apr 17th, 2012 at 05:46 AM.
-
Apr 17th, 2012, 07:08 AM
#16
Re: SQL Server Temporary Tables - General Question
Well there you go. Turns out I'm still wearing omelette
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|