How to Define an Auto Increment in SQL Server

 Auto Increment Key in SQL Server

Autoincrement key is available in MySQL, PostgreSQL, Oracle, and many other relational databases, SQL Server is best utilized when assigning unique primary keys to most database tables but its not mandatory to create Primary key in Auto Increment.

The advantages to using numeric, auto incremented keys are numerous, but the most impactful benefits are 

• Faster speed when performing queries
• Data-independence when searching through thousands of records which might contain frequently altered data elsewhere in the table. 

With a consistent and unique numeric identifier, applications can take advantage of these faster and more reliable queries.

Lets do the Demo for my Algae Portal

Step 1: Create the object

/*****Creating Table with Auto increment************/

/*****In SLQ server Auto increment is Identity************/

Use Tempdb

GO

Drop Table EMP

GO

/*Create table with identity column*/

CREATE TABLE EMP 

(

ID INT identity (1,1),

FirstName VARCHAR(100),

LastName VARCHAR(100),

City VARCHAR(100)

)

GO

/********************************/

Inserting records in table but not assigning any value to ID column.

/************Insert dummy records********************/

INSERT INTO EMP (FirstName,LastName,City)

SELECT TOP 10 

'Shashank',

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Verma'

ELSE 'Nigam' END,

CASE

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%1000 = 1 THEN 'Roorkee'

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'Delhi'

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'Bangalore'

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Srinagar'

ELSE 'Aizwal' END

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

/********************************/

 

Step 2. Lets validate the data by using the below query. you will notice that ID column has data filled serially.

/************Insert dummy records****/

Select * from emp

/****************/

Step 3: If I don't want to use the functionality of Identity column, we will use the option  "SET IDENTITY_INSERT TableName ON'.

This will make sure identity column is not automatically filled and we receive the error that "Explicit value must be specified for identity column "

/************Insert dummy records****/

Truncate Table EMP

GO

SET IDENTITY_INSERT EMP ON

GO

INSERT INTO EMP (FirstName,LastName,City)

SELECT TOP 10

'Shashank',

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Verma'

ELSE 'Nigam' END,

CASE

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%1000 = 1 THEN 'Roorkee'

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'Delhi'

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'Bangalore'

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Srinagar'

ELSE 'Aizwal' END

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

/************************************************/

Error: 

Msg 545, Level 16, State 1, Line 42

Explicit value must be specified for identity column in table 'EMP' either 

when IDENTITY_INSERT is set to ON or 

when a replication user is inserting into a NOT FOR REPLICATION identity column.

That’s all there is to it. Now the ID column of our EMP table in Algae portal will be automatically incremented upon every INSERT if we keep IDENTITY_INCREMENT OFF