Why filtered index on IS NULL value is not used?

Clash Royale CLAN TAG#URR8PPP

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
7
down vote

favorite

Assume we have a table definition like this:

CREATE TABLE MyTab (
 ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY
 ,GroupByColumn NVARCHAR(10) NOT NULL
 ,WhereColumn DATETIME NULL
 )

And a filtered non-clustered index like this:

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

Why this index is not "covering" for this query:

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

I'm getting this execution plan:

The KeyLookup is for the WhereColumn IS NULL predicated.

Here is the plan: https://www.brentozar.com/pastetheplan/?id=SJcbLHxO7

sql-server query-performance index-tuning

share|improve this question

edited Sep 7 at 18:40

asked Sep 7 at 18:13

jerik1

1127

add a comment | 

up vote
7
down vote

favorite

Assume we have a table definition like this:

CREATE TABLE MyTab (
 ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY
 ,GroupByColumn NVARCHAR(10) NOT NULL
 ,WhereColumn DATETIME NULL
 )

And a filtered non-clustered index like this:

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

Why this index is not "covering" for this query:

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

I'm getting this execution plan:

The KeyLookup is for the WhereColumn IS NULL predicated.

Here is the plan: https://www.brentozar.com/pastetheplan/?id=SJcbLHxO7

sql-server query-performance index-tuning

share|improve this question

edited Sep 7 at 18:40

asked Sep 7 at 18:13

jerik1

1127

add a comment | 

up vote
7
down vote

favorite

up vote
7
down vote

favorite

Assume we have a table definition like this:

CREATE TABLE MyTab (
 ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY
 ,GroupByColumn NVARCHAR(10) NOT NULL
 ,WhereColumn DATETIME NULL
 )

And a filtered non-clustered index like this:

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

Why this index is not "covering" for this query:

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

I'm getting this execution plan:

The KeyLookup is for the WhereColumn IS NULL predicated.

Here is the plan: https://www.brentozar.com/pastetheplan/?id=SJcbLHxO7

sql-server query-performance index-tuning

share|improve this question

edited Sep 7 at 18:40

asked Sep 7 at 18:13

jerik1

1127

Assume we have a table definition like this:

CREATE TABLE MyTab (
 ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY
 ,GroupByColumn NVARCHAR(10) NOT NULL
 ,WhereColumn DATETIME NULL
 )

And a filtered non-clustered index like this:

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

Why this index is not "covering" for this query:

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

I'm getting this execution plan:

The KeyLookup is for the WhereColumn IS NULL predicated.

Here is the plan: https://www.brentozar.com/pastetheplan/?id=SJcbLHxO7

sql-server query-performance index-tuning

share|improve this question

edited Sep 7 at 18:40

asked Sep 7 at 18:13

jerik1

1127

share|improve this question

share|improve this question

edited Sep 7 at 18:40

edited Sep 7 at 18:40

edited Sep 7 at 18:40

asked Sep 7 at 18:13

jerik1

1127

asked Sep 7 at 18:13

jerik1

1127

asked Sep 7 at 18:13

jerik1

1127

1127

add a comment | 

add a comment | 

1 Answer
1

active

oldest

votes

up vote
13
down vote



accepted

Why this index is not "covering" for this query:

No good reason. That is a covering index for that query.

Please vote for the feeback item here: https://feedback.azure.com/forums/908035-sql-server/suggestions/32896348-filtered-index-not-used-when-is-null-and-key-looku

And as a workaround include the WhereColumn in the filtered index:

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

share|improve this answer

edited Sep 7 at 20:09

answered Sep 7 at 18:31

David Browne - Microsoft

8,194622

• 
  
  
  

  
  10
  

  
  
  
  
  
  

  
  This was reported over a decade ago by Gail Shaw. Then Connect died. Closest I can find now is feedback.azure.com/forums/908035-sql-server/suggestions/…
  – Paul White♦
  Sep 7 at 18:53
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f217046%2fwhy-filtered-index-on-is-null-value-is-not-used%23new-answer', 'question_page');

);

Post as a guest

Name Email

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
13
down vote



accepted

Why this index is not "covering" for this query:

No good reason. That is a covering index for that query.

Please vote for the feeback item here: https://feedback.azure.com/forums/908035-sql-server/suggestions/32896348-filtered-index-not-used-when-is-null-and-key-looku

And as a workaround include the WhereColumn in the filtered index:

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

share|improve this answer

edited Sep 7 at 20:09

answered Sep 7 at 18:31

David Browne - Microsoft

8,194622

• 
  
  
  

  
  10
  

  
  
  
  
  
  

  
  This was reported over a decade ago by Gail Shaw. Then Connect died. Closest I can find now is feedback.azure.com/forums/908035-sql-server/suggestions/…
  – Paul White♦
  Sep 7 at 18:53
  
  

  
  
  
  

add a comment | 

up vote
13
down vote



accepted

Why this index is not "covering" for this query:

No good reason. That is a covering index for that query.

Please vote for the feeback item here: https://feedback.azure.com/forums/908035-sql-server/suggestions/32896348-filtered-index-not-used-when-is-null-and-key-looku

And as a workaround include the WhereColumn in the filtered index:

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

share|improve this answer

edited Sep 7 at 20:09

answered Sep 7 at 18:31

David Browne - Microsoft

8,194622

• 
  
  
  

  
  10
  

  
  
  
  
  
  

  
  This was reported over a decade ago by Gail Shaw. Then Connect died. Closest I can find now is feedback.azure.com/forums/908035-sql-server/suggestions/…
  – Paul White♦
  Sep 7 at 18:53
  
  

  
  
  
  

add a comment | 

up vote
13
down vote



accepted

up vote
13
down vote



accepted

Why this index is not "covering" for this query:

No good reason. That is a covering index for that query.

Please vote for the feeback item here: https://feedback.azure.com/forums/908035-sql-server/suggestions/32896348-filtered-index-not-used-when-is-null-and-key-looku

And as a workaround include the WhereColumn in the filtered index:

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

share|improve this answer

edited Sep 7 at 20:09

answered Sep 7 at 18:31

David Browne - Microsoft

8,194622

Why this index is not "covering" for this query:

No good reason. That is a covering index for that query.

Please vote for the feeback item here: https://feedback.azure.com/forums/908035-sql-server/suggestions/32896348-filtered-index-not-used-when-is-null-and-key-looku

And as a workaround include the WhereColumn in the filtered index:

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

share|improve this answer

edited Sep 7 at 20:09

answered Sep 7 at 18:31

David Browne - Microsoft

8,194622

share|improve this answer

share|improve this answer

edited Sep 7 at 20:09

edited Sep 7 at 20:09

edited Sep 7 at 20:09

answered Sep 7 at 18:31

David Browne - Microsoft

8,194622

answered Sep 7 at 18:31

David Browne - Microsoft

8,194622

answered Sep 7 at 18:31

David Browne - Microsoft

8,194622

8,194622

• 
  
  
  

  
  10
  

  
  
  
  
  
  

  
  This was reported over a decade ago by Gail Shaw. Then Connect died. Closest I can find now is feedback.azure.com/forums/908035-sql-server/suggestions/…
  – Paul White♦
  Sep 7 at 18:53
  
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  10
  

  
  
  
  
  
  

  
  This was reported over a decade ago by Gail Shaw. Then Connect died. Closest I can find now is feedback.azure.com/forums/908035-sql-server/suggestions/…
  – Paul White♦
  Sep 7 at 18:53
  
  

  
  
  
  

10

10

This was reported over a decade ago by Gail Shaw. Then Connect died. Closest I can find now is feedback.azure.com/forums/908035-sql-server/suggestions/…
– Paul White♦
Sep 7 at 18:53

This was reported over a decade ago by Gail Shaw. Then Connect died. Closest I can find now is feedback.azure.com/forums/908035-sql-server/suggestions/…
– Paul White♦
Sep 7 at 18:53

add a comment | 

 

draft saved

draft discarded

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f217046%2fwhy-filtered-index-on-is-null-value-is-not-used%23new-answer', 'question_page');

);

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Name Email

Name

Email

Name Email

Name

Email