Both options work like a charm.

Thanks again to both of you.

with cte as
(
    Select    Image, ImageDesc, Sequence, 1 as RecordType
    From    AQLFrontCartonMarksImages With (NoLock)
    Where    fKey=128
    union all
    Select    Image, ImageDesc, Sequence, 2 as RecordType
    From    AQLSideCartonMarksImages...

Thanks for the code Jacob, I will study it tomorrow. I did not know that the estimated EP was just an estimation :Whistling: and that it could be different...

I forgot to post the EP when getting the before the IF, here it is.

It is strange how SQL generate completely different plans for statements that actually do the same...

Tables InvTransactions, Inventory and StyleItem all have an index on ItemNo.

Table Style has indexes on columns Style and StyleType respectively

Code for the view

Alter View vInvAudit

as

Select it.Warehouse, si.Style, si.Label, si.Color,...

If this is what you 2 meant, it does not help, the execution plan for this also ends up with a Nest Loops in the 3rd step.

if exists (select 1...

The code for the view is in the like of

I...

I'm sure you both know much more than me about CTE's but logically the performance results should be similar for everyone so I'm thinking there must be external factors involved...

A million thanks to both of you guys. I really appreciate it and I do apologize for not properly posting my question 🙂

I ran both of them on my...

Gail,

Thanks for answering my question. Having to change exsting columns is not a big issue, I can generate a script that will do that. But will this change...

Yes of course, I forgot they were not SQL 2000 functions. I'm gonna check on my machine tomorrow, maybe I still have the 2000 code for this.

Hi Maddog.

I no longer have access to a SQL 2000 server to test but what's not working in my scrupt ? I reviewed it quickly and I think everything...

Then I guess all I have to do is go through all my procedures and get rid of those table variables if they're gonna hold more than a handful...

Gail let me put things differently then. Should I be worried about this ? I'm really not concerned about the execution time since I fixed it with temp...

Currently, a little over 10100 :Whistling:. I know it's a lot for a table variable and I should...