SQL Server Temporary Tables - General Question

[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

[FunkyDexter]

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.

[MMock]

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.

[FunkyDexter]

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.

[MMock]

Yeah, that wasn't intentional but I did end up overlapping. I intended this to be more general. Sorry about that.

[TysonLPrice]

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.

[CVMichael]

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.

[MMock]

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.

[CVMichael]

You got it right... for small recordsets, variable table is better, and for big recordsets temp table is better.

[CVMichael]

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

[FunkyDexter]

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.

[CVMichael]

Well, what I know is from reading online articles, and apparently not all of them are accurate

[FunkyDexter]

Yep, that describes exactly where I was right up until the moment the egg hit

[TysonLPrice]

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.

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.

[TysonLPrice]

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/

[FunkyDexter]

Well there you go. Turns out I'm still wearing omelette