Are 'Functional Indexes' Supported in MS SQL Server?

Question

I've just come across expression based indexes (aka "functional indexes") when reading through the documentation for PostgreSQL (I wasn't aware of such indexes previously).

I'm just wondering if there is anything that is similar/equivalent to this in Microsoft SQL Server? I have read the SQL Server documentation on indexing and I don't see anything that is obviously similar/equivalent.

Answer 1

While there isn't a feature of Functional Indexes in SQL Server per se, one can mimic a similar implementation via indexing on top of a Computed Column. Computed columns allow functions to be utilized. They also can even be persisted such that the result of a function is stored in the column on disk (as opposed to being evaluated every time at query runtime).

So long as the requirements of the document I linked above are met, it's possible to index on top of a function result similar to Functional Indexes from PostgreSQL. One of the most important requirements is that the function is a deterministic function, meaning it outputs the same result for the same input always. For example, ISNULL() is deterministic because the same input parameter will always return the same outputted result. But the GETDATE() function is nondeterministic because the same input (which happens to be no input for this particular function) always results in a different outputted result, one day to the next, when it's called.

Answer 2

The closest equivalent is that you can create a computed column and then index that column.

Computed column matching is a bit hit and miss but generally SQL Server will match the function call to the matching index.

CREATE TABLE #Demo
(
Foo VARCHAR(50)
)

ALTER TABLE #Demo ADD LowerFoo AS lower(Foo)

CREATE INDEX IX ON #Demo(LowerFoo)

SELECT COUNT(*)
FROM #Demo 
WHERE lower(Foo) = 'aa'

Answer 3

In addition to indexes on computed columns, you sometimes use Indexed Views instead. Computed columns change the logical design of the table, and sometimes that's not desirable or possible.

Answer 4

The main difference between PostGreSQL and MS SQL Server is that MS SQL Server supports functional indexes since almost 21 years through the concept of indexed computed columns....

But in addition, SQL Server publish the value of the column in the table definition.

And now you have fours ways of creating such a fonctionnality :

1. Computed columns without any storage called unpersisted computed columns
2. Computed columns with storage called persisted computed columns
3. Computed columns with index storage only called indexed computed columns
4. Computed columns with value and index storage called persisted computed columns

PostGreSQL does not have such equivalent and only give the possibility to create indexed computed columns without any way to view the value in a column...

By the way, computed columns without any storage that are called unpersisted computed columns is a functionnal equivalent to what you can do with a view.

The only difference is that you must have the computed column into the table in MS SQL Server... that PG do not have !