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

Local Temporary Tables and Table Variables Expand / Collapse
Author
Message
Posted Friday, January 15, 2010 3:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 15, 2010 7:17 AM
Points: 1, Visits: 2
the temp table is created with nvarchar(50) in both screenshots, is this correct?

"
CREATE TABLE #TestUDTs (AddressTypeID int NOT NULL,
[Name] nvarchar(50) NOT NULL PRIMARY KEY CLUSTERED(AddressTypeID))
It will fail with the following error:

Msg 2715, Level 16, State 7, Line 1

Column, parameter, or variable #2: Cannot find data type dbo.Name.

The work around is to use the native data type of the user defined data type:

CREATE TABLE #TestUDTs (AddressTypeID int
NOT NULL,
[Name] nvarchar(50) NOT NULL PRIMARY KEY CLUSTERED(AddressTypeID))
"
Post #848145
Posted Friday, January 15, 2010 5:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 24, 2010 3:25 AM
Points: 5, Visits: 16
Great article!

I stumbled across another difference some years ago now, while writing a Crystal Report with the data source being a stored procedure that used a temporary table - Crystal totally fails in this scenario.

The underlying issue here is that if you "SET FMTONLY ON" and call a stored procedure that uses a temporary table, the call will fail (Invalid object name '#temp1234' under SQL2008, message may vary on earlier versions). This is what Crystal does in order to determine the expected schema of the output.

Using a table variable works around this issue nicely.

Cheers,
Nick
Post #848183
Posted Friday, January 15, 2010 7:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 19, 2010 3:23 AM
Points: 5, Visits: 31
Thank you very much, Peter! No matter how many times someone discusses table variables vs. temp tables, it seems as though there are some more wrinkles to explore.

Two things you might want to address (only the first one is really germane to the topic):

1) Talk about indexing. With a temp table, you can add any kind of index you want. With a table variable, you are limited to the index on the PK that you declare for the table.

2) In IE 6, the "Appendix" HTML table is not shown completely unless the browser window is enlarged to something in excess of about 1280 pixels.

Curt
Post #848248
Posted Friday, January 15, 2010 9:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 5:12 PM
Points: 26, Visits: 172
Nice artical. I was quite surprised that performance was not addressed.
Post #848359
Posted Friday, January 15, 2010 9:30 AM
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: Friday, September 26, 2014 6:54 AM
Points: 531, Visits: 432
Thanks everyone for your comments.

To david.hultin 15509:
Yes, it was a copy/paste problem. It was reported by, interestingly, another David (David Quéméré
) when it was first published in 2008 (check comments on Page1)

To ccoker-1050064:
Thansk for mention the I.E. 6 issue.
Indexes on temp table are well discussed. Since the article is intended to focus on something not covered well by other writers, it was just mentioned briefly in the appendix DDL part.

To Ken Shapley:
Again, the article is intended to focus on something not covered well by other writers and performance aspect is well discussed by other writers, and it is mostly related/caused by the lack of index/statistics of table variables.






Post #848380
Posted Friday, January 15, 2010 11:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 5:12 PM
Points: 26, Visits: 172
I've read a some articles on Table variable vs Temp Table performance and I have found them lacking good explaination. This one left me hanging at the end of the article http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

Table 2: Using SQL Server 2005 (time in ms).
In some cases, SQL 2005 was much faster then SQL 2000 (marked with green). But in many cases, especially with huge amounts of data, procedures that used table variables took much longer (highlighted with red). In four cases, I even gave up waiting.


Why?
Post #848482
Posted Friday, January 15, 2010 5:49 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 17,812, Visits: 15,738
Peter, Nice article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #848635
Posted Saturday, January 16, 2010 8:39 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 35,366, Visits: 31,902
Ken Shapley (1/15/2010)
I've read a some articles on Table variable vs Temp Table performance and I have found them lacking good explaination. This one left me hanging at the end of the article http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

Table 2: Using SQL Server 2005 (time in ms).
In some cases, SQL 2005 was much faster then SQL 2000 (marked with green). But in many cases, especially with huge amounts of data, procedures that used table variables took much longer (highlighted with red). In four cases, I even gave up waiting.


Why?


IMHO, two reasons... 1) Stats will not and cannot be created against a table variable by StatMan. 2) Since the content of the table variable is not realized until run time (like a Temp Table) and they don't cause a recompile (which a temp table might), table variables are always evaluated as having only one row by the optimizer which also means that the optimizer may not (and frequently does not) make the best choice when many rows are present in the table variable.

As a side bar, they also make life a bit difficult when troubleshooting code because their scope is not only session sensitive but also run sensitive just like any variable is. With temp tables, you can run the code that populates it once and do continuous trouble shooting against it. With a table variable, you have to run the code that populates the table variable every time which may (usually ) require a bit of juggling in the code with commented out code.

There are places where table variables excel compared to temp tables (like where you don't want a rollback to affect the content) but, for the most part, the only reason why I'd ever use a table variable is because you can't use/reference temp tables in a function.

I also avoid ROLLBACKs like the plague (overcoming the "best" reason to use a table variable) because ROLLBACKs are comparatively VERY expensive... that's why you'll also not see much in the line of TRY/CATCH in my code... I make sure the "answer" is known and won't even start a transaction unless I'm absolutely sure (read that as "the code has prevalidated all data") that everything will successfully fly in the transaction.


--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 #848736
Posted Saturday, January 16, 2010 8:45 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:30 AM
Points: 40,184, Visits: 36,591
3) The only indexes that can be created against table variables are primary key and unique constraints (and even then, people don't usually bother)

It's not a problem with 10 rows in the table (but then neither's the lack of stats). When you start putting several thousand rows in, joining the table variable, filtering on the table variable, etc, it becomes rather noticeable rather quickly



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 #848737
Posted Saturday, January 16, 2010 12:35 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 17,812, Visits: 15,738
GilaMonster (1/16/2010)
3) The only indexes that can be created against table variables are primary key and unique constraints (and even then, people don't usually bother)

It's not a problem with 10 rows in the temp table (but then neither's the lack of stats). ...


Gail did you mean table variable here?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #848775
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse