Click here to monitor SSC
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
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 41
Comments posted to this topic are about the item SQL Server Table Types
Alex Fekken
Alex Fekken
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45460 Visits: 39946
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
humbleDBA
humbleDBA
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 1500
Ditto what Jeff has said...
MarkusB
MarkusB
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4457 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
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 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
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 107
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
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 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
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 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
Mr or Mrs. 500
Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)

Group: General Forum Members
Points: 501 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