Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

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


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 42,436, Visits: 35,490
Yes, I did.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 21,202, Visits: 14,886
Thanks. That is what I thought.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #848779
Posted Sunday, January 17, 2010 4:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 36,714, Visits: 31,164
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

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

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:23 AM
Points: 436, Visits: 255
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
Post #1052410
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse