DELETE, TRUNCATE, DROP and RESEED Identity in Sql Server

Now we are going to see the most 4 important queries in Sql Server DELETE, TRUNCATE, DROP and RESEED. After a long time now only i come to know there is way to increase the identity value using RESEED . Let see the example's of Delete, Truncate, Drop, Reseed identity

 Create a sample Employee Table 

 CREATE TABLE [dbo].[Students]( [ID] [int] IDENTITY(2,1) NOT NULL,
 [StudName] [varchar](50) NULL,
 [Address] [varchar](100) NULL,
 [PhoneNo] [varchar](50) NULL ) ON [PRIMARY]

  Inserting values in the Students table

   insert into [Students] (StudName,Address,PhoneNo)
   values ('vishuva','swiss bridge street','8899434343')

 Output


Note: Check now the seed value is 2

DELETE QUERY

DELETE query is to delete rows in table without WHERE clause it will delete all the rows. However, when a new record is inserted the identity value is increased from 2 to 3. It does not reset but keep on increasing.

 DELETE FROM Students 

Output

 

 TRUNCATE Query
 Truncate will remove all the rows in the table. TRUNCATE resets the identity value. However, when a new          
  record is inserted the identity value is same as original seed value of the table.

 insert into [Students] (StudName,Address,PhoneNo) values ('vishuva','swiss bridge street','8899434343')

Output 

 

RESEED Query 

RESEED identity is used to set seed value to the table . Already we set the seed value as 2 in the above examples. Now we are going to set the seed value to 1 DBCC CHECKIDENT ('Students', RESEED, 1)

Output


Drop Table Query

 Drop Table to delete the entire table DROP TABLE Students