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!

Answer 1

Specify Computed Columns in a Table

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);