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


Temp table vs Table variable


Temp table vs Table variable

Author
Message
Punprom
Punprom
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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. Cool
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68539 Visits: 9671
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.
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9200 Visits: 8492
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. Cool


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.
"


Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228443 Visits: 46342
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, MVP, M.Sc (Comp Sci)
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


Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68539 Visits: 9671
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.
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9200 Visits: 8492
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.


Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68539 Visits: 9671
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228443 Visits: 46342
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, MVP, M.Sc (Comp Sci)
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


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218353 Visits: 41995
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. Cool


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68539 Visits: 9671
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! :-D

Thanks again!
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