Local Temporary Tables and Table Variables

Question

Local Temporary Tables and Table Variables

Viewing 5 posts - 31 through 34 (of 34 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Yes, I did.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Thanks. That is what I thought.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

smeet.sinha Mr or Mrs. 500 Points: 577 More actions · Answer 3

One more major diff.

You can not use temp tables in UDF

But can use table variables in UDF....

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

True enough. You should also mention that most UDFs aren't necessary either. 🙂

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Graham_Day Ten Centuries Points: 1103 More actions · Answer 5

GilaMonster (8/31/2008)
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)

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):

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

* According to KB305977, a table variable can hold more data than could fit into memory.

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k