That's really a shame, because there are some indexes that are completely obsolete and do nothing except...

Loner (2/19/2016)

---

It is not I don't want to provide the information, but I can't post my company info on the website. I am trying to provide the info with a...

Would this be what you are looking for?

SELECT o.OrderId,

MAX(CASE WHEN od.Category = 'CATE-1'...

I suggest that you set up a trace/extended events session, or add a (temporary) trigger to the table to track deletes (but note that a trigger will not catch TRUNCATE...

You can probably do this by not using PIVOT but some other pivoting technique,

Please post the tables and sample data as CREATE TABLE and INSERT statements, so that I can...

What Gail says. Filtered indexes only allow AND and IN. OR is not allowed. Neither is NOT, which would otherwise have been a nice workaround.

An OR list of distinct values...

Yes, that's what happens when you insert data in a table. You should also delete...

Please post the *exact* code. With the "something like this" code, I was unable to reproduce the issue.

Loner: It's a long topic already so I may have overlooked something. Did you already try the OPTION (RECOMPILE) hint that Scott suggested? Did it help?

Without knowing the sourcecode of the FileExists user-defined function, there is no way for us to tell you what's going on.

However, if it works for you without the subquery then...

This is called a problem in the "gaps and islands" category (islands in this case). Itzik Ben-Gan has come up with some very creative and smart solutions for problems in...

Jeff Moden (2/18/2016)

---

Ed Wagner (2/18/2016)

---

I think Hugo hit the nail on the head. I can't believe those questions were even in an interview. There are a number of...

Please post your table definitions in the form of CREATE TABLE statements, and your sample data in the form of INSERT statements.

Then also post the exact output you want returned...

To reiterate what Steve already said: your problem is not clear. Please post it in a language that we all understand on this forum: in the form of CREATE TABLE...

Thanks for posting a nice execution plan to sink my teeth into! 😉

Just a few loose thoughts, in random order.

* I see estimates that are off, but not way off....