Search code examples
sqlsql-serverdatabaseprimary-keycalculated-columns

Calculated and converted field

I am trying to create a calculated field in SQL such that when a record is created in the table I capture the primary key Id of this record and it is saved in a new field of the same record but of varchar type.

But I don't know how to implement it. Also the new field must stay in persistence.

Thank you,

Solution

  • You seem to want a persisted computed column. The syntax in SQL Server goes like:

    create table mytable (
    id int identity(1, 1) primary key,
    val varchar(5),
    charid as convert(varchar(10), id) persisted
);

    charid is the computed column. I used an identity column as primary key, but it does not need to be identity for the code to work.

    Now say you insert a new record like so:

    insert into mytable (val) values ('foo');

    The content of the table is:

    id | val | charid
-: | :-- | :-----
 1 | foo | 1

    Demo on DB Fiddle