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 Wednesday, April 2, 2008 6:26 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 7:19 AM
Points: 153, Visits: 569
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
Posted Wednesday, April 2, 2008 7:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, May 31, 2014 10:37 PM
Points: 85, Visits: 625
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
Posted Wednesday, April 2, 2008 7:27 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: 573, 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
Post #478480
Posted Wednesday, April 2, 2008 7:31 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Wednesday, April 2, 2008 7:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Wednesday, April 2, 2008 7:40 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 133, Visits: 65
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
Posted Wednesday, April 2, 2008 7:55 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:36 AM
Points: 772, Visits: 1,183
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
Posted Wednesday, April 2, 2008 9:58 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:58 AM
Points: 643, Visits: 400
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
Posted Wednesday, April 2, 2008 11:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:05 AM
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
Post #478732
Posted Wednesday, April 2, 2008 11:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:05 AM
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
Post #478743
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse