By: Matteo Lorini | Comments (1) | Related: > Performance Tuning
Problem
Why index and statistics are not always used on a computed column. In this tip we look at how SQL Server generates a query plan based on a computed column and different methods to have it use the index.
Solution
The creation of an index on computed columns usually offers the following benefits:
- A better cardinality estimate
- The query can use the index for seeks or scans
The following example highlights a problem when ISNULL is used in a computed column and how to fix it for performance.
Let's create the following table and fill it with 30,000 rows.
CREATE TABLE test (col1 NVARCHAR(20) NOT NULL, col2 NVARCHAR(20) NULL, col3 NVARCHAR(20))
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0
BEGIN TRAN
WHILE @i < 300000
BEGIN
DECLARE @ch NVARCHAR(MAX) = CAST(@i AS NVARCHAR(MAX))
INSERT INTO test (col1, col2) VALUES (@ch, 'This is a test')
SET @i += 1
END
COMMIT TRAN
Turn on Include Acutal Execution Plan and run the following query.
SELECT ISNULL(col1,'0') + ISNULL (col2, '0') AS myString, COUNT (*)
FROM test
GROUP BY ISNULL(col1,'0') + ISNULL (col2, '0') This is the query plan that we get. We can see the Estimated Number of Rows is 547 which is not correct since the query returns 30,000 rows. This is because there is no statistics (because it is a dynamically computed column) and the estimated number of rows is much lower than the actual rows.
Let's create a new column which is a computed column and add a new index on table test and run the same SELECT query again.
ALTER TABLE test ADD myString AS ISNULL(col1,'0') + ISNULL (col2, '0') persisted
GO
CREATE INDEX IDX_myString ON test (myString)
GO
SELECT ISNULL(col1,'0') + ISNULL (col2, '0') AS myString, COUNT (*)
FROM test
GROUP BY ISNULL(col1,'0') + ISNULL (col2, '0') A persisted column is defined as: "Specifies that the PERSISTED property is added to or dropped from the specified column. The column must be a computed column that is defined with a deterministic expression. For columns specified as PERSISTED, the Database Engine physically stores the computed values in the table and updates the values when any other columns on which the computed column depends are updated. By marking a computed column as PERSISTED, you can create indexes on computed columns defined on expressions that are deterministic, but not precise." (SQL Server Books Online)
When we run the above code, we can see that the new index was not used and the Estimated Number of Rows is still 547.
If we take a close look at the computed expression we can see we are using ISNULL, but since "col1" is non-nullable we do not need to use ISNULL. So, let's drop the previous column and index and re-create them as:
DROP INDEX test.IDX_myString
GO
ALTER TABLE test DROP COLUMN myString
GO
ALTER TABLE test ADD myString1 AS col1 + ISNULL (col2, '0') persisted
GO
CREATE INDEX IDX_myString1 ON test (myString1)
GO Let's run the exact same SELECT query again.
SELECT ISNULL(col1,'0') + ISNULL (col2, '0') AS myString, COUNT (*)
FROM test
GROUP BY ISNULL(col1,'0') + ISNULL (col2, '0')
GO This time, we can see that the newly created index IDX_myString1 was used and the Estimated Number of Rows now reflects the query.
As you can see using ISNULL on the persisted column caused SQL Server to using the statstics in a different way versus when ISNULL was not used on the first part of the computed column.
Next Steps
- Read these related tips:
About the author
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
