Auto Primary Key Growth in SQL Server (Long Unique Code)

Question

I need to add a new column to the DB table, but with auto-increment (primary key). I could use this:

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

but it creates unique integers 1, 2, 3, etc. But I would like to create long uniques numbers: 0542365 or with the letters A546980, is this possible?

Answer 1

One way to reliably and beautifully do this:

• a ID INT IDENTITY(1,1) so that SQL Server handles the automatic increase of your numeric value.
• a calculated, stored column to convert this numeric value to the desired value

So try the following:

 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 line 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 the ID value, and AlphaID will contain values ​​such as A0000001 , A0000002 , ... etc. automatically, safely, reliably, without duplicates.