August 8, 2011 at 6:22 am
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. 😎
August 8, 2011 at 6:26 am
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.
August 8, 2011 at 6:33 am
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."
August 8, 2011 at 6:40 am
skcadavre (8/8/2011)
"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
August 8, 2011 at 6:41 am
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.
August 8, 2011 at 7:32 am
GilaMonster (8/8/2011)
skcadavre (8/8/2011)
"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.
August 8, 2011 at 7:35 am
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.
August 8, 2011 at 7:37 am
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
August 8, 2011 at 7:54 am
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
Change is inevitable... Change for the better is not.
August 8, 2011 at 7:59 am
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!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply