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 «««12345»»

Temp Tables in SQL Server Expand / Collapse
Author
Message
Posted Saturday, July 15, 2006 10:13 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 12:02 PM
Points: 32,831, Visits: 14,971
Jeff,

Excellent comments and references. Thanks for that!!







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #294642
Posted Sunday, July 16, 2006 6:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 14, 2011 11:53 AM
Points: 40, Visits: 68
I believed, as it is in Sybase, that SELECT INTO avoided logging operations : all the rows created by select into are not written to the tempdb log. If it is the same in SQL Server, the true advantage of the SELECT INTO is here, not elsewhere
Post #294680
Posted Sunday, July 16, 2006 10:12 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 36,015, Visits: 30,302

Good point... but you have to know what the Recovery Model is set to.  If it's set to FULL, even SELECT INTO is logged.  Again, don't take my word for it... here's a snippet from Books OnLine on the subject of "Recovery Models, Full Recovery"...

Full Recovery

The Full Recovery model uses database backups and transaction log backups to provide complete protection against media failure. If one or more data files is damaged, media recovery can restore all committed transactions. In-process transactions are rolled back.
Full Recovery provides the ability to recover the database to the point of failure or to a specific point in time. To guarantee this degree of recoverability, all operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged.

In SQL Server 2000, you can temporarily change the recovery model to "BULK LOGGED".  In both SQL Server 2000 and 7.x, you can temporarily change the SELECT INTO/BULKCOPY database option using sp_DBOption... in fact, in 6.5 and 7.x, you must enable this setting for SELECT INTO to even work... again, don't take my word for it... here's a snippet from Microsoft and the URL where it came from...

In releases before SQL Server 2000, creating a permanent table with SELECT INTO is allowed only if select into/bulkcopy is set. In SQL Server 2000 and later, the select into/bulkcopy database option no longer affects whether you can create a permanent table by using SELECT INTO.

http://msdn2.microsoft.com/en-us/library/ms188029.aspx



--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." -- 04 August 2013
(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 #294683
Posted Sunday, July 16, 2006 9:20 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:07 PM
Points: 2,844, Visits: 2,427
One reason that temporary tables are quicker than table variables is because SQL Server may take advantage of parallelism in the generated query for temp table. Hence, if you have a large temp table, you may get high performance for your query.


Post #294701
Posted Sunday, July 16, 2006 9:56 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 36,015, Visits: 30,302

Does anyone have a Microsoft reference or test code to support that one?  I'm kinda in the "Myth Buster" mode at work about Temp Tables and Table Variables... anything you could post would sure be appreciated.



--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." -- 04 August 2013
(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 #294706
Posted Monday, July 17, 2006 12:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, June 24, 2007 2:53 AM
Points: 28, Visits: 1
I cannot believe when I read "SELECT INTO is BAD"

SELECT INTO is one of BULK operation.
In case of large datasets there are no other way then to use one or other BULK operation.
(Don't forget DTS is also a BULK operation. I mean Fast Load =)
Post #294716
Posted Tuesday, July 18, 2006 2:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 14, 2011 11:53 AM
Points: 40, Visits: 68
I think it is a wrong way to create "temp" tables in user databases, especially is the database is in the full reco mode because the transactional coherency is lost (muse perform a full backup after)
Tempdb has been designed to store temporary table, i.e. throwable table that can be list without any risk for applications. So every temp table should be created into tempdb, and the recovery mode is simple, so no logging and strong performances, better than ddl + insert/select, in any case.
Post #295322
Posted Wednesday, July 19, 2006 10:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 14, 2011 11:53 AM
Points: 40, Visits: 68
To avoid to mislead all the readers of this article, I think it can be clearly claimed that in any case, for fast temp table creation, SELECT INTO is the best way to perform that.
Post #295581
Posted Wednesday, July 19, 2006 12:04 PM


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: Tuesday, June 21, 2011 10:03 AM
Points: 577, Visits: 102
it's also important to note that temp tables are a sign of ineffective design and workarounds (usually performance related). In an ideally designed database (sitting on an ideally designed db server) there would be absolutely no need for temporary tables.

Don't get me wrong; I use them when I have to, but I don't like them. Kind of like cursors.


Signature is NULL
Post #295611
Posted Wednesday, July 19, 2006 1:12 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, January 06, 2014 10:36 AM
Points: 104, Visits: 98
The operations you are speaking of are for permanent tables.  Select into a temp table should cause no logging to occur.  That is what we are speaking about in this thread, right?


Post #295635
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse