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 3 of 3

«««1 2 3

SQL Server Table Types

Rate Topic

Display Mode

Topic Options

Author

Message

Toby White

Posted Wednesday, April 02, 2008 3:39 PM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, November 09, 2012 2:48 PM
Points: 493, Visits: 636

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.

Post #478868

Alex Fekken

Posted Wednesday, April 02, 2008 6:07 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:50 AM
Points: 195, Visits: 444

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.

Post #478913

timothyawiseman

Posted Wednesday, April 02, 2008 10:43 PM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, February 14, 2013 12:01 PM
Points: 743, Visits: 900

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/

Post #478959

Robert-378556

Posted Friday, April 04, 2008 11:10 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:40 AM
Points: 1,078, Visits: 848

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.

Post #480146

ling_l_w

Posted Wednesday, April 09, 2008 12:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 2:39 AM
Points: 7, Visits: 145

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 */

Post #482065

« Prev Topic | Next Topic »

25 posts, Page 3 of 3

«««1 2 3

Permissions