How to use function based index in SQL Server2005?

How to use function based index in SQL Server2005?

am 03.07.2007 08:05:46 von Steven

Don Burleson said in SQL Server2005, several new features are
introduced include function based index.( Specialised index on a
computed column (ala Function based Index))

But when I refered MSDN, I could't find this new function.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON ( column [ ASC | DESC ] [ ,...n ] )
[ INCLUDE ( column_name [ ,...n ] ) ]
[ WITH ( [ ,...n ] ) ]
[ ON { partition_scheme_name ( column_name )
| filegroup_name
| default
}
]
[ ; ]

What should I if I want to create index like this:
CREATE INDEX IDX1 ON T1(COL1+COL2-COL3)

Re: How to use function based index in SQL Server2005?

am 03.07.2007 14:25:55 von Plamen Ratchev

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