Stairway to Advanced T-SQL Level 5: Storing Data in Temporary Tables

Question

Post reply

Stairway to Advanced T-SQL Level 5: Storing Data in Temporary Tables

Greg Larsen

SSC-Insane

Points: 20961
More actions
February 19, 2014 at 11:42 am

#150628

Comments posted to this topic are about the item Stairway to Advanced T-SQL Level 5: Storing Data in Temporary Tables
Gregory A. Larsen, MVP

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

dataman777 SSC Enthusiast Points: 100 More actions · Answer 1

What is the difference and implications of utilizing #TempTableName and @TempTableName ?

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 2

dataman777 (3/19/2014)
What is the difference and implications of utilizing #TempTableName and @TempTableName ?

See the following:

http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

Good examples in the article, Greg. I have to take exception to what you listed as "Best Practices", though...

From the article:
Here are few best practices to consider when creating temporary tables:
Before you create a temporary table, verify you can't create a set based query that does not require the creation of a temporary table.
Explicitly drop temporary tables as soon as they are no longer needed.

The presence or absence of Temp Tables has absolutely nothing to do with whether something is set based or not. Further, it's frequently much more efficient to use a Temp Table in a "Divide'n'Conquer" fashion to hold interim results than it is to write a monster "all-in-one" query even if the creation of a midstream Temp Table causes a recompile. The use of Temp Tables can also give quite a performance boost if you have (for example) a CTE that is called more than once in the same query (the CTE will be executed once for each time it's called). Also, not everything that looks set-based, is. A recursive CTE that counts is much less effective than building a Temp Table to do the counting for you (for example, again).

As to explicitly dropping Temp Tables as soon as they are no longer needed goes, "It Depends". Dropping Temp Tables in a batch oriented stored procedure will probably be fine although it could cause an unwanted recompile. Of course, that recompile could be very short and sweet compared to continuing using the memory that a Temp Table will take (and, yes, they start in memory and spill to disk only if they get too big). For procs that have a very high hit rate, it's usually better to NOT explicitly drop Temp Tables because, as of 2005, the skeleton of the Temp Table can be cached an reused making high hit rate code a bit faster and more efficient.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mario17 SSCertifiable Points: 6079 More actions · Answer 4

Thanks, Gregory !!

I heard that these days ( after 2008R2) we don't need to CREATE #Temp , you can just do SELECT * INTO #TEMP FROM dbo.Table, it won't be any difference in performance, locking, multitasking etc...

Not sure if I want to put any indexes on #temp how this will work, can I do SELECT INTO #TEMP and then Create IDX?

Appreciate your feedback..

Tx

mario

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

mario17 (4/3/2014)
Thanks, Gregory !!
I heard that these days ( after 2008R2) we don't need to CREATE #Temp , you can just do SELECT * INTO #TEMP FROM dbo.Table, it won't be any difference in performance, locking, multitasking etc...
Not sure if I want to put any indexes on #temp how this will work, can I do SELECT INTO #TEMP and then Create IDX?
Appreciate your feedback..
Tx
mario

The ability to create and populate a Temp Table by using SELECT/INTO has existed since at least the RTM of SQL Server 6.5. And there will, many times, be a performance difference depending on what the end goal is. Lot's of times, SELECT/INTO will be a fair bit faster if no indexes are needed. "It Depends". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

m mcdonald Hall of Fame Points: 3264 More actions · Answer 6

As to explicitly dropping Temp Tables as soon as they are no longer needed goes, "It Depends". Dropping Temp Tables in a batch oriented stored procedure will probably be fine although it could cause an unwanted recompile. Of course, that recompile could be very short and sweet compared to continuing using the memory that a Temp Table will take (and, yes, they start in memory and spill to disk only if they get too big). For procs that have a very high hit rate, it's usually better to NOT explicitly drop Temp Tables because, as of 2005, the skeleton of the Temp Table can be cached an reused making high hit rate code a bit faster and more efficient.

To me the assertion regarding dropping vs. not dropping temp table seems to be contrary to what Paul White posted here:

http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx

Apparently, DROP TABLE is not DDL
Dropping a temporary table in a procedure does not count as DDL, and neither does TRUNCATE TABLE, nor UPDATE STATISTICS. None of these things prevent temporary table caching (so it does not matter whether you explicitly drop a temporary table at the end of a procedure or not).

In what cases are you seeing different behavior? Trying to absorb as much as possible 😀

Just a side note....This is also a good read

http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx

</side note>

Thanks

Cheers

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Greg

Good, comprehensive article - thanks. Just one thing I thought I ought to pick up on:

When a session creates temporary table that contains a named constraint inside a transaction, another sessions cannot create a temporary table of the same name until the first session commits the transaction

That's not quite true. I posted in the Community Additions section of the page you linked to, because even the documentation appears to be wrong.

John

Edit - corrected typo

below86 SSChampion Points: 11532 More actions · Answer 8

I'm curious as to how much you use #temp tables? Almost all of our SQL is for batch processing at night, written in the 'divide and conquer' format Jeff mentioned. Years ago when we first started writing SQL we created tables in a database called 'db_work'. You had an 'If exists..Drop table..' at the beginning of your SQL and another at the end to clean up these work tables, temp tables really since they were deleted at the end of the session. As more and more SQL was written we started running into issues where the same 'work' table name was being used in two different SQL's. So one would create the table and be running then the other started and the first thing it did was drop that table name, causing the first SQL to fail because the table didn't exists or wrong fields. So after some time of patching these types of issues we started to use the #temp tables more and more since we wouldn't run into this issue with the temp tables, we usually didn't code delete's at the end since they would automatically be dropped when the session ended. Now we have a lot of #temp tables being built and we are getting push back that the tempdb space is getting to large. We are now being told to not write any SQL with #temp tables, as this is not a 'Best Practice'. They are asking that we start using the 'db_work' database more for these type of tables. I'm worried that we will start running into the same issue we had before, where a two SQL's will use the same name and run at the same time causing one to fail. Plus I see that the issue of space will only switch from the tempdb to the db_work database.

How do you find the balance of temp vs work table usage? If you use more work type tables, do you have a naming standard to avoid the issue I mention above?

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Brain2000 Old Hand Points: 364 More actions · Answer 9

Keep in mind that a local temp table can disappear on you if you mix queries that require separate connections. A new connection will be created without you knowing it and the temp table may seem to vanish. For example, if you create a local temp table, then run a firehose cursor and use the same connection for another query before fetching all the results, you will end up in a different session that doesn't have the local temp table.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 10

akljfhnlaflkj

SSC Guru

Points: 76202

March 23, 2016 at 4:48 pm

#1867272

Good refresher, thanks.