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 3:39 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 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
Posted Wednesday, April 2, 2008 6:07 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, December 15, 2014 3:15 PM
Points: 197, 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.
Post #478913
Posted Wednesday, April 2, 2008 10:43 PM


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: Monday, November 24, 2014 4:29 PM
Points: 752, 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/
Post #478959
Posted Friday, April 4, 2008 11:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:37 AM
Points: 1,275, Visits: 932
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
Posted Wednesday, April 9, 2008 12:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 11:44 AM
Points: 7, Visits: 151
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 »

Add to briefcase «««123

Permissions Expand / Collapse