Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Filtered index not used when IS NULL and key lookup with no output

Filtered index not used when IS NULL is used. IS NOT NULL works fine, but IS NULL does a key lookup. Same as this issue but on different version of SQL Server
https://connect.microsoft.com/SQLServer/feedback/details/454744/filtered-index-not-used-and-key-lookup-with-no-output

21 votes
Sign in
(thinking…)
Password icon
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

1 comment

Sign in
(thinking…)
Password icon
Signed in as (Sign out)
Submitting...
  • David Browne commented  ·   ·  Flag as inappropriate

    Here's a repro (since the linked connect item is gone):

    drop table if exists mytab
    CREATE TABLE MyTab (
    ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY
    ,GroupByColumn NVARCHAR(10) NOT NULL
    ,WhereColumn int NULL
    )

    CREATE NONCLUSTERED INDEX IX_MyTab_GroupByColumn ON MyTab
    (GroupByColumn) --include (WhereColumn)
    WHERE (WhereColumn IS NULL)

    SELECT
    GroupByColumn
    ,COUNT(*)
    FROM MyTab
    WHERE WhereColumn IS NULL
    GROUP BY GroupByColumn

Feedback and Knowledge Base