Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stairway to Advanced T-SQL Level 5: Storing Data in Temporary Tables Expand / Collapse
Author
Message
Posted Wednesday, February 19, 2014 11:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, January 12, 2016 1:09 PM
Points: 1,042, Visits: 288
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
Post #1543156
Posted Wednesday, March 19, 2014 7:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 23, 2015 6:25 AM
Points: 6, Visits: 21
What is the difference and implications of utilizing #TempTableName and @TempTableName ?
Post #1552622
Posted Wednesday, April 2, 2014 2:04 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:59 PM
Points: 40,530, Visits: 37,765
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1557685
Posted Wednesday, April 2, 2014 11:12 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:59 PM
Points: 40,530, Visits: 37,765
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1557814
Posted Thursday, April 3, 2014 11:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 18, 2016 6:50 PM
Points: 243, Visits: 645
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
Post #1558123
Posted Thursday, April 3, 2014 4:19 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:59 PM
Points: 40,530, Visits: 37,765
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1558298
Posted Friday, May 23, 2014 5:51 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 27, 2016 5:21 PM
Points: 1,671, Visits: 1,636
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
Post #1573978
Posted Friday, March 18, 2016 4:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:10 AM
Points: 6,491, Visits: 13,241
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
Post #1770507
Posted Friday, March 18, 2016 8:05 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 16, 2016 11:53 AM
Points: 552, Visits: 1,872
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
Post #1770580
Posted Friday, March 18, 2016 8:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 5, 2016 5:08 PM
Points: 25, Visits: 29
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.
Post #1770583
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse