Reset Identity Column In SQL
In this blog, I will show How to Reset Identity Column in SQL?
Create table emp:
- CREATE TABLE [dbo].[emp](
- [id] [int] IDENTITY(1,1) NOT NULL,
- [name] [varchar](50) NULL,
- [city] [varchar](50) NULL,
- [Mobile] [varchar](50) NULL,
- CONSTRAINT [PK_emp] PRIMARY KEY CLUSTERED
- (
- [id] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
Insert Data into emp table:
- insert into emp (id,name,city,Mobile) values(1,'Vikram','Pune','0123456789')
- insert into emp (id,name,city,Mobile) values(2,'vishal','Omerga','9921319080')
- insert into emp (id,name,city,Mobile) values(3,'Sunil','PPur','8855663322')
- insert into emp (id,name,city,Mobile) values(4,'vishu','Latur','7208009080')
Show Result as:
Then delete record where id=2 and result is:
- delete from emp where id=2;
Show deleted Result as:
Again, insert data into emp table where id=2 then:
- set identity_insert emp on
- insert into emp (id,name,city,Mobile) values(2,'Vaibhav','Osmanabad','9580256630')
- set identity_insert emp off
Show result as: