This will be my last blog about the limitations of the missing index feature. This time I will demonstrate that the missing index feature will not suggest filtered indexes. Filtered indexes reduce the index size and thus the storage allocation in the data file. Rebuilding or reorganizing these indexes also requires fewer resources. So it’s worth creating filtered indexes when meaningful.
The following example will prove that the missing index feature will not consider creating filtered indexes.
/**************************************************************************

missing index feature - limitations
It does not suggest filtered indexes

**************************************************************************/

DROP TABLE IF EXISTS [dbo].[missingIndex];
GO

-- Create a table
CREATE TABLE [missingIndex]
(
	[col1] int NOT NULL
  , [col2] int NULL
);
GO
;

-- Insert 100.000 test records into the table
WITH
	[cte] AS
	(
		SELECT [numbers].[col1]
		FROM
			(
				VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
			) AS [numbers] ([col1])
	)
INSERT [dbo].[missingIndex]
	(
		[col1]
	  , [col2]
	)
SELECT
	[c1].[col1]
  , CASE WHEN [c1].[col1] < 9 THEN NULL ELSE [c1].[col1] END AS [col2]
FROM
	[cte] AS [c1]
CROSS JOIN [cte] AS [c2]
CROSS JOIN [cte] AS [c3]
CROSS JOIN [cte] AS [c4]
CROSS JOIN [cte] AS [c5];
GO
-- Uneven distribution 
-- 90,000 NULL 
-- 10,000 9 
SELECT
	COUNT(*), [col2]
FROM
	[dbo].[missingIndex]
GROUP BY
	[col2];
GO
-- Index recommendation from missing index dmv 
-- Costs : 0,268838 
-- Table 'missingIndex'. Scan count 1, logical reads 211 
-- The Query Processor estimates that implementing the following index could 
-- improve the query cost by 73.2966%. 
SELECT *
FROM
	[dbo].[missingIndex]
WHERE
	[col2] = 9
OPTION (QU