Yeah...

It sounds like you have a pretty good handle on a complex situation. Kudos for that honestly.

As Drew mentioned SORTS are painful (despite getting you a MERGE JOIN apparently) so...

I'd really prefer not to have to do a nightly rebuild on every index - don't think I have enough time to complete that nightly anyway.

Can I just do a...

paul.biltcliffe 32759 (12/5/2016)

---

In regards nchar and nvarchar, its purely down to the 3rd party design for this.

There's a lot of this throughout their table designs, however they have very little...

Recurs1on (12/5/2016)

---

The understand that I could replace the variable, but the reason it was there in the first place was because there were multiple tables of relatively the same size...

krypto69 (12/5/2016)

---

Hi,

Long story but I have a production DB that has a ton of old archive records (staging records). I wrote an SSIS package that copies all the old archive...

I don't think I said it would be better for the OP's scenario (for which we know too little). But he/she is learning and having another tool in the TSQL...

That looks workable. There is a solution with CROSS APPLY too. Look up Itzik Ben-Gan TOP rows per group.

1) What does the query plan look like? Post up estimated query plan for review.

2) Table structures? Post those up too, including indexing on them.

3) Do you have any mismatched...

I would just hard-code the value I want to delete. Or use OPTION (RECOMPILE) if you do this with a variable.

I have to take exception with one thing you said Igor:

EF, as in Entity Framework, has been involved in...

So it doesn't look like you have mismatched data types. That is actually a shame, because it could have been a magic-bullet fix. 🙁

What are the data types of the columns a.client, a.status, and a.attribute_id??

Don't feel bad. I have been consulting on SQL Server for 20+ years now...

Michael L John (12/2/2016)

---

Good info. Thanks!

One of the things we did was to reach out to current users of the product. The reviews and recommendations from them couldn't have...