SQL Server Table Types

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Louis Roy » SQL Server Table Types

25 posts, Page 2 of 3

««1 2 3 »»

SQL Server Table Types

Rate Topic

Display Mode

Topic Options

Author

Message

Someguy

Posted Wednesday, April 02, 2008 6:26 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 07, 2012 10:39 AM
Points: 153, Visits: 565

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.”

Post #478406

Dean Cochrane

Posted Wednesday, April 02, 2008 7:18 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 3:15 PM
Points: 85, Visits: 607

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.

Post #478459

Simon Sabin

Posted Wednesday, April 02, 2008 7:27 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:18 AM
Points: 573, Visits: 95

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

Post #478480

GSquared

Posted Wednesday, April 02, 2008 7:31 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

Post #478487

oabusa

Posted Wednesday, April 02, 2008 7:39 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 27, 2012 8:36 AM
Points: 111, 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.

Post #478493

i.komarovsky

Posted Wednesday, April 02, 2008 7:40 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 2:45 PM
Points: 133, Visits: 56

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!

Post #478494

Jerry Hung

Posted Wednesday, April 02, 2008 7:55 AM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:02 PM
Points: 768, Visits: 1,159

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 :P
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

Post #478511

Michael Poppers

Posted Wednesday, April 02, 2008 9:58 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 17, 2013 2:34 PM
Points: 565, Visits: 360

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.

Post #478654

sg_dbdev

Posted Wednesday, April 02, 2008 11:24 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 24, 2012 8:19 AM
Points: 18, Visits: 99

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

Post #478732

sg_dbdev

Posted Wednesday, April 02, 2008 11:37 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 24, 2012 8:19 AM
Points: 18, Visits: 99

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

Post #478743

« Prev Topic | Next Topic »

25 posts, Page 2 of 3

««1 2 3 »»

Permissions