0

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

a_sid
  • 577
  • 10
  • 28
  • How does an Alphanumerical value "increment"? What is `'B'+1`? what about `'Sausages' + 1`? – Thom A Jul 05 '21 at 08:14
  • Also, in the image, you have defined `Id` as a `int`; it can only store integer values. – Thom A Jul 05 '21 at 08:15
  • 4
    So you want to **prefix** an incrementing integer with a letter? Then use a computed column. – Thom A Jul 05 '21 at 08:17
  • @Larnu _it can only store integer values._ Can this value be merged with some other characters in SQL Server? – a_sid Jul 05 '21 at 08:17
  • @Larnu _Then use a computed column._ What is a "computed column"? I am still figuring my way around SQL Server. – a_sid Jul 05 '21 at 08:18
  • 4
    [Specify Computed Columns in a Table](https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver15) – Thom A Jul 05 '21 at 08:20
  • 4
    `ALTER TABLE Student ADD MyId AS CONCAT('S', Id)` – Charlieface Jul 05 '21 at 08:35
  • Maybe, you can think to use Autoincrement uniqueidentifier https://stackoverflow.com/questions/2837559/autoincrement-uniqueidentifier – Esat Erkec Jul 05 '21 at 08:41
  • 1
    You really should reconsider changing your requirement. It doesn't make much sense to have "S" always in the beginning. You're just making things a lot more complex for yourself... for what purpose? – James Z Jul 05 '21 at 12:05
  • I'm with James Z on this one. Sounds like you could use a normal int id and just provide a view with an S in front to the one who enforces this...structurally suboptimal requirement – George Menoutis Jul 05 '21 at 12:09
  • @GeorgeMenoutis Thank you for the response. _....provide a view with an S in front..._ Do you mean on the front-end? – a_sid Jul 05 '21 at 14:07
  • @JamesZ Thank you for the response. _You're just making things a lot more complex for yourself... for what purpose?_ There are two reasons. The first is that it is a requirement from my boss. The second is that **I really want to learn how to create auto-incrementing alphanumeric primary keys** even if I could create an other varchar(50) column in my table called `Code` and store these alphanumeric codes there. – a_sid Jul 05 '21 at 14:13
  • @a_sid You're making it more complex because there is no such thing as `auto-incrementing alphanumeric primary keys` -- because you can't add things to text. And your key isn't really "alphanumeric", it's just a number with prefix "S". And most likely your boss doesn't understand this makes it more complex, and instead of making things more complex because of that, you should really discuss with him if this requirement is really needed, or was it just an idea that doesn't actually need to be like that. – James Z Jul 05 '21 at 14:15
  • @JamesZ My question really is general and is not just confined to the idea of appending "S" to an auto-incrementing integer. What if I want the ID to be of the format `S000000`? – a_sid Jul 05 '21 at 14:20
  • 1
    That's still just an "S" prefix with padded zeros. You can do that in presentation layer and have integers in the database. It's not really alphanumerical unless you have A0, A1... AZ, B0, B1... BZ... Z0... ZZ. – James Z Jul 05 '21 at 14:23
  • @JamesZ Good point. Now I am just interested in this problem as a mental exercise :) – a_sid Jul 05 '21 at 14:25
  • Have you researched [`[tsql] increment alphanumeric`](https://stackoverflow.com/search?q=%5Btsql%5D+increment+alphanumeric)? – HABO Jul 05 '21 at 16:29
  • The simplest way to accomplish something like this is to use 2 (or more) columns. One column for the prefix - once column for the ID. Then you can combine those in a computed column - or in the query (code) as needed. Trying to build something in a single column breaks all kinds of normalization rules - and just makes coding the system harder. – Jeff Jul 05 '21 at 16:45
  • @HABO Thank you for the response. I did (although not in the SO engine as in your link). I wanted to know if it is possible to accomplish what I want directly from the interface shown in my post. – a_sid Jul 05 '21 at 17:16

1 Answers1

2

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);
Charlieface
  • 52,284
  • 6
  • 19
  • 43