CODEWITHSUNDEEP
sqlsql-servert-sql
a_sid
a_sid

Reputation: 587

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

Upvotes: 1

Views: 939

Answers (2)

Gavin Campbell
Gavin Campbell

Reputation: 826

Slightly hacky solution involving triggers:

CREATE SEQUENCE cust_ids START WITH 1  INCREMENT BY 1 ;  


CREATE TABLE Customers(

  customer_id nvarchar(10) PRIMARY KEY,
  customer_firstname nvarchar(20),
  customer_lastname nvarchar(20)
)
GO
CREATE TRIGGER generate_customer_id
ON Customers
INSTEAD OF INSERT
  AS    INSERT INTO Customers(customer_id, 
customer_firstname,  customer_lastname)
SELECT CONCAT('CUST', FORMAT(NEXT VALUE FOR cust_ids, '000000')),   
customer_firstname,customer_lastname
FROM inserted

GO


 INSERT INTO Customers(customer_firstname, customer_lastname) 
 VALUES ('Jan', 'Stewer'), ('Peter', 'Davy')

select * from Customers

Upvotes: 0

Charlieface
Charlieface

Reputation: 71273

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);

Upvotes: 2

Related Questions