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


SQL Server Table Types


SQL Server Table Types

Author
Message
Someguy
Someguy
Old Hand
Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)

Group: General Forum Members
Points: 325 Visits: 579
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
Dean Cochrane
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 641
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
Simon Sabin
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1048 Visits: 107
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
GSquared
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30623 Visits: 9730
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
oabusa
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

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



i.komarovsky
i.komarovsky
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 123
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!
Jerry Hung
Jerry Hung
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1844 Visits: 1208
as mentioned couple posts above
Table Variable is not transaction-controlled is something to watch out for too
you cannot rollback on it

I only learned about that recently after reading the T-SQL Querying book Tongue
and my 1st thought "Gee, no wonder" because I couldn't figure out why 2 weeks ago on a script that uses table variable

SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Michael Poppers
Michael Poppers
SSC Eights!
SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)

Group: General Forum Members
Points: 911 Visits: 416
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 Smile; 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.
sg_dbdev
sg_dbdev
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 100
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 procedure
IF OBJECT_ID ('Proc1') IS NOT NULL
DROP PROCEDURE Proc1
GO
CREATE PROCEDURE Proc1
AS
SELECT 1, 2
UNION
SELECT 2, 1
go

exec Proc1
go

-- table variable example
DECLARE @vartable TABLE (
col1 INT
, col2 VARCHAR(10)
, col3 FLOAT
)

INSERT @vartable (col1, col2)
EXEC Proc1


SELECT * FROM @vartable

INSERT @vartable (col2, col3)
EXEC Proc1


SELECT * FROM @vartable


-- temp table example
CREATE TABLE #temptable (
col1 INT
, col2 VARCHAR(10)
, col3 FLOAT
)
INSERT #temptable (col1, col2)
EXEC Proc1


SELECT * FROM #temptable

INSERT #temptable (col2, col3)
EXEC Proc1


SELECT * FROM #temptable


-- cleanup
DROP TABLE #temptable
DROP PROCEDURE Proc1


Cheers
Simone Gigli
sg_dbdev
sg_dbdev
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 100
Someguy 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
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