SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Things You Didn't Know About Temp Tables and Table Variables


Things You Didn't Know About Temp Tables and Table Variables

Author
Message
Roi Assa
Roi Assa
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 153
Comments posted to this topic are about the item Things You Didn't Know About Temp Tables and Table Variables


Kindest Regards,

Roi Assa

Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6570 Visits: 3371
Another thing never said: when creating temp table is better adding the "collate" to char/varchar/text etc. cause of different collate in tempdb e the current db.w00t

I run on tuttopodismo
Christian Buettner-167247
Christian Buettner-167247
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4577 Visits: 3889
Hello Roi,

you actually can add constraints to table variables.
Example:

DECLARE @MyTabVar TABLE(
PKCol int NOT NULL PRIMARY KEY CLUSTERED
,UniqueCol int NOT NULL UNIQUE
)



Best Regards,

Chris Büttner
SuperDBA-207096
SuperDBA-207096
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2585 Visits: 711
Nice work! Your article was very informative!
sbateman-803743
sbateman-803743
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 43
remember you can use tempdb as a nearly normal database**

I create Structure for XML explict table[s] and error_log table[s] in tempdb and JOB-Agent bcp's these to files.xml

Their STRUCTURES/Programmes are copied into model
Every time SQL-2005 is restarted empty tables are created in tempdb,from model, and are then populated as required.

this data is available if error is found and correction to 'Standard' database table[s] can be made - otherwise I am not sad to loose-data on sql_shutdown! We have to many tables in standard named as xtemp****** but nobody remembers what they were for!

**An article on differences between Tempdb/Model and 'STANDARD' sql-tables is probably some-where, it is just that I have been doing it this way for ages!
blandry
blandry
Right there with Babe
Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)

Group: General Forum Members
Points: 783 Visits: 723
Great article, and thanks for it - but under the heading of "the more I learn the less I know"...

Is there really any difference or advantage between Temp Tables and Table variables? If there is not enough cache on hand and both will eventually hit TempDB - is the only advantage that Table variables dont hit it explicitly to start?

I ask this because we have an app that generates reports by calling stored procedures. So we are only grabbing data when a report is being prepped to run. But as we design stored procs and sometimes play with Temp Tables versus Unions, or Temp Tables versus Table variables, we have yet to see any major significant difference in performance and I am left wondering if these are all just pretty much the same?

Thanks again for a great post!

There's no such thing as dumb questions, only poorly thought-out answers...
WayneS
WayneS
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15567 Visits: 10635
Is there really any difference or advantage between Temp Tables and Table variables? If there is not enough cache on hand and both will eventually hit TempDB - is the only advantage that Table variables dont hit it explicitly to start?


One thing that you CAN do with a #temp table that you can NOT do with a @Table variable is to create indexes on columns. As another poster noted, you can create the PK (and thus the PK index) on a @Table variable - but only if it is included in the declare statement. Whereas with a #temp table, you can follow up the create table (or select into) statement with create index statements. This can give the #temp table a big performance benefit, especially if you are loading it with a lot of data.

I agree that this is an excellent article... especially the part about the named constraints.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

BobAtDBS
BobAtDBS
Say Hey Kid
Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)

Group: General Forum Members
Points: 708 Visits: 365
I'll be the one to ask the idiot question:

When is it advantageous to use a table variable? Since I started in Version 7, when they didn't exist, I just have never bothered to use one yet. When I need a temp table, I've never felt constrained by it - you can call another SP and let it reference your temp table which is what I thought was the big deal with table variables. You can't pass a table variable into a SP from ADO (now THAT would be cool).


Student of SQL and Golf, Master of Neither
SuperDBA-207096
SuperDBA-207096
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2585 Visits: 711
sbateman (7/17/2008)
remember you can use tempdb as a nearly normal database**

I create Structure for XML explict table[s] and error_log table[s] in tempdb and JOB-Agent bcp's these to files.xml

Their STRUCTURES/Programmes are copied into model
Every time SQL-2005 is restarted empty tables are created in tempdb,from model, and are then populated as required.

this data is available if error is found and correction to 'Standard' database table[s] can be made - otherwise I am not sad to loose-data on sql_shutdown! We have to many tables in standard named as xtemp****** but nobody remembers what they were for!

**An article on differences between Tempdb/Model and 'STANDARD' sql-tables is probably some-where, it is just that I have been doing it this way for ages!



Hmmm that makes sense... Grab the data out of tempdb...
sbateman-803743
sbateman-803743
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 43
on area where I have been back to #table is after CTE

eg
;WITH cte_OldTraders (TRID, LastSOdate)
AS (
SELECT traderid, MAX(orderdate)
FROM salesorders
WHERE status != N'CANCELLED'
GROUP BY traderid
)
--
SELECT customers.id AS xtrid
, isNull(LastSOdate,customers.createddate) AS xsdat
INTO #soldtraders
FROM customers
LEFT OUTER JOIN cte_OldTraders ON (customers.id = TRID)
WHERE [tradingstatus] = N'CURRENT';
--
BEGIN TRAN
UPDATE traders
SET [tradingstatus] = N'DORMANT', [isonhold] = 0
FROM traders
INNER JOIN #soldtraders ON (id = xtrid)
WHERE [dbo].[xfn_julian_date](xsdat) < 3400 -- aging Function
COMMIT TRAN

I could not get @soldtraders working

so it is GOOD to have knowledge of both
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