IS NULL Filtered indexes and column choice

Question

some filtered indexes have been created on one of our databases that filter on [EndDATE] IS NULL, so only records with a null enddate field appear in the index, which is good.

The enddate field is then added as the first column of the key columns, followed by the other columns to order on. I can't work out of this is a bad thing to do. All the results of the first column will be Null so, will it have any real world impact?

According to MS you should put a IS NULL filtered field in the included columns or the Where clause, for the DB engine to use it, but does adding it as the first key column have any impact?

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/filtered-index-with-column-is-null

Answer 1

I wouldn't put it in the key columns at all and certainly not as the first column.

It is annoying that it needs to be include-d in the index at all when it is guaranteed by the filtered index predicate (but this is a necessary evil to avoid redundant lookups).

From a practical point of view it may not cause any adverse effects though apart from mildly less rows per page on the index non leaf pages.

If you have an index on (A,B,C) WHERE A IS NULL then the query will need a WHERE A IS NULL predicate to match the filtered index anyway and I would expect that the optimiser would recognize this equality in most/all cases and that the matching rows will then be ordered by B,C.

You are in the best position to see if there is any "real world impact" from this practice as you have a real world system doing it. Have you seen any sub optimal execution plans caused by this practice?

Answer 2

As others have noted, the effect on the actual index is minimal. Only the index pages themselves (not the leaf pages) could need extra pages to store the column, and in the case of a simple IS NULL predicate it's unlikely to take up any space anyway.

One thing missing in the other answers: the statistics will be mostly useless. The histogram in SQL Server is only single-column, so you end up with a single row of only null values.

You can see this in action with the following script

CREATE TABLE t (
  id int IDENTITY PRIMARY KEY,
  x int NOT NULL,
  y int,
  INDEX IX (x) INCLUDE (y) WHERE (y IS NULL));

INSERT t (x, y)
SELECT x.value, NULLIF(y.value, 1)
FROM GENERATE_SERIES(1, 100) x
CROSS JOIN GENERATE_SERIES(1, 3) y;

UPDATE STATISTICS t WITH FULLSCAN;

DBCC SHOW_STATISTICS ( N't', N'IX');

CREATE INDEX IX ON t (y, x) WHERE (y IS NULL) WITH (DROP_EXISTING = ON);
UPDATE STATISTICS t WITH FULLSCAN;

DBCC SHOW_STATISTICS ( N't', N'IX');

db<>fiddle

Answer 3

Theoretically speaking, this will have minimal impact if any on the performance. However, you can check on the effects as this is already running on your system. Additionally, I wouldn’t recommend having the column as the first in the key columns or among them, as this column’s value (NULL) has no variability and thus won’t provide SQL Server any additional benefits concerning index ordering and selectivity.

This is why Microsoft suggests having the filtered column as an included column or in the WHERE clause. Also, this will help in storage as the NULL values will consume storage when storing the key columns in the index structure.