The limitations of the missing index feature – It suggests creating indexes on imprecise and not persisted columns

This will be my penultimate blog about the limitations of the missing index feature and this one will be short.
Before we start, let us clarify some terms so you can better understand the table we are going to create.

The data types real and float are imprecise data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

Non deterministic columns are computed columns which may return different results each time they are called.

A computed column is a virtual column that is not physically stored in the table, unless the column is marked persisted.

Let us create a table with a imprecise deterministic non persisted computed column and insert 100,000 records.

-- Create a table with a deterministic but non precise computed column
CREATE TABLE dbo.missingIndex
(
	col1 int NOT NULL
  , col2 AS CONVERT(float, 1.25) * col1
);
GO

-- Insert 100.000 records
;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 AS col1, c1.col1 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

We can evaluate that the column col2 in the table is imprecise, deterministic, computed but not persisted by running the following statements.

-- Check if the column is precise
-- IsPrecise : 0
SELECT
	COLUMNPROPERTY(OBJECT_ID('missingIndex'), 'col2', 'IsPrecise') AS IsPrecise;
GO

-- Check if the column is deterministic
-- IsDeterministic : 1
SELECT
	COLUMNPROPERTY(OBJECT_ID('missingIndex'), 'col2', 'IsDeterministic') AS IsDeterministic;
GO

-- Check if the column is computed
-- IsComputed : 1
SELECT
	COLUMNPROPERTY(OBJECT_ID('missingIndex'), 'col2', 'IsComputed') AS IsComputed;
GO

-- Check if the column is persisted
-- IsPersisted : 0
SELECT
	tab.name AS table_name
  , ccol.name AS column_name
  , ccol.is_persisted AS IsPersisted
FROM
	sys.tables AS tab
INNER JOIN
	sys.computed_columns AS ccol
ON
	ccol.object_id = tab.object_id
WHERE
	tab.object_id = OBJECT_ID('missingIndex', 'U');
GO

Now let us execute the following query:

-- Costs : 0,419689
-- Table 'missingIndex'. Scan count 1, logical reads 335
-- The Query Processor detects a missing index which could improve the query 
-- cost by 95.3725%.
SELECT col2
FROM
	dbo.missingIndex
WHERE
	col2 = 3.75
	AND (SELECT 1) = 1;
GO

As we can see in the query execution plan, the missing index function suggests implementing an index to improve the query cost by 95.9647%. The implementation of the index would avoid scanning the entire table with 335 pages.

That sounds like a good suggestion, so let’s create the recommended index.

-- Create the recommended non clustered index
-- Cannot create index because the computed column 'col2' is imprecise and not
-- persisted
CREATE NONCLUSTERED INDEX test
ON dbo.missingIndex (col2);
GO

The missing index feature suggest creating an index on a non precise and non persisted column which causes the following error:
Msg 2799, Level 16, State 1, Line 867
Cannot create index or statistics ” on table ‘dbo.missingIndex’ because the computed column ‘col2’ is imprecise and not persisted. Consider removing column from index or statistics key or marking computed column persisted.

To fix this, we can follow the recommendation and make column col2 persisted, but I wonder why SQL Server suggests an index that can not be created because of the above limitations – I think that’s a bug.

Nevertheless, let us physically store the computed column:

-- Make col2 persisted
ALTER TABLE missingIndex ALTER COLUMN col2 ADD PERSISTED;
GO

-- Check if the column is persisted
-- IsPersisted : 1
SELECT
	tab.name AS table_name
  , ccol.name AS column_name
  , ccol.is_persisted AS IsPersisted
FROM
	sys.tables AS tab
INNER JOIN
	sys.computed_columns AS ccol
ON
	ccol.object_id = tab.object_id
WHERE
	tab.object_id = OBJECT_ID('missingIndex', 'U');
GO

-- Create the recommended index
CREATE NONCLUSTERED INDEX test
ON dbo.missingIndex (col2);
GO

If we now run the query again we will see that the query engine uses an index seek to get the data which reduces the logical page reads and the costs.

-- Index Seek 
-- Costs : 0,0351339 (0,419689)
-- Table 'missingIndex'. Scan count 1, logical reads 32 (335)
SELECT col2
FROM
	dbo.missingIndex
WHERE
	col2 = 3.75
	AND (SELECT 1) = 1;
GO

-- Housekeeping
DROP TABLE IF EXISTS dbo.missingIndex;
GO