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

20 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
    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
    Check!
    (thinking…)
    Reset
    or sign in with
      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