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
No comments:
Write commentsPlease do not enter spam links