CODEWITHSUNDEEP
sql-servert-sql
0x12abc
0x12abc

Reputation: 21

SQL Server : identity and autoincrement for varchar

This is my first question on this platform. I am working on a database project. I want to use autoincrement for my primary key for id, but also want to add an alphabet before it. Are there other ways to do it apart from using 2 columns declaring one as identity and casting the other? I have worked with stored procedures and triggers.

Thank you

PS: I want to do it using one column if possible

Upvotes: 0

Views: 1339

Answers (1)

marc_s
marc_s

Reputation: 754488

You won't be able to do this with just one column.

The best solution is to use

  • an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value
  • a computed, persisted column to convert that numeric value to the value you need

So try this:

CREATE TABLE dbo.tblCompany
(
      ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
      CompanyID AS 'CMP-' + RIGHT('00000' + CAST(ID AS VARCHAR(5)), 5) PERSISTED,
      .... your other columns here....
)

Now, every time you insert a row into tblCompany without specifying values for ID or CompanyID:

INSERT INTO dbo.tblCompany(Col1, Col2, ..., ColN)
VALUES (Val1, Val2, ....., ValN)

then SQL Server will increase your ID value, and CompanyID will contain values like CMP-00001, CMP-00002,...... and so on - automatically. The CompanyID column will be fill automatically, by SQL Server, upon inserting a new row - so there's no need for triggers or stored procedures or anything else - just this declaration in your table definition.

UPDATE: if you're using SQL Server 2012 or newer, you can do it with just one column - if you also create a SEQUENCE - like this:

CREATE SEQUENCE SEQ_CompanyID
AS INT
START WITH 1000
INCREMENT BY 1;

CREATE TABLE dbo.Company
(
    CompanyID VARCHAR(20) NOT NULL
        CONSTRAINT DF_CompanyID
        DEFAULT('CMP-' + CAST(NEXT VALUE FOR dbo.SEQ_CompanyID AS VARCHAR(10))),
    CompanyName VARCHAR(100) NOT NULL,
    ----- other columns here
)

Now if you make sure to insert with omitting the CompanyID column in the insert statement, like this:

INSERT INTO dbo.Company (CompanyName)
VALUES ('Company #1'), ('Company ABC'), ('Company Three');

then you get CMP-1001', 'CMP-1002 etc. as your CompanyID, again, automatically handled by SQL Server upon inserting a new row.

Upvotes: 4

Related Questions