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.
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.