SQLServer IDENTITY Column with text

Question

How would I create an IDENTITY column in SQLServer with text in the column?

Example:

ABCD-987065
ABCD-987066
ABCD-987067

Answer 1

In addition to the other answers, you could create a computed column on the table to provide what you are asking for.

CREATE TABLE dbo.MyTable
(
    Id int NOT NULL PRIMARY KEY,
    CombinedId AS 'ABCD-' + CAST(Id as varchar(16)) 
)

Or:

CREATE TABLE dbo.MyTable
(
    Id int NOT NULL PRIMARY KEY,
    PrefixField varchar(16),
    CombinedId AS PrefixField + CAST(Id as varchar(16)) 
)

(Your question doesn't say whether the prefix is intended to be fixed or not...)

Answer 2

You'd have to not use an IDENTITY column, but generated the ids/strings yourself.

Far better to format the IDENTITY column for display instead, especially if the string part is constant for all records - makes indexing/querying more performance and saves on db space.

If records may have a different string section (i.e. not all starting with "ABCD-"), then you could store that as a separate field.