Posted by Plamen Ratchev on 07/03/07 12:25

You have to create an index on the computed column, not on the expression
defining the computed column. If the computed column expression is imprecise
you can mark the computed column as PERSISTED (that will force storing the
computed value in the table).

See more details here:
http://msdn2.microsoft.com/en-us/library/ms189292.aspx

Here is one example:

CREATE TABLE Foo (
col1 INTEGER,
col2 INTEGER,
col3 INTEGER,
col4 AS col1 + col2 - col3);

CREATE INDEX ix_computed
ON Foo ( col4 );

INSERT INTO Foo (col1, col2, col3)
VALUES (2, 3, 4);

SELECT col1, col2, col3, col4
FROM Foo;

DROP TABLE Foo;

-- With imprecise computed column.
CREATE TABLE Foo (
col1 FLOAT,
col2 FLOAT,
col3 FLOAT,
col4 AS col1 + col2 - col3 PERSISTED);

CREATE INDEX ix_computed
ON Foo ( col4 );

INSERT INTO Foo (col1, col2, col3)
VALUES (2, 3, 4);

SELECT col1, col2, col3, col4
FROM Foo;

DROP TABLE Foo;


HTH,

Plamen Ratchev
http://www.SQLStudio.com

• Next in forum: [SQL Server 2005] T-SQL for configuring replication
• Prev in forum: Reporting Services
• Thread view: Re: How to use function based index in SQL Server2005?

[Reply to this message]