sqlsql-servert-sql

Create an auto incrementing alpha numeric primary key


I have a Student table in SQL Server database which is as follows:

CREATE TABLE [dbo].[Student] (
    [Id]   INT           NOT NULL IDENTITY,
    [Name] NVARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

I want the Id property to be alpha-numeric and auto-increment itself for a new entry. I want Id to be S<number> and then S<number+1> and so on.

I tried to solve this problem as a two-step process:

(i) I first tried to make the Id an auto-incrementing property by doing this:

enter image description here

Then I pressed "Update":

enter image description here

And then I updated again and it led me to this table:

CREATE TABLE [dbo].[Student] (
    [Id]   INT           NOT NULL IDENTITY,
    [Name] NVARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

I do not think Id is an auto-incrementing value yet. How can I make it both auto-incrementing and alpha-numeric from the following interface:

enter image description here


Solution

  • It seems that you don't really want a fully auto-incrementing alphanumeric column A001,A002...B001, you just want a regular integer column with a prefix of S. For this you can use a simple computed column

    ALTER TABLE Student
      ADD MyId AS CONCAT('S', Id);