This is going to be unanswerable here.

You will have to try both methods on YOUR data , on YOUR system to find the answer.

I would really like to hear you're logic behind this, it sounds like bad design to me.

There is no doubt that in *some* circumstances , that 2005 is slower than 2000.

Now that is not to say that the queries in question cannot be rewritten to execute...

You are using the cte StudHistory multiple times , in this case this is hurting you.

Contrary to popular belief , cte are not spooled or cached in any way shape...

isNull is similar, but what is your issue with coalesce ?

Try this...

SELECT empid,contact,'CITY'=

CASE

WHEN contact like '9%' THEN 'Chennai'

WHEN contact like '8%' THEN 'Bangalore'

WHEN contact like '7%' THEN 'Mumbai'

WHEN contact like '6%' THEN 'Pune'

ELSE 'Oversease'

END

FROM emp;

BTW , LEFT is probably...

grahamc (11/23/2010)

...

Only ever use Dynamic SQL when you absolutely have to because dynamic SQL can't cache any execution plans for re-use later, which is a performance hit (I hope Gail...

Something like this ?

Be warned though , this is non-sargable ,and will perform badly over a large dataset.

SELECT number,count(*)

FROM custom.Routing

WHERE @var LIKE Number + '%'

group by number

order by count(*) desc

Unfortunately, for him, he doesent know sargable.

Obvious Optimization #3

get_interaction_list_employee is using a lot of dynamic with the parameters hard coded , these should be made into parametrized statements to enable plan reuse.

Also subotimal scans of

...

Obvious optimization #2

ALTER TABLE #1 ADD SamplesIssued nvarchar(600)

UPDATE #1 SET #1.[SamplesIssued] = [dbo].[SamplesByInteraction](interaction_id)

ALTER TABLE #1 ADD Products nvarchar(600)

UPDATE #1

SET #1.Products = dbo.[ProductsByInteraction](interaction_id)

Its bad practice to alter tables , this...

Obvious Optimization #1

select * into #interactions from @interactions where Year(interaction_date)=left(@period,4) and month(interaction_date)=right(@period,2)

This code should be moved into the preceeding selects and reworded to be

"Where interaction_date >= @firstdayofmonth and interaction_date...

Hmmm , possibly not the most efficient but does the job i think 😉

with table1 as

(

select dt, cat

...

Well , that was easy

http://www.sqlservercentral.com/Forums/Topic1024259-338-1.aspx

Have you tried ?