How to create a table in sql server that has an id column with numbers and letters

Question

I have an issue with my sql server.

I want to be able to create a table that has only one id column which contains letters and numbers. The numbers should auto increment when a new row is inserted.

The letters should not change only the numbers should increase as the rows do. I want to be able to look at an id and tell what department an employee is from and what number he/she is. For example IT001 then IT002 and so on.

Thank You in Advance

I know two are better, but for this I have to have only one. — Musi, May 01 '15 at 23:07
@Musi You can't do this directly, but this is a good approach http://stackoverflow.com/a/16801288/2161256 — nramirez, May 01 '15 at 23:11
This is called a smart key, and is not a best practice. You can obtain this information in other ways. For example, the Department Id should be a foreign key. — Pittsburgh DBA, May 01 '15 at 23:39
Out of curiosity, why can't you have two columns? What's the constraint? — Mike Parkhill, May 01 '15 at 23:53
@Mike Parkhill The employer that wants me to create this table does not to columns. I have explained to him that it is not the best way, but he only wants one. — Musi, May 01 '15 at 23:58

yazanpro · Answer 1 · 2015-05-01T23:18:28.883

2

You better use a computed column which will not involve any coding from your side except the column creation. Here is a sample table creation script. Check FriendlyID column

CREATE TABLE [dbo].[ITEMS](
[ITEMID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[FriendlyID]  AS ('IT'+CONVERT([varchar](31),[ITEMID],0)),
 CONSTRAINT [PK_ITEMS] PRIMARY KEY CLUSTERED 
(
    [ITEMID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO ITEMS VALUES ('John')
INSERT INTO ITEMS VALUES ('Sam')
INSERT INTO ITEMS VALUES ('Kate')

SELECT * FROM ITEMS

edited May 01 '15 at 23:18

answered May 01 '15 at 23:13

yazanpro

4,512
6
44
66

I don't want there to be two columns like the ITEMID and Friendly ID. I want only one column. – Musi May 01 '15 at 23:43
1

You cant always have what you want :-) There are only three ways of doing this. **Trigger** to create the column, use **Computed** column or use a **View**. – Rohit Gupta May 02 '15 at 00:01

isevcik · Accepted Answer · 2015-05-01T23:37:56.073

0

Ideal for instead of trigger:

CREATE TABLE Employees (
    Id   VARCHAR(16) PRIMARY KEY,
    Name VARCHAR(64)
);
GO

CREATE TRIGGER InsteadTrigger on Employees
INSTEAD OF INSERT
AS
BEGIN
  BEGIN TRANSACTION

  DECLARE @maxvalue INT = (SELECT MAX(CAST(SUBSTRING(Id, 3, 16) as INT)) FROM Employees )
  INSERT INTO Employees (Id, Name)
       SELECT 'IT' + CAST(ISNULL(@maxvalue, 0) + 1 AS VARCHAR(16)), Name
       FROM inserted

  COMMIT TRANSACTION
END;
GO

edited May 01 '15 at 23:37

answered May 01 '15 at 23:32

isevcik

553
1
4
15

1

If two processes are attempting to insert at the same time, it's possible that both will get the same value for `@maxid` and one of them will fail because it's attempting to insert a duplicate value. – Adam Porad May 01 '15 at 23:37
@AdamPorad fixed by transaction – isevcik May 01 '15 at 23:41
Not going to down vote, since this would work if you are desperate, but the complexity added by a trigger is not worth it here in my opinion. Its hard to document clearly and everytime someone revisits this they have to do a little mystery solving to remember what generates these keys. – AaronLS May 01 '15 at 23:44
Is it possible to make the ID auto generate, so that when just the name is inserted it will automatically assign it an ID number. – Musi May 01 '15 at 23:49
@Musi: actually, the trigger does it – isevcik May 02 '15 at 00:00
@fanosek: when I insert a value for just the name it gives me this error: "Column name or number of supplied values does not match table definition." – Musi May 02 '15 at 00:02

score 0 · Answer 3 · answered May 02 '15 at 06:53

You can have 'computed column'.

Its a good practice to keep integer identity column in your table, so beside removing it , you can add new column based on your identity column and text you want to prefix.

This will be a normal string column with 'computed column specification' in table designer.

How to create a table in sql server that has an id column with numbers and letters

3 Answers3