I have a table with a
nonclustered index on a
On that same table i have a field
(char) which is used to logically delete records and can have 2 distinct values :
There are 451047 records with the
datetime2 field set at
NULL but only 7095 are marked as
Every query in the application looks for active records only, so, every query that's looking for
NULLs in the datetime field was getting really bad estimates and, with that, bad execution plans.
It looks like i still get the old estimates, even if the query is using properly the filtered index. Does anyone know the reason of this behaviour?
CREATE TABLE [TYDATPRD].[HAND00F]( [STDRECSTS] [char](1) NULL, [HDHAND] [numeric](14, 0) IDENTITY(1,1) NOT NULL, [HDCHKINDT] [datetime2](7) NULL, --lots of other columns which I don't think are needed CONSTRAINT [PK_TYDATPRD_HAND00F] PRIMARY KEY CLUSTERED ( [HDHAND] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA] ) ON [DATA]
Nonclustered filtered index definition:
CREATE NONCLUSTERED INDEX [IX_HAND00F_HDCHKINDT] ON [TYDATPRD].[HAND00F] ( [HDCHKINDT] ASC ) WHERE [STDRECSTS]='A' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [INDEXES] GO