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
-- Insert 100.000 records
VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
) AS numbers (col1)
INSERT dbo.missingIndex (col1, col2)
c1.col1 AS col1, c1.col1 AS col2
cte AS c1
CROSS JOIN cte AS c2
CROSS JOIN cte AS c3
CROSS JOIN cte AS c4
CROSS JOIN cte AS c5;
We can evaluate that the column col2 in the table is imprecise, deterministic, computed but not persisted by running the following statements.