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 123»»»

SQL Server Table Types Expand / Collapse
Author
Message
Posted Tuesday, April 1, 2008 9:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 30, 2013 7:28 AM
Points: 4, Visits: 41
Comments posted to this topic are about the item SQL Server Table Types
Post #478211
Posted Tuesday, April 1, 2008 9:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 7:17 PM
Points: 197, Visits: 459
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.
Post #478213
Posted Tuesday, April 1, 2008 11:06 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 35,215, Visits: 31,665
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."

(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 #478252
Posted Wednesday, April 2, 2008 1:30 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:17 AM
Points: 216, Visits: 1,367
Ditto what Jeff has said...
Post #478273
Posted Wednesday, April 2, 2008 1:47 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:08 AM
Points: 4,427, Visits: 4,171
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
Post #478279
Posted Wednesday, April 2, 2008 2:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 3, 2008 6:18 AM
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
Post #478286
Posted Wednesday, April 2, 2008 3:09 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
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
Post #478298
Posted Wednesday, April 2, 2008 4:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 4, 2014 6:11 AM
Points: 237, Visits: 137

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!
Post #478348
Posted Wednesday, April 2, 2008 4:44 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403
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?
Post #478349
Posted Wednesday, April 2, 2008 6:12 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Thursday, November 13, 2008 9:13 AM
Points: 499, 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
Post #478391
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse