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

Temp table vs Table variable Expand / Collapse
Author
Message
Posted Monday, August 8, 2011 6:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 3:46 AM
Points: 11, Visits: 51
As developer and still got lots to learn in SQL Server administration, can anyone tell me, which is better using temp table(s) or creating table variable(s) within these conditions,
i. Small or relatively medium size return rows
ii. Only used once to process within the working SP.

I alway thought that using variable is better, or is it? This always in back of my mind, would be great if any experience SQL Admins can reply for me.

Thanks in advance for your help.
Post #1155976
Posted Monday, August 8, 2011 6:26 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 21,405, Visits: 9,617
I wish I had a good link detailing all of this.

Bottom line is that they are not the same and each have better use depending on the case.


My bottom line is that unless I want to log something locally that won't be affected by a rollback, I'll always default to temp tables. The main factor is that you can have statistics on temp table and more than 1 index. The real benefit of this is that your execution plan will almost always be more optimal with temp table than table variable.
Post #1155980
Posted Monday, August 8, 2011 6:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 2,443, Visits: 7,556
pkasemsant (8/8/2011)
As developer and still got lots to learn in SQL Server administration, can anyone tell me, which is better using temp table(s) or creating table variable(s) within these conditions,
i. Small or relatively medium size return rows
ii. Only used once to process within the working SP.

I alway thought that using variable is better, or is it? This always in back of my mind, would be great if any experience SQL Admins can reply for me.

Thanks in advance for your help.


I've just recently started a new job and have been analysing our code for bad performance. One of the issues I found was where a table variable had been used, instead of a temp table. The following is taken from the report I wrote: -

"Table variables are not the same as temporary tables. The biggest differences are that: -
• Temporary tables can be altered with DDL statements whereas table variables cannot.
Essentially, this means a table variable is a heap, so every access to the table variable requires a table scan. This quickly becomes extremely costly when looking at a large dataset.
• SQL Server creates statistics on temporary tables but not on table variables.
This is both a positive and a negative for table variables. The positive is that in stored-procedures it requires fewer recompilations since there is no point in coming up with a new query plan every time due to the only way to access the data being through a table scan. However, those lack of statistics means that the query optimiser cannot come up with the most efficient execution plan.

I’d also like to clarify that table variables are not just held in memory (which is a misconception I've come across a lot), they are placed in TempDB just like temporary tables.
"



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1155989
Posted Monday, August 8, 2011 6:40 AM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 42,998, Visits: 36,155
skcadavre (8/8/2011)

"[i]Table variables are not the same as temporary tables. The biggest differences are that: -
• Temporary tables can be altered with DDL statements whereas table variables cannot.
Essentially, this means a table variable is a heap, so every access to the table variable requires a table scan. This quickly becomes extremely costly when looking at a large dataset.


The first statement is correct, but the conclusion is not. I can easily create a table variable that has a clustered index and a couple nonclustered indexes. The trick is that the table has to be created with them, not altered afterwards.

DECLARE @SomeTable TABLE (
ID INT IDENTITY PRIMARY KEY, -- clustered index
SomeStringColumn VARCHAR(20), -- I want to index this too, soo...
ARandomDate DATETIME,
UNIQUE (SomeStringColumn, ID)
);

Table variable with a clustered and a nonclustered index. SQL will seek on those indexes too quite happily.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1155995
Posted Monday, August 8, 2011 6:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 21,405, Visits: 9,617
My "worst" scenario here is that I had a long proc using a splitter function (table variable) to process a series multi-select filter.

The usual run had a total of 50M page reads (that's 400 GB of data).

Once I had put the results of the splitter into a temp table with Clustered PK (big help to the optimizer), the total reads went down to under 2M.

Now keep in mind that the WHOLE db was only 20 GB. So the first version was basically doing the same work as reading the whole db 20 times over. While the final version was doing it less than 1 time.

Needless to say that this was for a monster dashboard, but the benefits are the same on a "simper" report.
Post #1155996
Posted Monday, August 8, 2011 7:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 2,443, Visits: 7,556
GilaMonster (8/8/2011)
skcadavre (8/8/2011)

"[i]Table variables are not the same as temporary tables. The biggest differences are that: -
• Temporary tables can be altered with DDL statements whereas table variables cannot.
Essentially, this means a table variable is a heap, so every access to the table variable requires a table scan. This quickly becomes extremely costly when looking at a large dataset.


The first statement is correct, but the conclusion is not. I can easily create a table variable that has a clustered index and a couple nonclustered indexes. The trick is that the table has to be created with them, not altered afterwards.

DECLARE @SomeTable TABLE (
ID INT IDENTITY PRIMARY KEY, -- clustered index
SomeStringColumn VARCHAR(20), -- I want to index this too, soo...
ARandomDate DATETIME,
UNIQUE (SomeStringColumn, ID)
);

Table variable with a clustered and a nonclustered index. SQL will seek on those indexes too quite happily.


Thanks Gail, I was aware of the possibility of adding a clustered index but unaware of adding a non-clustered index so learnt something new.

I possibly should have pasted more of my report, which would've pointed that out, but it's littered with code that I doubt the company would be happy with going onto the internet.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1156039
Posted Monday, August 8, 2011 7:35 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 21,405, Visits: 9,617
Not to be too anal here, but you can't really add NC indexes, you can only add NC constraints. I've had cases where constraints just were not what I needed so I couldn't have used the table variable.
Post #1156042
Posted Monday, August 8, 2011 7:37 AM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 42,998, Visits: 36,155
Ninja's_RGR'us (8/8/2011)
Not to be too anal here, but you can't really add NC indexes, you can only add NC constraints.


And a unique constraint is enforced by a unique index, by default a nonclustered one. Other than splitting hairs was there a point?

If the column that you want to have the index on is not unique, well, I had that in the example above...



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1156046
Posted Monday, August 8, 2011 7:54 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 37,065, Visits: 31,628
pkasemsant (8/8/2011)
As developer and still got lots to learn in SQL Server administration, can anyone tell me, which is better using temp table(s) or creating table variable(s) within these conditions,
i. Small or relatively medium size return rows
ii. Only used once to process within the working SP.

I alway thought that using variable is better, or is it? This always in back of my mind, would be great if any experience SQL Admins can reply for me.

Thanks in advance for your help.


In true "IT" fashion, "It Depends". Please see the following article which I consider to be one of the definitive articles on the subject...
http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

My personal opinion and practice is... the only three places I use Table Variables are
1. In UDF's (and only because UDF's really don't like Temp Tables although there is a trick to using them there),
2. Inn places where I don't want something to be rolled back if a failure occurs such as process audit logs.
3. In places where I need to read from temporary structures using BCP or similar external programs.

If Temp Tables could be used for those three things, I'd never use a Table Variable for one and only one reason... they don't persist in SSMS which can be a real PITA when it comes to troubleshooting code especially if it's lengthy code.


--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 #1156058
Posted Monday, August 8, 2011 7:59 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 21,405, Visits: 9,617
GilaMonster (8/8/2011)
Ninja's_RGR'us (8/8/2011)
Not to be too anal here, but you can't really add NC indexes, you can only add NC constraints.


And a unique constraint is enforced by a unique index, by default a nonclustered one. Other than splitting hairs was there a point?

If the column that you want to have the index on is not unique, well, I had that in the example above...



No that would be me missing something I had never thaught of on my own. I had never imagined going around the limitation of having to put a constraint by adding the PK in it. Very NICE!

Thanks again!
Post #1156061
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse