Function Based Index in T-SQL

,

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.

--Create the table
create table t (id int, phone nvarchar(100))
insert into t select 1, '123-456-7890 x 132'
insert into t select 2, 'my phone is 823-456-7890 ext 134'
GO

--Create a function removing all non-digit characters and then the leading "1" from a free format phone number
Alter  FUNCTION [dbo].[udfGetPhoneDigits](
@phone NVARCHAR(100)
)
RETURNS NVARCHAR(100)
WITH SCHEMABINDING
AS  BEGIN
-- Remove non-digit characters 
WHILE PATINDEX('%[^0-9]%', @phone) > 0  
SET @phone = REPLACE(@phone, SUBSTRING(@phone,PATINDEX('%[^0-9]%', @phone),1),'') 

Return right(@phone, len(@phone) - case left(@Phone,1) when '1' then 1 else 0 end)
END
GO

-- Add a computed column
Alter table t add PhoneDigits AS  dbo.udfGetPhoneDigits(phone) PERSISTED 
GO

-- Create the index
Create index idx1 on t ( PhoneDigits)
GO

-- Ready for indexed search
select * from t where phonedigits = dbo.udfGetPhoneDigits('hey, please try to find my phone #: 1 - (123)-456-7890 with extension 132')

Rate

5 (3)

Share

Share

Rate

5 (3)