Systems Engineering and RDBMS

Indexes on Computed Columns

Posted by decipherinfosys on August 28, 2007

In one of our previous blog post, we had covered computed columns in detail. Though all the three major databases Oracle, MS SQL Server and DB2 support computed columns, its implementation is different in each one of them. For details on computed columns, look at our previous blog post .

In Oracle, since the behavior of a computed column is achieved via the use of a trigger, the column does not have any limitations if we need to create an index on the computed column. But this is not the case with SQL Server. In order to create an index on a Computed column, the column value or the computed expression should be deterministic. It means that for a given input, computed column should always give the same results. If that is not the case, then computed column is considered non-deterministic and one cannot create index on computed column. Let us illustrate this with the help of an example. Connect to the database using Query Analyzer or Management Studio.

CREATE TABLE dbo.TEST
(
TEST_ID INT IDENTITY(1,1) PRIMARY KEY,
TEST_NAME VARCHAR(10),
CODE_NAME AS REVERSE(TEST_NAME),
TEST_DATE AS GETDATE()
)
GO

In the above table, we are creating two computed columns. Computed column does not contain data type, instead they are defined with ‘AS’ and then column expression. CODE_NAME will be storing reverse value of TEST_NAME column and TEST_DATE will be storing date time at the time of record creation. Using following SQL, we will check column property of all the columns.

SELECT columnproperty(object_id(‘TEST’),’CODE_NAME’,’ISDeterministic’)
UNION ALL
SELECT columnproperty(object_id(‘TEST’),’TEST_DATE’,’ISDeterministic’)
UNION ALL
SELECT columnproperty(object_id(‘TEST’),’TEST_NAME’,’ISDeterministic’)
GO

Output of above SQL will produce 3 records with value 1, 0 and NULL respectively. Value 1 indicates that column is deterministic (Value will be exactly same at any given time), 0 indicates column is non-deterministic (Value will not be same at any given point of time. NULL value indicates that column is not a computed column and hence property value cannot be determined. There is also ‘ISComputed’ column property which indicated whether column is computed column or not. So in short, ISDeterministic property returns 1/0 only for the computed column. As we mentioned earlier, index can be created on the computed column only if it is deterministic. Let us check that by creating index on the table.

CREATE INDEX TEST_IND_1 ON dbo.TEST(CODE_NAME)
GO

Since CODE_NAME is deterministic, index will be created successfully on the column. Next we will create index on TEST_DATE column.

CREATE INDEX TEST_IND_2 ON dbo.TEST(TEST_DATE)
GO

Since CODE_NAME is deterministic, index will be created successfully on the column. Next we will create index on TEST_DATE column. SQL Server will not allow creation of index and will throw following error.

Msg 2729, Level 16, State 1, Line 1
Column ‘TEST_DATE’ in table ‘dbo.TEST’ cannot be used in an index or statistics or as a partition key because it is non-deterministic.

Index on computed column can be real performance enhancer. If query has filter criteria which requires certain function to be applied on the column in the filter criteria, index will not be used even if there is an index on the column in question. It is better to create an extra computed column on the table, create an index on the column and then use computed column in filter criteria. That way proper index will be used for retrieval of the data. Index on computed column is similar to function based index or FBI in Oracle. To learn more about FBI, visit our previous blog post.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: