SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Table Types


SQL Server Table Types

Author
Message
Louis Roy
Louis Roy
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 41
Comments posted to this topic are about the item SQL Server Table Types
Alex Fekken
Alex Fekken
SSC-Addicted
SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)

Group: General Forum Members
Points: 499 Visits: 460
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
Jeff Moden
SSC Guru
SSC Guru (381K reputation)SSC Guru (381K reputation)SSC Guru (381K reputation)SSC Guru (381K reputation)SSC Guru (381K reputation)SSC Guru (381K reputation)SSC Guru (381K reputation)SSC Guru (381K reputation)

Group: General Forum Members
Points: 381270 Visits: 42994
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... Wink

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
humbleDBA
humbleDBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1496 Visits: 1510
Ditto what Jeff has said...
MarkusB
MarkusB
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17093 Visits: 4208
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.

Markus Bohse
andremyburgh
andremyburgh
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 3
"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

Andre
Simon Sabin
Simon Sabin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3022 Visits: 108
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
wim.buyens
SSChasing Mays
SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)

Group: General Forum Members
Points: 649 Visits: 188
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
Jim Russell-390299
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4207 Visits: 1403
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
Vic Kirkpatrick-173212
SSC Eights!
SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)

Group: General Forum Members
Points: 985 Visits: 76
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search