sql server - Is it possible to set a default value for field A based on the value of field B in the same table?

Question

I have a table with an autonumber field. I have a second field where on insert I want it to take the generated number for the autonumber field and prefix it with 'S'. How can I do this? Can I put some kind of expression in the default value specification?

Answer 1

Use a (PERSISTED) computed column:

ALTER TABLE dbo.YourTable ADD PrefixedAutoNumber AS CONCAT('S',AutoNumber) PERSISTED;

Then you don't need to INSERT it, or anything else, it'll have simply have the value of your AutoNumber column prefixed with an 'S'. Persisting the column means you can add it to indexes, which'll help for performance if you'll going to be using the column for WHERE clauses or JOINs.

If it is, however, purely for display/presentation purposes, and will never be used in the WHERE/ON then you don't need to persist the column.