Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««1234

Local Temporary Tables and Table Variables Expand / Collapse
Posted Saturday, January 16, 2010 12:59 PM



Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 43,986, Visits: 41,371
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

Post #848778
Posted Saturday, January 16, 2010 1:08 PM



Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 19,576, Visits: 17,836
Thanks. That is what I thought.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...


Posting Performance Based Questions - Gail Shaw
Post #848779
Posted Sunday, January 17, 2010 4:15 AM


Group: General Forum Members
Last Login: Monday, August 12, 2013 7:50 AM
Points: 15, Visits: 105

One more major diff.

You can not use temp tables in UDF
But can use table variables in UDF....

Post #848839
Posted Friday, February 5, 2010 6:03 PM



Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 39,666, Visits: 36,795
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Favorite Quotes:
"Has anyone ever told you that a query you have written runs too fast?" - Dwain Camps - 6 Mar 2014

Helpful Links:
How to post code problems
How to post performance problems
Post #860948
Posted Monday, January 24, 2011 8:29 AM


Group: General Forum Members
Last Login: Wednesday, February 3, 2016 3:55 AM
Points: 463, Visits: 264
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%'
-- 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)

* According to KB305977, a table variable can hold more data than could fit into memory.;en-us;305977&Product=sql2k
Post #1052410
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse