This is another query optimizer conundrum.
Maybe I'm just over-estimating query optimizers, or maybe I'm missing something - so I'm putting it out there.
I have a simple table
CREATE TABLE [dbo].[MyEntities]( [Id] [uniqueidentifier] NOT NULL, [Number] [int] NOT NULL, CONSTRAINT [PK_dbo.MyEntities] PRIMARY KEY CLUSTERED ([Id]) ) CREATE NONCLUSTERED INDEX [IX_Number] ON [dbo].[MyEntities] ([Number])
with an index and some thousand rows in there,
Number being evenly distributed in the values 0, 1 and 2.
Now this query:
SELECT * FROM (SELECT [Extent1].[Number] AS [Number], CASE WHEN (0 = [Extent1].[Number]) THEN 'one' WHEN (1 = [Extent1].[Number]) THEN 'two' WHEN (2 = [Extent1].[Number]) THEN 'three' ELSE '?' END AS [Name] FROM [dbo].[MyEntities] AS [Extent1] ) P WHERE P.Number = 0;
does an index seek on
IX_Number as one would expect.
If the where clause is
WHERE P.Name = 'one';
however, it becomes a scan.
The case-clause is obviously a bijection, so in theory an optimization should be possible to deduct the first query plan from the second query.
It's also not purely academic: The query is inspired by translating enum values to their respective friendly names.
I'd like to hear from someone who know what can be expected from query optimizers (and specifically the one in Sql Server): Am I simply expecting too much?
I'm asking as I had cases before where some slight variation of a query would make an optimization suddenly come to light.
I'm using Sql Server 2016 Developer Edition.