Comparing Table Variables with Temporary Tables

Question

Comparing Table Variables with Temporary Tables

Viewing 15 posts - 121 through 135 (of 163 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

kajalchatterjee (4/9/2010)
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.

Your code is not all it might be - but it still works for me.

Notice that the name of the entry for the table variable is its internal # name.

It was #3EF2F937 on my test run.

Anyway, don't take my word for it - read the following blog entry by Sunil Agarwal, Program Manager in the SQL Server Storage Engine Group at Microsoft:

http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 2

peter-757102 (4/9/2010)
Strange, indeed column defaults and primary keys seem not be properly mapped transparently when applied to temp tables.

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

kajalchatterjee Newbie Points: 6 More actions · Answer 3

Oh! I still don't understand why it is not showing for me, Its just zero rows when i execute last sql statement. However i will read the article.

kajalchatterjee Newbie Points: 6 More actions · Answer 4

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

kajalchatterjee Newbie Points: 6 More actions · Answer 5

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

declare @mytable table (rowid int)

select * from tempdb.sys.objects where type='U'

select name from tempdb.sys.columns where name like 'rowid%'

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.

create table #mytable (rowid int)

select * from tempdb.sys.objects where type='U'

select name from tempdb.sys.columns where name like 'rowid%'

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.

Paul White SSC Guru Points: 150467 More actions · Answer 6

kajalchatterjee (4/9/2010)
If i execute the last two sql statements separately the table variable info is not displayed from tempdb.

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

peter-757102 SSCertifiable Points: 6877 More actions · Answer 7

Paul White NZ (4/9/2010)
peter-757102 (4/9/2010)
Strange, indeed column defaults and primary keys seem not be properly mapped transparently when applied to temp tables.
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.

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!

Paul White SSC Guru Points: 150467 More actions · Answer 8

peter-757102 (4/10/2010)
However true, that does not make it suddenly all right 😉

I tend to agree - and I have no idea why constraints are schema-scoped objects.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

kajalchatterjee Newbie Points: 6 More actions · Answer 9

kajalchatterjee

Newbie

Points: 6

April 10, 2010 at 8:42 am

#1149562

Thanks. It makes sense.

WayneS SSC Guru Points: 95460 More actions · Answer 10

kajalchatterjee (4/9/2010)
Comparing Table Variables with Temporary Tables
I 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
-----------------------------------

I just ran the above code, and it demonstrate that the table variable does in fact get in entry in tempdb..sysobjects

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 11

T Norton (4/9/2010)
Very well done..!!!
This article clearly articulates and validates what I've been preaching to my team for years.
Thanks for doing this…! 😀

You're welcome! And thanks for the accolades.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

kajalchatterjee Newbie Points: 6 More actions · Answer 12

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.

WayneS SSC Guru Points: 95460 More actions · Answer 13

kajalchatterjee (4/10/2010)
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.

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.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Toby White SSCertifiable Points: 6187 More actions · Answer 14

Excellent 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).

TomThomson SSC Guru Points: 104773 More actions · Answer 15

RBarryYoung (6/15/2009)
GilaMonster (6/15/2009)...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)^0.75, though I haven't done the calculations myself to verify that.
In this case the exponent is closer to 0.886.

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.

Tom