Auto Incremented Column With VARCHAR and NVARCHAR Data Type in SQL

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 [UK_AutoInc] ON [dbo].[EmployeeMaster]
(

   [EmployeeNo] ASC

)

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.