Auto increment primary key in SQL Server (long unique code)

Question

I need add new column in to DB table but with auto increament (primary key). I could use this:

ALTER TABLE [yourTable] ADD ID INT IDENTITY(1,1)

but this creates unique integers 1, 2, 3, etc But i would like create long uniques numbers exmpl: 0542365 or with letters A546980 it is posible?

Answer 1

One way to reliably and nicely do this:

• 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.YourTable
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   AlphaID AS 'A' + RIGHT('00000000' + CAST(ID AS VARCHAR(8)), 8) PERSISTED,
   .... your other columns here....
  )

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

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

then SQL Server will automatically and safely increase your ID value, and AlphaID will contain values like A0000001, A0000002,...... and so on - automatically, safely, reliably, no duplicates.