January 16, 2010 at 12:59 pm
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
January 16, 2010 at 1:08 pm
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
January 17, 2010 at 4:15 am
One more major diff.
You can not use temp tables in UDF
But can use table variables in UDF....
February 5, 2010 at 6:03 pm
True enough. You should also mention that most UDFs aren't necessary either.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2011 at 8:29 am
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
Viewing 5 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy