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


SQL Server Table Types


SQL Server Table Types

Author
Message
Toby White
Toby White
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 639
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
Alex Fekken
Old Hand
Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)

Group: General Forum Members
Points: 381 Visits: 460
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
timothyawiseman
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2382 Visits: 920
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
Robert-378556
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2346 Visits: 1010
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
ling_l_w
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 159
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 */
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