Create Custom Primary Key SQL

Create Custom Primary Key SQL

Step 1: Create a function to get custom value

e.g. : EMP-0001, EMP-0002

CREATE FUNCTION Empcode (@id INT)

returns CHAR(10)

AS

  BEGIN

      RETURN 'EMP-'+ RIGHT('0000' + CONVERT(VARCHAR(10), @id), 4)

  END 

Step 2: Create table "tbl_Emloyee"

Note :-Add EmploeeId column , datatype as function name and parameter will be id

CREATE TABLE tbl_Employee

(

Id int primary key identity(1,1),

EmployeeId as dbo.Empcode(Id),

EmployeeName VARCHAR(50) NOT NULL,

EmployeeDOB DateTime ,

Gender int,

Address VARCHAR(500),

State int,

Hobbies VARCHAR(200)

)

Step 3:- Insert Values 

INSERT INTO [dbo].[tbl_Employee]

            ([EmployeeName],

             [EmployeeDOB],

             [Gender],

             [Address],

             [State],

             [Hobbies])

VALUES      ('Test User',

             Getdate(),

             1,

             'Address delhi',

             1,

             'Writing Blog') 

Step 4 :- To Check

SELECT * FROM tbl_Employee;

See Custom value as primary key