SQL Server Table Types

Question

SQL Server Table Types

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

i.komarovsky SSC Enthusiast Points: 171 More actions · Answer 1

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 SSChampion Points: 12968 More actions · Answer 2

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 😛

and my 1st thought "Gee, no wonder" because I couldn't figure out why 2 weeks ago on a script that uses table variable

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Michael Poppers SSCrazy Points: 2151 More actions · Answer 3

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.

sg_dbdev SSC Rookie Points: 36 More actions · Answer 4

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 (

col1INT

,col2VARCHAR(10)

,col3FLOAT

)

INSERT @vartable (col1, col2)

EXEC Proc1

SELECT * FROM @vartable

INSERT @vartable (col2, col3)

EXEC Proc1

SELECT * FROM @vartable

-- temp table example

CREATE TABLE #temptable (

col1INT

,col2VARCHAR(10)

,col3FLOAT

)

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 SSC Rookie Points: 36 More actions · Answer 5

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

Toby White SSCertifiable Points: 6187 More actions · Answer 6

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.

Alex Fekken Ten Centuries Points: 1109 More actions · Answer 7

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.

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 8

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.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 9

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.

ling_l_w SSC Enthusiast Points: 111 More actions · Answer 10

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?

Regards

LW Ling

Examples attached below:

Use TESTDB

go

Set NoCount On

select DISTINCT ACTIVITY_TYPE, RESOURCE_ACTIVITY_TYPE_ID

from

(select top 100 ACTIVITY_TYPE, RESOURCE_ACTIVITY_TYPE_ID

from [DBO].[V_ACD_SIGN_ONOFF_GRP]) as p

(This took from 1 min 17 sec to 1 min 25 sec to copmplete)

USE TESTDB

GO

SET NOCOUNT ON

Declare @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_ID

from [DBO].[V_ACD_SIGN_ONOFF_GRP]

select DISTINCT ACTIVITY_TYPE, RESOURCE_ACTIVITY_TYPE_ID

from @t

/* The above takes 4 sec to 9 sec to process 10,000 items */