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
I am a Microsoft Most Valuable Professional for Data Platform from Germany specialized in Microsoft SQL Server performance tuning, troubleshooting and T-SQL programming. You can meet me at international and national user groups and conferences.
Related Posts
- The limitations of the missing index feature – It does not suggest filtered indexes
- The limitations of the missing index feature – It reports only include columns for some queries
- The limitations of the missing index feature – It does not specify an order for columns to be used in an index
- The limitations of the missing index feature – It does not consider trivial plans
- The limitations of the missing index feature – It cannot gather statistics for more than 500 missing index groups
inside-sqlserver is a friend of Redgate
Share the passion