InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 17:40:32 GMT20http://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxThank you Patrick... It is due to responses like yours that makes me want to do this.Mon, 18 Jun 2012 17:58:18 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxMost comprehensive comparison table variables and temp tables I've ever seen. ThanksMon, 18 Jun 2012 17:05:32 GMTPatrick Gehttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxI want to thank Lawrence, Andrew, opc, Skamath, Haining, Alex, Jeff and Sean for all the kind and positive words for me. Thanks a bunch for the feedback.Mon, 11 Jun 2012 12:06:32 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspx[quote][b]Andrew Diniz (6/8/2012)[/b][hr]Thanks to Wayne for an awesome article! And thanks others for great discussion.There's one point that no one picked up that I find inaccurate:[quote]Note that the estimated execution plan for both table variables and temporary tables will show 1 row; [/quote]Agreed, the cardinality for the table variable is 1 row (without recompilation) but the temporary table?? Execute:[code]select 1 id into #obsunion select 2union select 3union select 4union select 5go[/code]and then examine the estimated execution plan for:[code]select * from #obs;go[/code]The full table scan of #obs has cardinality 5, not 1, even before the query is executed and the plan cached. Given that statistics are created for temporary tables this is exactly what I'd expect so am confused why you would suggest otherwise. What have I missed?[/quote]Andrew, I do believe that you are right... you found something that was missed the first time around. I'll have to look into this tonight and respond.Mon, 11 Jun 2012 12:00:28 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspx[quote][b]sparky-407434 (6/8/2012)[/b][hr]I had been using temp tables in a large import process and made the change to table variables and saw a big improvement in performance. However, the server was later moved to a virtual server (VMWare) and the processes ran much slower (x40 slower). I reverted back to temp tables and the process improved again (though not to the original speed on physical server using table variables)I think we need more discussion when looking at how different methods interact with disk / memory, as to how they compare across both physical and virtual environments. In my case what is good for physical was bad for virtual and vice versa. Or perhaps there are things which the VM administrators need to do to overcome this problem.[/quote]And this highlights one very important thing... always test in your environment. Your results may be different, and may change over time. Thanks for bringing this up Sparky!Mon, 11 Jun 2012 11:57:45 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspx[quote][b]opc.three (6/8/2012)[/b][hr]Very well done. I especially like the comparison table you produced. I have plans to refer some developers to read this article as the misuse of Table Variables is rampant in the current shop. In the article:[quote]Microsoft [url=http://msdn.microsoft.com/en-us/library/ms174979.aspx]recommends[/url] using table variables (in Reference 4).[/quote]What would be unfortunate is if the initial adoption of such practices were due to a poorly worded recommendation from Microsoft so I would like to read their actual recommendation. I visited Reference 4 as shown as [u][url=http://msdn.microsoft.com/en-us/library/ms174979.aspx]T-SQL BOL (SQL 2008), CREATE TABLE[/url][/u] however I must be missing it. I would like to have my bases covered before I bring the issue for discussion with the group. Can you point me to the recommendation made by Microsoft, or restate it here?Thanks again for a great read.[/quote]The way that MS does their SQL BOL links, that link now points to the SQL 2012 BOL entry. Check out the SQL 2008 (not R2) link at: [url=http://msdn.microsoft.com/en-us/library/ms174979%28v=sql.100%29.aspx][u]http://msdn.microsoft.com/en-us/library/ms174979%28v=sql.100%29.aspx[/u][/url] - it's in the "Temporary Tables" section, and it has been removed from the SQL 2008R2 / 2012 sections.Mon, 11 Jun 2012 11:31:33 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxSuperb article. Well worth the read.Sun, 10 Jun 2012 13:46:10 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxI know it's a reprint but I have to say it again... I consider this to be the definitive article on the differences between Table Variables and Temp Tables. It has opened the eyes of several people that I know, especially about the myth that Table Variables are "memory only" and that Temp Tables are "disk only". Very well done, Wayne.Sat, 09 Jun 2012 08:06:15 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxVery good article, thanks. And apparently I wasn't the only one who must have missed it the first time around, so it is also a good illustration of the value of resurfacing older articles.Fri, 08 Jun 2012 21:40:27 GMTAlex Fekkenhttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxReally good article. I will read it more in details.Thanks Wayne!Fri, 08 Jun 2012 21:14:06 GMTHaininghttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxI wish I could rate it more than 5 stars. Excellent article. Concise and precise.Fri, 08 Jun 2012 10:28:50 GMTskamathhttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxVery well done. I especially like the comparison table you produced. I have plans to refer some developers to read this article as the misuse of Table Variables is rampant in the current shop. In the article:[quote]Microsoft [url=http://msdn.microsoft.com/en-us/library/ms174979.aspx]recommends[/url] using table variables (in Reference 4).[/quote]What would be unfortunate is if the initial adoption of such practices were due to a poorly worded recommendation from Microsoft so I would like to read their actual recommendation. I visited Reference 4 as shown as [u][url=http://msdn.microsoft.com/en-us/library/ms174979.aspx]T-SQL BOL (SQL 2008), CREATE TABLE[/url][/u] however I must be missing it. I would like to have my bases covered before I bring the issue for discussion with the group. Can you point me to the recommendation made by Microsoft, or restate it here?Thanks again for a great read.Fri, 08 Jun 2012 05:38:29 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxThanks to Wayne for an awesome article! And thanks others for great discussion.There's one point that no one picked up that I find inaccurate:[quote]Note that the estimated execution plan for both table variables and temporary tables will show 1 row; [/quote]Agreed, the cardinality for the table variable is 1 row (without recompilation) but the temporary table?? Execute:[code]select 1 id into #obsunion select 2union select 3union select 4union select 5go[/code]and then examine the estimated execution plan for:[code]select * from #obs;go[/code]The full table scan of #obs has cardinality 5, not 1, even before the query is executed and the plan cached. Given that statistics are created for temporary tables this is exactly what I'd expect so am confused why you would suggest otherwise. What have I missed?Fri, 08 Jun 2012 03:59:18 GMTAndrew Dinizhttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxI had been using temp tables in a large import process and made the change to table variables and saw a big improvement in performance. However, the server was later moved to a virtual server (VMWare) and the processes ran much slower (x40 slower). I reverted back to temp tables and the process improved again (though not to the original speed on physical server using table variables)I think we need more discussion when looking at how different methods interact with disk / memory, as to how they compare across both physical and virtual environments. In my case what is good for physical was bad for virtual and vice versa. Or perhaps there are things which the VM administrators need to do to overcome this problem.Fri, 08 Jun 2012 03:49:46 GMTsparky-407434http://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxExcellent article. Very comprehensive and informative.Will be very useful in future to have comprehensive comparison data side by side in one place.Fri, 08 Jun 2012 03:47:01 GMTLawrence Moorehttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxyup makes sense. This is so interesting, .. you really need to be aware of and careful with this.Tue, 20 Apr 2010 14:17:20 GMTMichael H Lutzhttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxHi Wayne!Yes I see that now. My apologies for bad information, I was thinking about it afterward too and yes I see the difference. I was looking at actual rows instead of the optimizer's estimate of number of rows. Thanks for clarifying this clearly and finding a counter example!Best Regards,MikeTue, 20 Apr 2010 14:14:55 GMTMichael H Lutzhttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxQuote"When you start joining the table variable to other tables, then the cardinality error frequently results in a bad execution plan, which gets worse the more complex the query is, and the larger the row count is."My comment on the performance with table variable and temporary tableThe optimizer of SQL SERVER probably treats a table variable as a tiny table, therefore gives a loop join in the execution plan. This is the primary factor that causes table variable solution is at least dozens time slower than a temporary table solution under certain conditions assuming that both solutions have same data schema and indexes. In other word, we can force the optimizer to choose the appropriate join and have similar performance results.The trick is the performance difference between loop, hash and merge join.Feng Zhengzhengfengchina@yahoo.comMon, 19 Apr 2010 11:55:57 GMTzhengfengchinahttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxQuote"When you start joining the table variable to other tables, then the cardinality error frequently results in a bad execution plan, which gets worse the more complex the query is, and the larger the row count is."My comment on the performance with table variable and temporary tableThe optimizer of SQL SERVER probably treats a table variable as a tiny table, therefore gives a loop join in the execution plan. This is the primary factor that causes table variable solution is at least dozens time slower than a temporary table solution under certain condition assuming that both solutions have same data schema and indexes. In other word, we can force optimizer to choose the appropriate join and have similar performance results.The trick is the performance difference between loop, hash and merge join.Feng Zhengzhengfengchina@yahoo.comMon, 19 Apr 2010 09:21:44 GMTzhengfengchinahttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspx[quote][b]Michael H Lutz (4/17/2010)[/b][hr]..There is one thing I see though that is incorrect, the statement " This means that the execution plan for table variables will always treat the table as if it has 1 row. " is not true.[/quote]I agree, and mentioned this earlier - though in different circumstances. Re-using Wayne's code:[code="sql"]select count(*) from @test option (recompile);[/code]Produces the following actual execution plan (note the estimated row count and parallel plan):[img]http://www.sqlservercentral.com/Forums/Attachment5691.aspx[/img]Sat, 17 Apr 2010 23:34:35 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspx[quote][b]Michael H Lutz (4/17/2010)[/b][hr]Great article. There is one thing I see though that is incorrect, the statement " This means that the execution plan for table variables will always treat the table as if it has 1 row. " is not true. I realize that SQL Server does not keep stats on indexes on table variables. But I have a counter-example to this claim.If you create a table and load it with let's say a million rows. Then create a table variable (doesn't matter if it has a primary key or unique constraint the result is the same) and load the million rows into the table variable. Let's say the permanent table is TableA and the table variable is @TableA. Then run the following query:SELECT COUNT(*) FROM (SELECT @TableA.* FROM @TableA INNER JOIN TableA on TableA.SomeCol = @TableA.SomeCol) allrowsYou will see in the actual execution plan that SQL Server knows the exact number of rows in the table variable. I found this to be true on SQL 2005 and 2008, and regardless of whether or not auto_create_statistics is on or off and auto_update_statistics is on or off. In some magical way SQL knows the number of rows at least in this case of the temp tables. It doesn't have stats I'm assuming, but it does know the # of rows. The estimated execution plan estimates 1 row for the table variable but the actual plan has the exact number of rows.[/quote]Hi Micheal,No, that just means that when running the query, it is able to get the proper count of records. The optimizer still treats it as only having one record.Let me demonstrate that. This code builds a one-million row tally table into a table variable:[code="sql"]declare @test table (N int PRIMARY KEY CLUSTERED);;WITHTENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)INSERT INTO @test (N)SELECT N from TALLY ORDER BY Nselect count(*) from @test[/code]This produces this execution plan: [img]http://www.sqlservercentral.com/Forums/Attachment5689.aspx[/img]I've circled the two parts of interest... see how the "Estimated Number of Rows" is one? That's the optimizer treating the table variable as one row. Since the optimizer thinks that the table variable only has one row, the plan might not be the best for the query.Sat, 17 Apr 2010 08:39:51 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxGreat article. There is one thing I see though that is incorrect, the statement " This means that the execution plan for table variables will always treat the table as if it has 1 row. " is not true. I realize that SQL Server does not keep stats on indexes on table variables. But I have a counter-example to this claim.If you create a table and load it with let's say a million rows. Then create a table variable (doesn't matter if it has a primary key or unique constraint the result is the same) and load the million rows into the table variable. Let's say the permanent table is TableA and the table variable is @TableA. Then run the following query:SELECT COUNT(*) FROM (SELECT @TableA.* FROM @TableA INNER JOIN TableA on TableA.SomeCol = @TableA.SomeCol) allrowsYou will see in the actual execution plan that SQL Server knows the exact number of rows in the table variable. I found this to be true on SQL 2005 and 2008, and regardless of whether or not auto_create_statistics is on or off and auto_update_statistics is on or off. In some magical way SQL knows the number of rows at least in this case of the temp tables. It doesn't have stats I'm assuming, but it does know the # of rows. The estimated execution plan estimates 1 row for the table variable but the actual plan has the exact number of rows.Sat, 17 Apr 2010 06:41:24 GMTMichael H Lutzhttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxWhile you may not be able to use SELECT <columnlist> INTO <table> with table variables it's possible to use:insert @tableselect col1, col2...from someTablewhere someColumn = someValue;To circumvent the issue.Thu, 15 Apr 2010 15:03:56 GMTGene Porterhttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspx[quote][b]Toby White (4/12/2010)[/b][hr]Excellent article...[/quote]Thanks Toby.Wed, 14 Apr 2010 21:53:39 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspx[quote][b]Tom.Thomson (4/14/2010)[/b][hr]Great article, it's very thorough, meticulously clear descriptions and examples, I wish we could have more articles like this.[/quote]Thanks Tom! Nothing like a little pressure for any future articles... I sure hope I can live up to it!Wed, 14 Apr 2010 21:52:02 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspx[quote][b]Tom.Thomson (4/14/2010)[/b][hr][quote][b]RBarryYoung (6/15/2009)[/b][hr][quote][b]GilaMonster (6/15/2009)[/b]...No one I've spoken with knows exactly where the estimate is coming from. It's not the 30% of the table that's used with inequalities when a estimate can't be made. That would be 30 000 rows. Someone did point out that the estimates (which change as the total number of rows in the table variable changes) is approx (total rows)[sup]0.75[/sup], though I haven't done the calculations myself to verify that.[/quote]In this case the exponent is closer to 0.886.[/quote]No, 100000^0.886 is 26915, and 100000^0.75 is 5623. Since the total rows were 100000 and the extimate 5623, the exponent is much closer in this case to 0.75 than to 0.886.[/quote] Arrrggh! Yep, you are correct Tom (and Gail), I used the actual result rowcount (17134) as the base instead of the total rowcount for the source table (100000).Wed, 14 Apr 2010 21:03:48 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspx[quote][b]kajalchatterjee (4/9/2010)[/b][hr]Comparing Table Variables with Temporary TablesI ran the following piece of code in SQL-Server 2005 and i can not see any entry of table variable in tempdb database. I can only see temp table entry in tempdb database.So it is not Myth that table variables are stored in Memory but it seems tobe tru. Please let me know if am i missing anything. [/quote]You (or your code) appear to be missing a couple of spaces. I looked around in tempdb a bit in the dim and distant past to confirm for myself that table variables did show up there (using SQLS 2000), but I hadn't actually checked it for SQLS 2008 so I ran your code (with necessary spaces added). The results I got were:[quote]name--------------------------------------------------------------------------------------------------------------------------------#tempTables_________________________________________________________________________________________________________000000000011(1 row(s) affected)name--------------------------------------------------------------------------------------------------------------------------------#2E1BDC42(1 row(s) affected) [/quote] No surprises. There's very clearly an extra temp table (called #2E1BDC42 on that particular run) in tempdb (and this is the table variable).If it doesn't work in your work system there's something really weird going on. I can't see any reason why it shouldn't. I even tried wrapping it in a transaction and trying all the different isolation levels in case that could have any effect (although I was very sure it couldn't) and it made no difference.Wed, 14 Apr 2010 07:13:41 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxGreat article, it's very thorough, meticulously clear descriptions and examples, I wish we could have more articles like this. It's also provoked some fine discussion.edit: originally pasted the wrong text into here!Wed, 14 Apr 2010 06:38:18 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspx[quote][b]RBarryYoung (6/15/2009)[/b][hr][quote][b]GilaMonster (6/15/2009)[/b]...No one I've spoken with knows exactly where the estimate is coming from. It's not the 30% of the table that's used with inequalities when a estimate can't be made. That would be 30 000 rows. Someone did point out that the estimates (which change as the total number of rows in the table variable changes) is approx (total rows)[sup]0.75[/sup], though I haven't done the calculations myself to verify that.[/quote]In this case the exponent is closer to 0.886.[/quote]No, 100000^0.886 is 26915, and 100000^0.75 is 5623. Since the total rows were 100000 and the extimate 5623, the exponent is much closer in this case to 0.75 than to 0.886.I haven't a clue where the number comes from, or indeed why the estimate should have an exponential form.Wed, 14 Apr 2010 06:27:34 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxExcellent article. You did a great job of explaining why table variables often perform poorly when they have many rows and are joined, especially in complex T-SQL. I did run across something puzzling recently related to this topic. A developer had written a 1000+ line procedure using a table variable somewhere in the middle to populate and then use later to join and updated other tables in the same procedure. The update statement was an extremely poor performer as we would expect. Since the engine can't store statistics on the columns I expected this to be the case, but before I recommended that a temp table be used I tried using the with recompile option of just the update statement. The actual execution plan then changed from expecting one row in the table variable to expecting the correct amount of rows. In my case the cardinality was roughly 1:1 so this solved the problem and the execution plan was optimal. So, although the engine cannot store statistics on columns of the table variable, it does seem to have the ability to calculate the row count, which it can use if it compiles any statements later in the process.I found this exercise ironic because the benefit of table variables is that the procedure doesn't necessarily have to recompile, but the only way I could get the table variable to work was by recompiling statements that used the table variable (after it was initially populated).Mon, 12 Apr 2010 07:07:17 GMTToby Whitehttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspx[quote][b]kajalchatterjee (4/10/2010)[/b][hr]Well Wayne, it works perfectly in my local sqlserver (home computer) but it does not work in server at my office. At my office when i ran the code it did not display table variable in tempdb.sysobjects. I am not sure why it is not working but it only showed temporary table.[/quote]Well, I'm puzzled by this. I know that if you don't have the "GO" between selecting from tempdb..sysobjects and declaring the table variable, I think the optimizer does something like the parameter sniffing, and show it as a new table. But the code you posted has all of the necessary "GO" statements, so it should be working.Sun, 11 Apr 2010 05:36:27 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxWell Wayne, it works perfectly in my local sqlserver (home computer) but it does not work in server at my office. At my office when i ran the code it did not display table variable in tempdb.sysobjects. I am not sure why it is not working but it only showed temporary table.Sat, 10 Apr 2010 20:20:14 GMTkajalchatterjeehttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspx[quote][b]T Norton (4/9/2010)[/b][hr]Very well done..!!!This article clearly articulates and validates what I've been preaching to my team for years.Thanks for doing this…! :-D[/quote]You're welcome! And thanks for the accolades.Sat, 10 Apr 2010 17:28:18 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspx[quote][b]kajalchatterjee (4/9/2010)[/b][hr]Comparing Table Variables with Temporary TablesI ran the following piece of code in SQL-Server 2005 and i can not see any entry of table variable in tempdb database. I can only see temp table entry in tempdb database.So it is not Myth that table variables are stored in Memory but it seems tobe tru. Please let me know if am i missing anything.----------------------------------------- make a list of all of the user tables currently active in the -- TempDB database if object_id('tempdb..#tempTables') is not null drop table #tempTables select name into #tempTables from tempdb..sysobjects where 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 not in (select name from #tempTables) GO-----------------------------------[/quote]I just ran the above code, and it demonstrate that the table variable does in fact get in entry in tempdb..sysobjectsSat, 10 Apr 2010 17:27:11 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxThanks. It makes sense.Sat, 10 Apr 2010 08:42:38 GMTkajalchatterjeehttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspx[quote][b]peter-757102 (4/10/2010)[/b][hr]However true, that does not make it suddenly all right ;)[/quote]I tend to agree - and I have no idea why constraints are schema-scoped objects.Sat, 10 Apr 2010 06:55:32 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspx[quote][b]Paul White NZ (4/9/2010)[/b][hr][quote][b]peter-757102 (4/9/2010)[/b][hr]Strange, indeed column defaults and primary keys seem not be properly mapped transparently when applied to temp tables.[/quote]Constraints are always schema-scoped objects - see sys.objects.This is true for 'real' tables too, not just temporary tables. It is only the table object that is private to the session.It is true for table variables too - if they allowed named constraints, you could get collisions there too.It has always been this way...:cool:One practice is to always include the (schema-qualified) object name in the constraint name.[/quote]However true, that does not make it suddenly all right ;). It is not as if a column constraint or a primary key has to affect anything else then the table it is applied to. If you are going to hide the true table, do it right and do it for all the interfaces with its environment.Right now if an error happens you get a random constraint name which doesn't tell the application on top of it, anything!Sat, 10 Apr 2010 06:39:11 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspx[quote][b]kajalchatterjee (4/9/2010)[/b][hr]If i execute the last two sql statements separately the table variable info is not displayed from tempdb.[/quote]A table variable is scoped to a batch - as it says in the article :-)The table variable is automatically destroyed when your first batch completes.Sat, 10 Apr 2010 01:43:21 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxThe following sql statement only shows table variable in tempdb database if execute them all at a time. If i execute the last two sql statements separately the table variable info is not displayed from tempdb. The table variable information is not persistent in tempdb within a session it just exists within the sql block.[b]declare @mytable table (rowid int)select * from tempdb.sys.objects where type='U'select name from tempdb.sys.columns where name like 'rowid%'[/b]However the following sql statements deal with temp table. The temp table information exist within tempdb database for this session. So it does not matter how you execute the sql statements i.e. all of them together or one by one.[b]create table #mytable (rowid int)select * from tempdb.sys.objects where type='U'select name from tempdb.sys.columns where name like 'rowid%'[/b]So the scope of table variable within tempdb even not persistent within the same session (unlike temptable) it only exists for sql block execution time period.Fri, 09 Apr 2010 23:43:03 GMTkajalchatterjeehttp://www.sqlservercentral.com/Forums/Topic732005-1273-1.aspxYou are correct. I just executed the following sql statements in my personal machine and i can see the table variable in tempdb database. Maybe it is something to do with my work computer server or access level.Thanks anyway.Fri, 09 Apr 2010 23:28:20 GMTkajalchatterjee