In a schema, an int is used as a bitfield, one of those bits is used in a frequently called SQL query (a bit marking entries to be new for processing).
The query (entity framework generated, got it from listening in/querying execution plan cache) is
SELECT TOP (1000) [Extent1].[Id] AS [Id], [Extent1].[Created] AS [Created], [Extent1].[DataTypeId] AS [DataTypeId], [Extent1].[CodePage] AS [CodePage], [Extent1].[InMsgStateFlags] AS [InMsgStateFlags], [Extent1].[ContentData] AS [ContentData], [Extent1].[ContentHash] AS [ContentHash] FROM [dbo].[tInput] AS [Extent1] WHERE (([Extent1].[InMsgStateFlags]) & (@p__linq__0)) = @p__linq__1
Only relevant values in reality are 1 for both parameters (though others would be possible).
I tried
-- add computed column alter table dbo.tInput add InMsgStateFlagNew as (InMsgStateFlags & 1) persisted -- add index, filtered index cannot be created on computed column.. CREATE INDEX IX_InMsgStateFlag_New ON tInput (InMsgStateFlagNew); -- where InMsgStateFlagNew = 1;
Sadly, SQL Server (2008 R2) does not automatically recognize this and still scans the index (there is a “normal” index on InMsgStateFlags). Is there any way to make the C#-generated code (well, the statement given above) recognize the index without having to rewrite the C# code?
(Note: I would for the rewrite-case directly query the computed column, probably making it a bit type, and leaving the generic functionality in place and handling the fact that both values are 1 in the special case of going to the computed column. If possible, I would like to avoid that, in order not to introduce breaking changes between code and DB schema.)