Auto Incremented Column With VARCHAR and NVARCHAR Data Type in SQL

Introduction

I have reviewed and answered many questions related to SQL Server. For the previous few months, I found 3 to 4 questions related to Auto-incremented ID with the VARCHAR / NVARCAHAR data type in SQL Server. So I decided to write an article on that, it might help people who are looking for a solution to this.

Problem statement in SQL Server

SQL Server provides functionality called Auto Incremented column. This can be done by setting the IsIdentity property of the column to "yes", but this property only sets on a column that the data type is INT or BIGINT. Suppose I have an EmployeeMaster table and it has an EmployeeNo column with VARCHAR data type and I want to auto-generate EmployeeNo with some prefix like SEZ0000001, SEZ0000002, SEZ0000003, and so on.

Solution

This can be done in one of two ways.

1. Using Computed column

An Auto-incremented column with VARCHAR / NVARCHAR data type in SQL can be done using a computed column.

A computed column is a column that expresses the data that can be used by another column in the same table. This expression can be non-computed columns or any function and constant. It cannot be a sub-query. Here the computed column is marked as Persisted so any update made in the referenced column will be automatically synchronized in the computed column. Nullability for a computed column will be determined by the database engine. There is some limitation with a computed column, such as we cannot insert or update a computed column. We can use a computed column with SELECT, WHERE and ORDER BY clauses but to use a computed column with CHECK, FOREIGN KEY, or NOT NULL constraints we need to set it to persist.

Example

CREATE TABLE [dbo].[EmployeeMaster](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [PreFix] [varchar](50) NOT NULL,
  [EmployeeNo]  AS ([PreFix]+ RIGHT('0000000' + CAST(Id AS VARCHAR(7)), 7)) PERSISTED,
  [EmployeeName] VARCHAR(50),
  CONSTRAINT [PK_AutoInc] PRIMARY KEY ([ID] ASC)
)

Create Unique Index on the EmployeeNo column.

CREATE UNIQUE INDEX [UK_AutoInc] ON [dbo].[EmployeeMaster]
(

   [EmployeeNo] ASC

)

Now insert some test data

INSERT INTO EmployeeMaster (PreFix,EmployeeName) VALUES('SEZ','Jignesh')
INSERT INTO EmployeeMaster (PreFix,EmployeeName) VALUES('SEZ','Tejas')
INSERT INTO EmployeeMaster (PreFix,EmployeeName) VALUES('SEZ','Rakesh')

Output/ Result

SELECT EmployeeNo,EmployeeName FROM EmployeeMaster

Here the main advantage of using a computed column is that we cannot target this column for an insert and update statement, in other words, it ensures that no one can change this column manually. Here we can also use a User-Defined Function with a computed column to get better performance. There are also some limitations with computed columns, such as we can also create an index on a computed column, it is deterministic but not precise, and if this column is referred to in a CLR function then that DB engine cannot verify whether the function id is truly deterministic or not.

2. Manually maintained a column

The same thing can be also done by a manually maintained column. In this method, we need to maintain an auto-incremented column manually in a Stored Procedure or any other part of our code. Actually we cannot say this is auto-incremented but the developer does not bother with this column, what is the value of this column? And so on. This is maintained within the Stored Procedure.

Example

CREATE TABLE EmployeeMasterNew(
 ID INT NOT NULL,
 EmployeeNo VARCHAR(25),
 EmployeeName VARCHAR(50)
);

A stored procedure that helps us to insert a record in a table.

CREATE PROCEDURE InsertEmployee
 @Name VARCHAR(MAX)
AS
BEGIN
 DECLARE @NewEmpID VARCHAR(25);
 DECLARE @PreFix VARCHAR(10) = 'SEZ';
 DECLARE @Id INT;

SELECT @Id = ISNULL(MAX(ID),0) + 1 FROM EmployeeMasterNew

 SELECT @NewEmpID = @PreFix + RIGHT('0000000' + CAST(@Id AS VARCHAR(7)), 7)

INSERT INTO EmployeeMasterNew VALUES (@Id,@NewEmpID,@Name)
END

In this Stored Procedure, we need to create an EmployeeNo manually and insert it into the database. Here we have no control over the insert and update on this column, in other words anybody can update this column outside of the Stored Procedure.

Insert some test data as in the following

EXEC InsertEmployee 'Jignesh';
EXEC InsertEmployee 'Tejas';
EXEC InsertEmployee 'Rakesh';

Output/ Result

Conclusion

Using the two methods described above we can do an auto-incremented column with a VARCHAR / NVARCHAR data type in SQL. The only drawback with a manually maintained column is that we can also modify this column from outside of the Stored Procedure or we can say, with a manually maintained column, the change on the column is out of our control.