SQLServerCentral / Discuss Content Posted by Peter He / Article Discussions / Article Discussions by Author / Local Temporary Tables and Table Variables / Latest Posts

RE: Local Temporary Tables and Table Variables

Graham_Day — Mon, 24 Jan 2011 08:29:00 GMT

[quote][b]GilaMonster (8/31/2008)[/b][hr]Very nice.Just one small thing. You say that for data storage, temp tables are in TempDB and table variables are in memory and tempDB. I may be misunderstanding what you mean there, but temp tables are also memory resident unless it becomes necessary to write them to disk (memory pressure, too large a table)[/quote]Also, the Appendix states that table variables are held in memory and temporary tables are held in tempdb. Whilst usually correct*, it is also the case that both are physically created in tempdb, as can be demonstrated by running the following code on a SQL Server which has no other activity occurring (runs on SQL 2000, 2005 and 2008):[code="sql"]-- make a list of all of the user tables currently active in the -- TempDB database if object_id('tempdb..#tempTables') isnot null droptable #tempTables select name into #tempTables from tempdb..sysobjectswhere type ='U' -- prove that even this new temporary table is in the list. -- Note the suffix at the end of it to uniquely identify the table across sessions. select * from #tempTables where name like '#tempTables%' GO -- create a table variable declare@MyTableVariable table (RowID int) -- show all of the new user tables in the TempDB database. select name from tempdb..sysobjects where type ='U' and name notin (select name from #tempTables) GO[/code]* According to KB305977, a table variable can hold more data than could fit into memory. [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k[/url]

RE: Local Temporary Tables and Table Variables

Jeff Moden — Fri, 05 Feb 2010 18:03:23 GMT

True enough. You should also mention that most UDFs aren't necessary either. :-)

RE: Local Temporary Tables and Table Variables

smeet.sinha — Sun, 17 Jan 2010 04:15:12 GMT

One more major diff.You can not use temp tables in UDFBut can use table variables in UDF....

RE: Local Temporary Tables and Table Variables

SQLRNNR — Sat, 16 Jan 2010 13:08:28 GMT

Thanks. That is what I thought.

RE: Local Temporary Tables and Table Variables

GilaMonster — Sat, 16 Jan 2010 12:59:05 GMT

Yes, I did.

RE: Local Temporary Tables and Table Variables

SQLRNNR — Sat, 16 Jan 2010 12:35:07 GMT

[quote][b]GilaMonster (1/16/2010)[/b][hr]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 [u]temp table [/u](but then neither's the lack of stats). ...[/quote]Gail did you mean table variable here?

RE: Local Temporary Tables and Table Variables

GilaMonster — Sat, 16 Jan 2010 08:45:49 GMT

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

RE: Local Temporary Tables and Table Variables

Jeff Moden — Sat, 16 Jan 2010 08:39:27 GMT

[quote][b]Ken Shapley (1/15/2010)[/b][hr]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.aspxTable 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.[font="Arial Black"]Why?[/font][/quote]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 [i]always [/i]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 :-D) 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.

RE: Local Temporary Tables and Table Variables

SQLRNNR — Fri, 15 Jan 2010 17:49:11 GMT

Peter, Nice article.

RE: Local Temporary Tables and Table Variables

Ken Shapley — Fri, 15 Jan 2010 11:50:28 GMT

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.aspxTable 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?

RE: Local Temporary Tables and Table Variables

peterhe — Fri, 15 Jan 2010 09:30:42 GMT

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.

RE: Local Temporary Tables and Table Variables

Ken Shapley — Fri, 15 Jan 2010 09:13:12 GMT

Nice artical. I was quite surprised that performance was not addressed.

RE: Local Temporary Tables and Table Variables

ccoker-1050064 — Fri, 15 Jan 2010 07:16:36 GMT

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

RE: Local Temporary Tables and Table Variables

Nick Chadwick — Fri, 15 Jan 2010 05:25:02 GMT

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

RE: Local Temporary Tables and Table Variables

david.hultin 15509 — Fri, 15 Jan 2010 03:10:06 GMT

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 1Column, 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 intNOT NULL,[Name] nvarchar(50) NOT NULL PRIMARY KEY CLUSTERED(AddressTypeID))"

RE: Local Temporary Tables and Table Variables

Dan-476626 — Mon, 15 Jun 2009 06:39:42 GMT

Very good article.Pingback from [url=http://madebysql.blogspot.com/]http://madebysql.blogspot.com/[/url]Thanks

RE: Local Temporary Tables and Table Variables

Jerry Hung — Sun, 14 Sep 2008 08:05:31 GMT

[quote][b]GilaMonster (9/14/2008)[/b][hr][quote][b]jerryhung (9/13/2008)[/b][hr]Good, that's smaller scope. After noticing previous posts, we do also observe high IO and CPU as wellso my next step is perhaps change all TABLE VARIABLE -> TEMP TABLE, and see if similiar pattern exists[/quote]You can try. It may help, it may not. Depends on what's causing the problem in the first place.Can you post of of the queries that seems to be giving problems (perhaps start a separate thread for that?)[quote]I checked (sp_configure, Process Explorere). SQL has max size of 3GB yes, but the VirtualSize is 10GB so it is from sqlserv.exeIt's a dedicated SQL Server, nothing else runs on it[/quote]SQL's max memory is 3GB, but it's using 10? You may want to raise that with MS using your early adopter contacts. SQL does and will use a bit more memory than what the max memory says, but not 7 GB more.What do the target and total server memory counters return (perfmon, SQL Server:memory manager)What does the following return?[code]select SUM(single_pages_kb + multi_pages_kb)/1024.0 FROM sys.dm_os_memory_clerks [/code][quote]It's SQL 2008 Standard (yes RC0 evaluation), I wish we can upgrade already :([/quote]You should look into upgrading to RTM. I don't know when RC0 is going to expire and I don't think you want to find out when your prod server refuses to start up.[/quote]I agree, I had the fear of one day, SQL Server would just stop working (in house, and production)But at the moment, I *think* we may have found the issue - [url=http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3627586&SiteID=17]Page File going nuts with a iFTS Catalog[/url]. RC0 x64 on Windows 2003 x64 as wellSo I disabled the Full-Text Automatic Change Tracking, and guess what, Pagefile has stayed at 3.5-3.7GB for 12 hoursNow we just need to prove the theory

RE: Local Temporary Tables and Table Variables

GilaMonster — Sun, 14 Sep 2008 02:20:23 GMT

[quote][b]jerryhung (9/13/2008)[/b][hr]Good, that's smaller scope. After noticing previous posts, we do also observe high IO and CPU as wellso my next step is perhaps change all TABLE VARIABLE -> TEMP TABLE, and see if similiar pattern exists[/quote]You can try. It may help, it may not. Depends on what's causing the problem in the first place.Can you post of of the queries that seems to be giving problems (perhaps start a separate thread for that?)[quote]I checked (sp_configure, Process Explorere). SQL has max size of 3GB yes, but the VirtualSize is 10GB so it is from sqlserv.exeIt's a dedicated SQL Server, nothing else runs on it[/quote]SQL's max memory is 3GB, but it's using 10? You may want to raise that with MS using your early adopter contacts. SQL does and will use a bit more memory than what the max memory says, but not 7 GB more.What do the target and total server memory counters return (perfmon, SQL Server:memory manager)What does the following return?[code]select SUM(single_pages_kb + multi_pages_kb)/1024.0 FROM sys.dm_os_memory_clerks [/code][quote]It's SQL 2008 Standard (yes RC0 evaluation), I wish we can upgrade already :([/quote]You should look into upgrading to RTM. I don't know when RC0 is going to expire and I don't think you want to find out when your prod server refuses to start up.

RE: Local Temporary Tables and Table Variables

Jeff Moden — Sat, 13 Sep 2008 10:23:39 GMT

Ummm... high IO and CPU isn't necessarily caused by either Table Variables or Temp Tables. It's HOW they are used. I think what you're going to find is that the shift to Temp Tables may help a bit, but the real key is, how much RBAR and aborant joins you're working with in the code.

RE: Local Temporary Tables and Table Variables

Jerry Hung — Sat, 13 Sep 2008 09:25:46 GMT

[quote][b]GilaMonster (9/13/2008)[/b][hr]Table variables has the same scope as normal variables. Much less than a temp table. Their lifetime is limited to one set of commands (batch) sent to the server. So, if you send a bunch of ad-hoc SQL to the server (using Execute or ExecuteNoQuery or the like) and that ad-hoc SQL declares and uses a table variable, the table variable only exists for the duration of that piece of ad-hoc SQL.Are you running SQL Enterprise or Standard? Have you checked to see if the page file usage is coming from SQL or from something else on the box?Lastly (not a perf issue) RC0 isn't licensed for production usage (unless you're a member of the early-adopter program) and has a built-in expiry date. You should replace that with SQL 2008 RTM as soon as you can.[/quote]Thank you GilaGood, that's smaller scope. After noticing previous posts, we do also observe high IO and CPU as wellso my next step is perhaps change all TABLE VARIABLE -> TEMP TABLE, and see if similiar pattern existsI checked (sp_configure, Process Explorere). SQL has max size of 3GB yes, but the VirtualSize is 10GB so it is from sqlserv.exeIt's a dedicated SQL Server, nothing else runs on itIt's SQL 2008 Standard (yes RC0 evaluation), I wish we can upgrade already :(Added:actually now I recall, we are in the early-adopter program, so we may be safe thereand Pagefile usage was 8GB last night -> 5GB near midnight -> 10GB this morning -> 8.7GB noon -> 12GB nowbtw, we use Spatial index and Full-text Daemon on the server

RE: Local Temporary Tables and Table Variables

GilaMonster — Sat, 13 Sep 2008 03:16:42 GMT

Table variables has the same scope as normal variables. Much less than a temp table. Their lifetime is limited to one set of commands (batch) sent to the server. So, if you send a bunch of ad-hoc SQL to the server (using Execute or ExecuteNoQuery or the like) and that ad-hoc SQL declares and uses a table variable, the table variable only exists for the duration of that piece of ad-hoc SQL.Are you running SQL Enterprise or Standard? Have you checked to see if the page file usage is coming from SQL or from something else on the box?Lastly (not a perf issue) RC0 isn't licensed for production usage (unless you're a member of the early-adopter program) and has a built-in expiry date. You should replace that with SQL 2008 RTM as soon as you can.

RE: Local Temporary Tables and Table Variables

Jerry Hung — Fri, 12 Sep 2008 18:59:10 GMT

I have a question, hopefully you guys will know better than meWe use a lot of TABLE VARIABLES (not temp tables) in our select statements for our heavy traffic websitesServer has 4GB RAM. Windows 2003 Standard x64, SQL 2008 RC0 64-bitSQL Server has been limited to max of 3GB memory, with 8GB page fileNow the issue is our page file usage in Task Manager kept growingIn 1~2 hours it has grown from 4.2GB to 9.5GB and not letting go so it seemsThere is no LONG running queries (no long duration, no high CPU)Probably a matter of time before it may reach the 12GB commited charge (4 physical + 8 page file)But does table variable get dropped??Noticed the article says "Current Batch" scope, while temp table is "Current Session"what is defined a batch? what if the front end .Net keeps re-using the same connection, therefore not dropping the table variable?Appreciate any help

RE: Local Temporary Tables and Table Variables

GilaMonster — Tue, 09 Sep 2008 01:44:08 GMT

Probably because of the affect they have on queries. Bad cardinality = bad query plans = higher than expected IO and CPU.

RE: Local Temporary Tables and Table Variables

paul.t.silvey — Mon, 08 Sep 2008 15:30:37 GMT

hmmm... I personally hate table variables because they cause the disk queue length (I/O) and CPU to shoot through the roof. They only work on simple small systems with low transactional volume. If you take a real life OLTP system or a large warehouse, they are pretty much useless in my book. They seem to bring the system to its knees. They kind of remind me of Cursors... wait... aren't cursors memory intensive also? Do you see the similarities?I will take an indexed temp table over a table variable any day on a heavy usage system.

RE: Local Temporary Tables and Table Variables

peterhe — Tue, 02 Sep 2008 07:24:00 GMT

To humbleDBA,You are right. INSERT ... EXEC works for table variable too in SQL Server 2005. In SQL 2000, it does not work. Thanks.Jeff, Gail, and Chris, Thanks for the workarounds for UDTs in master and msdb. I have not had a chance to test and confirm it.David Quéméré,Your are right. The script has a mistake. It's actually the workaround script. I guess I messed it up by copy/pasteThank you guys for taking time to read/comment on it

RE: Local Temporary Tables and Table Variables

Christian Buettner-167247 — Tue, 02 Sep 2008 01:31:37 GMT

[quote][b]GilaMonster (9/1/2008)[/b][hr][quote][b]rbarryyoung (9/1/2008)[/b][hr]Of course we use a startup procedure to define the UDT's in TempDB.[/quote]Does defining them in model work as well?[/quote]According to this article, yes[url]http://www.sqlservercentral.com/articles/Advanced/3104/[/url]

RE: Local Temporary Tables and Table Variables

GilaMonster — Mon, 01 Sep 2008 23:41:05 GMT

[quote][b]rbarryyoung (9/1/2008)[/b][hr]Of course we use a startup procedure to define the UDT's in TempDB.[/quote]Does defining them in model work as well?

RE: Local Temporary Tables and Table Variables

Anipaul — Mon, 01 Sep 2008 23:16:31 GMT

Wonderful article..

RE: Local Temporary Tables and Table Variables

RBarryYoung — Mon, 01 Sep 2008 16:51:27 GMT

Of course we use a startup procedure to define the UDT's in TempDB.

RE: Local Temporary Tables and Table Variables

Jeff Moden — Mon, 01 Sep 2008 14:56:39 GMT

Good article! You did forget to mention one workaround for the UDT's that would allow it to work across all databases including TempDB... Define the UDT in the Master DB. Of course, most folks will argue that the Master DB is a sacred entity that should not be touched and, for the most part, I agree. :D

RE: Local Temporary Tables and Table Variables

David Quéméré — Mon, 01 Sep 2008 12:11:06 GMT

The 2nd chapter about UDTs contains a mistake : the script supposed to raise an error and the workaround don't have any difference. I guess the error script should use a UDT (dbo.Name) instead of a native type (nvarchar(50)).Thanks to all the authors and SqlserverCentral for their efforts and the useful documentation they bring us.

RE: Local Temporary Tables and Table Variables

timothyawiseman — Mon, 01 Sep 2008 11:32:57 GMT

Awesome article that covers some of the lesser considered aspects of temporary tables and table variables. Thank you.

RE: Local Temporary Tables and Table Variables

humbleDBA — Mon, 01 Sep 2008 02:43:00 GMT

Agree with Gail, nice article, plus I'm reading it that you're suggesting that Data Storage for Temp Table is not in memory - for which it is in-memory unless pushed out to disk.Also, I'm taking it that, as this article is not an old one, that it covers SQL2005. If this is the case, then INSERT...EXEC is supported for Table Variables. You can test this with the following:-- SQL2005DECLARE @helpdb TABLE (name sysname, db_size nvarchar(13), owner sysname, dbid smallint, created nvarchar(11), status nvarchar(600), compatibility_level tinyint)INSERT @helpdb (name, db_size, owner, dbid, created, status, compatibility_level)EXEC dbo.sp_helpdbSELECT * FROM @helpdbHTH

RE: Local Temporary Tables and Table Variables

GilaMonster — Sun, 31 Aug 2008 23:50:52 GMT

Very nice.Just one small thing. You say that for data storage, temp tables are in TempDB and table variables are in memory and tempDB. I may be misunderstanding what you mean there, but temp tables are also memory resident unless it becomes necessary to write them to disk (memory pressure, too large a table)

Local Temporary Tables and Table Variables

peterhe — Sun, 31 Aug 2008 23:17:22 GMT

Comments posted to this topic are about the item [B]Local Temporary Tables and Table Variables[/B]