SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Louis Roy / SQL Server Table Types / Latest Posts

RE: SQL Server Table Types

ling_l_w — Wed, 09 Apr 2008 00:06:23 GMT

Thanks for the interesting article.Recently I have experimented with derived table and also table variable.For the simple examples below, using derived table, the query took about 1 min 17 sec to complete, and using the table variable, the query took about 4 sec to complete. I can't explain why it happens this way.I have tried a few more examples, and it is still the table variable that wins out.Can someone please help to explain?RegardsLW LingExamples attached below:Use TESTDBgoSet NoCount Onselect DISTINCT ACTIVITY_TYPE, RESOURCE_ACTIVITY_TYPE_IDfrom (select top 100 ACTIVITY_TYPE, RESOURCE_ACTIVITY_TYPE_IDfrom [DBO].[V_ACD_SIGN_ONOFF_GRP]) as p(This took from 1 min 17 sec to 1 min 25 sec to copmplete)USE TESTDBGOSET NOCOUNT ONDeclare @t table(ID int Identity (1,1),ACTIVITY_TYPE varchar (50),RESOURCE_ACTIVITY_TYPE_ID int)Insert into @t(ACTIVITY_TYPE, RESOURCE_ACTIVITY_TYPE_ID)select top 10000 ACTIVITY_TYPE, RESOURCE_ACTIVITY_TYPE_IDfrom [DBO].[V_ACD_SIGN_ONOFF_GRP]select DISTINCT ACTIVITY_TYPE, RESOURCE_ACTIVITY_TYPE_IDfrom @t/* The above takes 4 sec to 9 sec to process 10,000 items */

RE: SQL Server Table Types

Robert-378556 — Fri, 04 Apr 2008 11:10:15 GMT

Since you mention temp tables #temp (local to connection), you should also mention global temp tables ##temp (accessible by all connections).Also, how to check it the temp table exists.Best practice is to first discuss a topic and then write the article.Such article is then more accurate, informative and an excellent reference to point newbies to.

RE: SQL Server Table Types

timothyawiseman — Wed, 02 Apr 2008 22:43:51 GMT

I agree with Alex that CTEs are more readable than most other forms of derived table, and they were well worthy of a mention, but they are essentially a form of derived table.Thank you for the article. It was well written and logical.

RE: SQL Server Table Types

Alex Fekken — Wed, 02 Apr 2008 18:07:56 GMT

In my opinion whether derived tables and/or CTEs are "tables in any sense" is not really relevant. What is relevant is that they can, and probably should, be used in many situations where many would use some sort of temporary table. For example, they have a significant scope advantage over the other pretenders. That is why it makes sense to discuss them in an article on temporary tables. As far as the relational model goes tables, view, CTEs and other relations are supposed to be equivalent anyway. It is only when you look beyond the relational model and consider things like performance that the distinction should become relevant. I am not saying performance issues are not important, but I think they should be discussed in the context of a relational approach.

RE: SQL Server Table Types

Toby White — Wed, 02 Apr 2008 15:39:05 GMT

I came to this forum to mention the inaccuracies around the table variable being stored in memory, but since that has already been covered several times I will just mention that a cool feature of 2008 is that table variables can be used in parameters for stored procedures.The table variable always seemed so limited to me without this feature because if you can only access the table inside of a stored procedure why not just create a local temp table - much easier for debugging.In several cases I have used global temp tables when I needed to persist data across dynamic SQL and/or inner procedures, but that is prone to collisions. The table variable will act much like a semi-persistent result set or array in 2008 which will make it useful.

RE: SQL Server Table Types

sg_dbdev — Wed, 02 Apr 2008 11:37:54 GMT

[b]Someguy[/b] posted already same idea, but I didn't see it...My mistake...:-(I am sharing exactly the same debug technique he is using. I can add that I use PRINT debug for normal variables and I seldom remove them as they don't influence SP output dataset. However, I find them very useful when I use dynamic SQL, because I can get the whole SQL statement while a variable watch in VStudio doesn't display the full value (I can see them in the output pane of the debugger as soon as execution proceeds).Simone

RE: SQL Server Table Types

sg_dbdev — Wed, 02 Apr 2008 11:24:55 GMT

Despite MSSQL 2000, MSSQL 2005 allows to insert SP output into a particular subset of columns independently if it is a table variable or a temporary table. I found it nice to use when you work with sparse tables and it generally gives more clarity to SP code.-- create dummy procedureIF OBJECT_ID ('Proc1') IS NOT NULL DROP PROCEDURE Proc1GOCREATE PROCEDURE Proc1AS SELECT 1, 2UNIONSELECT 2, 1goexec Proc1go-- table variable exampleDECLARE @vartable TABLE ( col1 INT, col2 VARCHAR(10), col3 FLOAT)[b]INSERT @vartable (col1, col2)EXEC Proc1[/b]SELECT * FROM @vartable[b]INSERT @vartable (col2, col3)EXEC Proc1[/b]SELECT * FROM @vartable-- temp table exampleCREATE TABLE #temptable ( col1 INT, col2 VARCHAR(10), col3 FLOAT)[b]INSERT #temptable (col1, col2)EXEC Proc1[/b]SELECT * FROM #temptable[b]INSERT #temptable (col2, col3)EXEC Proc1[/b]SELECT * FROM #temptable-- cleanupDROP TABLE #temptableDROP PROCEDURE Proc1CheersSimone Gigli

RE: SQL Server Table Types

Michael Poppers — Wed, 02 Apr 2008 09:58:21 GMT

Re Someguy's comment: I do SELECT [columns] INTO [new_table] FROM [source] ... (in essence, creating a copy) quite often (mostly, when I want to back up rows that I'm about to modify, just in case I mess up :); sometimes, because I will then tweak the copy and use its data to UPDATE the source).Errors aside, I liked the way this article was written.

RE: SQL Server Table Types

Jerry Hung — Wed, 02 Apr 2008 07:55:24 GMT

as mentioned couple posts aboveTable Variable is not transaction-controlled is something to watch out for tooyou cannot rollback on itI only learned about that recently after reading the T-SQL Querying book :Pand my 1st thought "Gee, no wonder" because I couldn't figure out why 2 weeks ago on a script that uses table variable

RE: SQL Server Table Types

i.komarovsky — Wed, 02 Apr 2008 07:40:20 GMT

I think the mistake author made about table variables residing in memory, not in tempdb, which was mentioned in several comments is quite serious. I think SQLServerCentral should validate the content of the articles. In case if erroneous information was published, it should be removed from the site.Currently, when all our comments are hidden in the discussion, the reader may be seriously mislead.Please fix it!

RE: SQL Server Table Types

oabusa — Wed, 02 Apr 2008 07:39:00 GMT

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.

RE: SQL Server Table Types

GSquared — Wed, 02 Apr 2008 07:31:13 GMT

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.

RE: SQL Server Table Types

Simon Sabin — Wed, 02 Apr 2008 07:27:09 GMT

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.

RE: SQL Server Table Types

Dean Cochrane — Wed, 02 Apr 2008 07:18:16 GMT

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.

RE: SQL Server Table Types

Someguy — Wed, 02 Apr 2008 06:26:39 GMT

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 #tempreturnIt can result in much quicker debugging than when using derived tables...

RE: SQL Server Table Types

Vic Kirkpatrick-173212 — Wed, 02 Apr 2008 06:12:11 GMT

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

RE: SQL Server Table Types

Jim Russell-390299 — Wed, 02 Apr 2008 04:44:45 GMT

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?

RE: SQL Server Table Types

wim.buyens — Wed, 02 Apr 2008 04:43:41 GMT

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!

RE: SQL Server Table Types

Simon Sabin — Wed, 02 Apr 2008 03:09:20 GMT

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.

RE: SQL Server Table Types

andremyburgh — Wed, 02 Apr 2008 02:18:28 GMT

"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 2005There 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 asinsert @variableexec sp_whoStarting in SQL Server 2005, this limitation was removed and table variables can now be used as the destination for INSERT EXEC commands.“

RE: SQL Server Table Types

MarkusB — Wed, 02 Apr 2008 01:47:15 GMT

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.

RE: SQL Server Table Types

humbleDBA — Wed, 02 Apr 2008 01:30:21 GMT

Ditto what Jeff has said...

RE: SQL Server Table Types

Jeff Moden — Tue, 01 Apr 2008 23:06:22 GMT

[quote]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. [/quote]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... ;)[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k[/url]Just in case someone doesn't actually want to make the trip, here's a copy of Q4/A4 from the URL above...[quote]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: [b]A table variable is not a memory-only structure[/b]. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. [b]Table variables are created in the tempdb database similar to temporary tables[/b]. If memory is available, [b]both table variables and temporary tables are created and processed while in memory [/b](data cache). [/quote]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 [i]all [/i]that in version 7 and [b]it hasn't been a problem for about 12 years.[/b] 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.

RE: SQL Server Table Types

Alex Fekken — Tue, 01 Apr 2008 21:50:12 GMT

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.

SQL Server Table Types

Louis Roy — Tue, 01 Apr 2008 21:44:45 GMT

Comments posted to this topic are about the item [B]SQL Server Table Types[/B]