Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Interesting example of statistics and index usage on a SQL Server computed column


By:   |   Read Comments (1)   |   Related Tips: More > Performance Tuning

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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.

how sql server generates a query plan based on a computed column and different methods to have it use the index

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.

a persisted column as defined by sql server books online

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.

using isnull on the persisted column caused sql server to use the statstics in a different way

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, March 22, 2011 - 9:18:16 AM - Tim Radney Back To Top

Great article.


Learn more about SQL Server tools