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