Query Optimizer Suggests Wrong Index and Query Plan — Why?

As I was preparing my presentation for the C# Corner SQL Server Virtual Conference, I came across (accidentally) a query plan that was unexpected. This plan had a suggested index that unfortunately supported that very unusual query plan. This article is not a “how you can do something better” one, but more of a starting point to discuss why the optimizer did what it did.

I look forward to your comments after reading (and maybe running the code on your computer). I’ve forward this code to Microsoft but have yet to receive acknowledgment or any discussion from them.

The Issue