Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
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

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com
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: Monday, August 18, 2014 12:59 PM
Points: 6, Visits: 15
What is the difference and implications of utilizing #TempTableName and @TempTableName ?
Post #1552622
Posted Wednesday, April 2, 2014 2:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 35,215, Visits: 31,667
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

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


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 35,215, Visits: 31,667
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:24 AM
Points: 181, Visits: 440
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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 35,215, Visits: 31,667
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:06 AM
Points: 1,343, Visits: 1,336
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse