Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Greg Larsen
Greg Larsen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1051 Visits: 290
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
dataman777
dataman777
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 21
What is the difference and implications of utilizing #TempTableName and @TempTableName ?
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45012 Visits: 39884
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45012 Visits: 39884
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mario17
mario17
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 683
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45012 Visits: 39884
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
m mcdonald
m mcdonald
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1779 Visits: 1668
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 :-D


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
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7420 Visits: 15114
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
below86
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 2118
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
Brain2000
Brain2000
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 32
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search