CODEWITHSUNDEEP
sqlsql-servert-sql
Sophia D'souza
Sophia D'souza

Reputation: 47

Customized auto-incremented value in a column in SQL Server 2016

This is for SQL Server 2016.

Can we have auto-incremented alphanumeric value set for column?

For eg:

enter image description here

Here value for column Channel is auto-incremented.

Appreciate your suggestion very much.

Upvotes: 0

Views: 68

Answers (1)

marc_s
marc_s

Reputation: 754518

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.Channels
(
     ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
     ChannelID AS 'JOSH' + RIGHT('000' + CAST(ID AS VARCHAR(3)), 3) PERSISTED,
     .... your other columns here....
)

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

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

then SQL Server will automatically and safely increase your ID value, and ChannelID will contain values like JOSH001, JOSH002,...... and so on - automatically, safely, reliably, no duplicates.

Or as @JuergenD already mentioned in a comment: don't store the ChannelID in the table at all - just compute it, based on the ID, when needed in a SELECT (or in a view)

Upvotes: 4

Related Questions