I've reread the questions now 3 times and still can't figure them out without some expected output.

Nice of you to post DDL though Sean.

Also, one other:

AND ISNULL(l.ExcludeFlag, '0') = '0'

Can you set a default value for ExcludeFlag, run a script to set all NULL values to '0' and then...

I'll make two other suggestions then, but for the first I must ask a question:

AND l.ARTxnBusinessDate <= '1/1/2011'--@DateFrom

1. Are you actualy comparing against a...

This seems a bit more succinct:

SELECT productlineid, NumberofProductsOutofStock=COUNT(productlineid)

FROM @Products

GROUP BY productlineid

HAVING COUNT(CASE Status WHEN 2 THEN 1 ELSE NULL END) = COUNT(productlineid)

For what it's worth, here's my opinion on Tuning Advisor.

If you put sugar on dog food it may taste better but do you really want to eat it?

The meaning of...

I may understand what you're looking for.

Have you considered creating a table with 6 columns:

- Server name, database name, table name (for the master table)

- Server name, database name, table...

Lynn Pettis (8/7/2012)

---

I don't know, but I'm really getting tired of questions wanting answers but not getting enough information to for me to even start to give a decent answer....

mtassin (8/7/2012)

---

MyDoggieJessie (8/7/2012)

---

All jokes aside 😉 when removing the index hint the optimizer automatically turns it into an index scan...so that's why I was trying to force it to use...

The 2nd to last query in the sequence illustrates what I meant by the edit in my prior post.

--===== Conditionally drop the test table to make reruns in SSMS easier.

...

SomewhereSomehow (8/7/2012)

---

WoW! When I asked for testing, I didn't think that it will turn into a kind of contest =)

Here is one more for uint.

PRINT '========== ??? ========================'

SET STATISTICS TIME...

Personally I wouldn't use Dynamic SQL for this.

My suggestion is to pass the two lists of values (@CriteriaA and @CriteriaB) as delimited strings and use a string splitter like Jeff...

I can't help you PROBE your BITMAP (sorry but I couldn't resist ;-)), but my first suggestion would be to remove the INDEX hint and see what that does to...

Do you mean like this?

UPDATE a

SET a.active = 0

FROM sys_scd_profile a

INNER JOIN sys_scd_profile_activation b

ON a.id = b.profile_id

WHERE a.active = 1 and b.date_deactivated is not...

What? You mean you read it too, Vinu?

An interesting use case just appeared here: http://www.sqlservercentral.com/Forums/Topic1339633-392-3.aspx#bm1340977

The discussion thread also sheds further light on its performance, just in case you didn't...

vinu512 (8/6/2012)

---

dwain.c (8/6/2012)

---

SomewhereSomehow (8/3/2012)

---

When you asking about alternate solution you, do mean funtctional equivalent? you may use case+group by instead of pivot, and cross apply instead of unpivot. You may...