An index based on a scalar function applied to a column

Question

I have an user scalar-valued function which takes NVARCHAR(MAX) and returns NVARCHAR(MAX). To increase perfomance of GROUP BY command utilizing this function I wanted to create an index also based on it. But documentation says what CREATE INDEX command only takes columns not expressions, so this is illegal:

CREATE INDEX an_index ON a_table (foo(a_column))

Are any workarounds?

Thank you!

Answer 1

You'll need to create a computed column:

CREATE TABLE a_table (a_column NVARCHAR(MAX), a_foo AS foo(a_column))
CREATE INDEX an_index ON a_table (a_foo)

In order to be indexable, the function foo must, of course, meet certain demands:

Indexing Computed Columns That Invoke a User-Defined Function

A computed column that invokes a user-defined function can be used in an index when the user-defined function has the following property values:

• IsDeterministic = true
• IsSystemVerified = true (unless the computed column is persisted)
• UserDataAccess = false
• SystemDataAccess = false

You cannot build an index on a non-deterministic function like GETDATE that changes its return value every time you call it.

It will be even easier to solve your case if you post the function you want to index on.