Sql Calculated Column (1 Viewer)

[waseem0888]

Hi,

I am migrating my Access DB to SQL server and i am very new to SQL server I was calculating one of my field in access with the below expression but I don't know how and where this can be done in SQL my calculated field expression is below.

[Primary_Attribute] & "-" & [Project] & "-" & IIf([Series]<10,"00000",IIf([Series]<100,"0000",IIf([Series]<1000,"000",IIf([Series]<10000,"00","")))) & [Series] & IIf([Revision]<>"00","-" & [Revision],"")

 

[Minty]

In Access you could simply use a format to display that rather than a lot of nested If's.

Format([Series],"00000")

You can do something similar in SQL Server - I'm not sure I would use a calculated field for this though, simply build the expression in view and use that as your record source. If you do remember it won't perform well in queries as it can't make use of an index without a lot of hard work. T-SQL something like this

[Primary_Attribute] + '-' + [Project] + '-' + Format([Series],'00000') + '-' + Format([Revision],'00')

 

[Galaxiom]

If you do remember it won't perform well in queries as it can't make use of an index without a lot of hard work.

Specifically, calculated columns in SQL Server can be PERSISTED. This means the results are stored, but updated when the inputs to the expression change. Persisted columns can be indexed as long as they are Deterministic, meaning, among other things, they always return the same results.

Indexes on computed columns - SQL Server

Learn how to create indexes on computed columns

docs.microsoft.com