Search code examples
sql-serversql-server-2014tsql-sequence

Is it possible to concatenate a string to a sequence value and use as a column default value?

I'd like to set a default value for a column from a sequence like what is done here, but also prepend a value in front of the sequence so the value saved in the table looks like P123. Is that possible?

Solution

  • It's totally possible.
    Changing the example from the post you've linked to something like this:

    create sequence mainseq as bigint start with 1 increment by 1;

create table mytable (
    id      varchar(20) not null constraint DF_mytblid default 'p' + CAST(next value for mainseq as varchar(10)),
    code    varchar(20) not null
)

    Test:

    INSERT INTO MyTable (Code) VALUES ('asdf'), ('cvnb')

SELECT *
FROM MyTable

    Results:

    id  code
p1  asdf
p2  cvnb