Table variable that uses the tempdb

[MarnickTelenet]

Does anyone know if SQL2005 is better than SQL2000 with the table variables (DECLARE @tblTable TABLE ...)?

Table vars are "SQL-tables in memory".

If you put to much data in a table var so the table var needs more memory than available/free, they say the table var will use the tempdb.

In that case, what is the name of that tempdb-table that will be created? Is that name on every run the same name?

In that case, is there a chance that two concurrent users of the same stored-procedure that (of course) uses the same table var locks each others tempdb-table because two concurrent users are using the same tempdb-table that is created because of not enough memory available?

 

[bborissov]

No,
Even if two (or many) users use the same TEMP TABLE (not table variable) they will see only table they created. Every temp table are stored in TempDB with unique name based on the connection. Table variables are DROP-ed when the batch where they are defined disappears.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[mrdenny]

Table variables are never swapped to tempdb. They are memory only objects. If more memory is required than is available the table variable will be swapped to the pagefile not the tempdb datafiles.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Lamprey13]

Table variables are never swapped to tempdb. They are memory only objects. If more memory is required than is available the table variable will be swapped to the pagefile not the tempdb datafiles.

Are you 100% sure about that? The information I have read says that it will use memory (obviously) but if memory runs out the table variable is, in essence, swaped out to temp db just like a normal temp table. But, maybe I am mistaken and Table Variables are just cached in tempdb.

I did a little looking around and did not see anything difinative about this, but here are two articles for wat it is worth:

http://msdn2.microsoft.com/en-us/library/ms345368.aspx

http://blogs.ittoolbox.com/c/progra...riables-when-to-use-what-and-why-part-2-16303

-Ryan

 

[mrdenny]

Now that I've gone some looking into table variables it appears that table variables do indead get written to the tempdb database. This is new to SQL Server 2005. I'll have to adjust some of my thinking because of this.

This takes a lot of the performance benifit of using table variables over temp tables away.

I'll have to ask Microsoft about this.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ESquared]

mrdenny,

This is not new to SQL Server 2005. Table variables have always been objects which can and often do take tempdb space. I remember reading articles on this over two years ago.

Table variables CAN have less tempdb usage than an equivalent temp table. But there's no guarantee.

I've seen table variables perform worse than temp tables even for plain vanilla usage.

Also, the ability to do schema modification on a temp table, or to add and remove indexes and constraints, can make temp tables a manyfold improvement over table variables.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[MarnickTelenet]

Thanks for all this reactions!

Do I understand correct that there is absolutely NO chance that two concurrent users of the same stored-procedure will lock each other because of the use of a table variable in that stored-procedure.

If necessary (not enough memory available), the tempdb will be used buth the table in that tempdb will get a unique name by each user-session. So it will not be the same tempdb table the concurrent users use and in that way there is NO chance of locking the same tempdb-table?

Do I understand correct?

 

[SQLSister]

Yes, you are correct. The temp tables are not stored in temp db by the name you give them, so many users can run the same code with different input variables at the same time and they will not affect each other.

Questions about posting. See faq183-874