SQL

Reset Identity Column In SQL

Create table emp:

  1. CREATE TABLE [dbo].[emp](  
  2. [id] [int] IDENTITY(1,1) NOT NULL,  
  3. [name] [varchar](50) NULL,  
  4. [city] [varchar](50) NULL,  
  5. [Mobile] [varchar](50) NULL,  
  6. CONSTRAINT [PK_emp] PRIMARY KEY CLUSTERED  
  7. (  
  8. [id] ASC  
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  10. ) ON [PRIMARY]  
  11. GO  
Insert Data into emp table:
  1. insert into emp (id,name,city,Mobile) values(1,'Vikram','Pune','0123456789')  
  2. insert into emp (id,name,city,Mobile) values(2,'vishal','Omerga','9921319080')  
  3. insert into emp (id,name,city,Mobile) values(3,'Sunil','PPur','8855663322')  
  4. insert into emp (id,name,city,Mobile) values(4,'vishu','Latur','7208009080')  
Show Result as:

show table

Then delete record where id=2 and result is:
  1. delete from emp where id=2;  
Show deleted Result as:

result

Again, insert data into emp table where id=2 then:
  1. set identity_insert emp on  
  2. insert into emp (id,name,city,Mobile) values(2,'Vaibhav','Osmanabad','9580256630')  
  3. set identity_insert emp off  
Show result as:

query result
Ebook Download
View all
Basic SQL Queries
Read by 19.4k people
Download Now!
Learn
View all
Membership not found