SQL Server: Is it possible to create a calculated index

In SQL Server, is it possible to create an index on a calculation?

The following does not work for me:

CREATE INDEX customermonth ON customers(month(dob));

Some databases allow me to do this. Is there a trick to doing this in SQL Server?

Yes it is possible to do this, but not with the syntax you used. The key is that you need to create a computed column in the table and then index that:

ALTER TABLE customers ADD MonthOfBirth AS MONTH(dob) PERSISTED;
CREATE INDEX customermonth ON customers (MonthOfBirth);

Computed column is SQL Server's name for what other products call a generated column, and in place of STORED (cf. VIRTUAL) we use PERSISTED.

See How to create indexes on computed columns in SQL Server by Atif Shehzad.

Naturally, this requires that you have permission to alter the schema of the table, and you'll need to ensure that there won't be any nasty side effects of adding a column (like breaking reports that use SELECT * FROM customers;)

Computed columns do not always have to be PERSISTED before you create an index on them. See the documentation on the topic.

StackExchange Database Administrators Q#168351, answer score: 13