Can I create index on a column with functions or analytical functions in SQL Server 2016?

Question

Can I create an Index on a column with functions or analytical functions?

I have a table ABC with columns XXX updateddatetime, YYY updatedusername. I am trying to create indices as shown below

CREATE INDEX idx_Cname1
ON ABC(UPPER(updatedusername));

CREATE INDEX idx_Cdate1
ON ABC(YEAR(updateddatetime));

I just get an error

Incorrect syntax near '('

Is it possible to create index as shown above. Also, can I create an index on columns with analytical functions like LEAD, LAG, etc.,

Thanks in advance!

You can index a [computed column](https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-2017). That's different from trying to throw in an index on an expression that isn't a column. — HABO, Jan 23 '19 at 02:22
You might [read about *indexed views*](https://aboutsqlserver.com/2011/03/24/indexed-materialized-views-in-microsoft-sql-server/) — Shnugo, Jan 23 '19 at 08:16

score 1 · Accepted Answer · answered Jan 23 '19 at 08:49

Create computed column

ALTER TABLE [t] ADD Cname1 AS ABC(UPPER(updatedusername))
ALTER TABLE [t] ADD Cdate1 AS ABC(YEAR(updateddatetime))

add PERSISTED no difference, the index still materializes the field

after create index

CREATE INDEX idx_Cname1
ON [T] (Cname1);

CREATE INDEX idx_Cdate1
ON [T] (Cdate1);

1 Answers1