Function Based Index in T-SQL

By duanxn, 2007/04/10

To build function based index in SQL Server, you can use indexed view (materialized view) or using a computed column.

In this script, I give an exmaple of how to use the computed column to implement a function based index on a table to search through a free formated telephone number column. The basic step is:

1) Create the UDF you want to index on. For a function to be indexable, it must be deterministic.

2) Add a persisted computed column to the table, in context of the function.

3) Create the index on the computed column.

Total article views: 2070 | Views in the last 30 days: 12

FORUM

Computed Column

FORUM

Computed Columns

ARTICLE

Complex Computed Columns

Computed columns in SQL Server 2000 allow you to have a calculated value available easily in a query...

FORUM

Computed column error

FORUM

Comparing Computed columns

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Function Based Index in T-SQL

Computed Column

Computed Columns

Complex Computed Columns

Computed column error

Comparing Computed columns

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

You make SSC a better place

Already a member? Jump in: