SQL Server Table Types

Question

SQL Server Table Types

Louis Roy

SSC Journeyman

Points: 96
More actions
April 1, 2008 at 9:44 pm

#379530

Comments posted to this topic are about the item SQL Server Table Types

Viewing 15 posts - 1 through 15 (of 24 total)

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

Alex Fekken Ten Centuries Points: 1109 More actions · Answer 1

Thanks for the overview.

It would be good to mention CTEs as well. In my opinion they are very usefull and much more readable than derived tables.

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

Temp Tables are created in the SQL Server TEMPDB database and therefore require more IO resources and locking. Table Variables and Derived Tables are created in memory.

Nice try on the article but you really need to do more research before you write about something like this... the statement above is dead wrong. Here's the URL to prove it... pay particular attention to Question and Answer Q4/A4... 😉

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

Just in case someone doesn't actually want to make the trip, here's a copy of Q4/A4 from the URL above...

Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

Also, I can't put my hands on the reference right now, but all this locking business about Temp tables is mostly a left over myth. In version 6.5, creating and using a temp table would cause all sorts of locking problems... they fixed all that in version 7 and it hasn't been a problem for about 12 years. Yes, mixing DDL and DML will still cause recompiles but most of the blocking done by temp tables is no longer true. Only time it's still true is when using SELECT/INTO and that's so short it just doesn't matter most of the time.

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

humbleDBA Hall of Fame Points: 3540 More actions · Answer 3

humbleDBA

Hall of Fame

Points: 3540

April 2, 2008 at 1:30 am

#797034

Ditto what Jeff has said...

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 4

Jeff is correct about the table variables.

The rerason why temp tables perform better for larger datasets is not so much parallelism but the fact that temp tables can use statistics.

Also it would have been nice to mention the option of creating an index on a temp table. If you have really big datasets it can make quite a difference if you create an index on your temp table.

[font="Verdana"]Markus Bohse[/font]

AndreMyburgh SSC Rookie Points: 25 More actions · Answer 5

"You cannot use a stored procedure to insert data into a Table Variable or Derived Table. For example, the following will work: INSERT INTO #MyTempTable EXEC dbo.GetPolicies_sp whereas the following will generate an error: INSERT INTO @MyTableVariable EXEC dbo.GetPolicies_sp."

I've tested this on SQL2005 and it works fine. Am i doing something "wrong"?

From http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1267047,00.html

“Changes to table variables in SQL 2005

There are limitations on how you can use table variables. The limitations began changing with SQL Server 2005. Namely, table variables could not be used as the destination of an INSERT EXEC command such as

insert @variable

exec sp_who

Starting in SQL Server 2005, this limitation was removed and table variables can now be used as the destination for INSERT EXEC commands.

“

Cheers,

Andre

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 6

Further to the comments about in memory structures which are just a big confusion. Any data written to a table will reside in the data cache(memory) as long as it isn't pushed out by something else, for this reason the data from a table variable or a temp table MAY be in the data cache but may not be.

Additionally derived tables are not tables they are merely syntactic sugar for making queries more readable which is the same for CTEs. A worktable may be produced during the query but these can be generated in many different situations and are more about how a query is fulfilled by the query engine.

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

wim.buyens Ten Centuries Points: 1159 More actions · Answer 7

Table variables are set into memory but also in tempdb!

This is one of the big msitakes people make and I found it in a lot of sql books!

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 8

Re (from the example):

"alias that derived table with the name 'NewestVehicle' ".

I would have thought that the derived table alias was 'MaxVehicles'. Am I missing something?

Vic Kirkpatrick-173212 SSCommitted Points: 1831 More actions · Answer 9

Andre, you beat me to that one. Yes, in 2005 you can insert directly into a table variable from a stored procedure. I just used that bit of functionality last week in fact.

Also, CTE's most definitely belong in this article. Otherwise, this is a good article to discuss because a lot of newbies and even mid-level DBAs / DB developers get this table stuff mixed up. Good discussion.

-Vic

Someguy SSCrazy Points: 2207 More actions · Answer 10

It's also possible to create a temp table with the syntax:

Select (fields) into #temp from (table).

This is much easier than defining the table, but I've heard though that doing this sometimes raises performance issues. I'm guessing that the issues relate to things already mentioned in this discussion like the size of the created table (a large recordset would be more likely to cause spill-over into virtual/disc memory), etc.

Anyone care to comment?

One way or the other it can be a handy debugging tool if you need to find out what's happening in the middle of a complicated procedure and you don't want to re-define temp tables every time you make a small change. At any point in the procedure, you just use:

Select * from #temp

return

It can result in much quicker debugging than when using derived tables...

___________________________________________________
“Politicians are like diapers. They both need changing regularly and for the same reason.”

Dean Cochrane SSC Eights! Points: 992 More actions · Answer 11

This raises a couple of points.

First, ditto what everyone else said about the @table variables. For me, the most important thing about them, and the thing that is most often overlooked, is that @tables live outside transaction control. This can have serious consequences.

Second, derived tables and CTEs are not tables in any sense. They are better thought of as temporary views. I believe that in some cases the optimizer may choose to use tempdb to store the data generated by a derived table or CTE, but this does not make them tables any more than it makes the results of a subquery a table.

Third, I am not aware of any performance issues with #temp tables under SQLServer2K5. For large result sets, #temp tables have some distinct advantages, as someone else mentioned, and you can create indexes on them, which is a major advantage.

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 12

I just wish this discussion was on the same page as the article. That way anyone reading the article can clearly see the comments about what is wrong and thus dispell the generation and propogation of myths.

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

GSquared SSC Guru Points: 260824 More actions · Answer 13

The article is a reasonably good introduction to the subjects, even though it does have the common flub of assuming table variables are RAM and temp tables are HDD. Other than that, I'd say it's good enough for what it aims to accomplish.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

oabusa Old Hand Points: 351 More actions · Answer 14

Actually it is possible to capture the out of a stored procedure in a table variable. I have been using it regularly in SQL Server 2005 for sometime now. Here is how to do it:

INSERT @MyTableVariable EXEC dbo.GetPolicies_sp.