RESET identity Columns in SQL Server

Introduction:

 During application development, we input dummy data into database for testing purposes. But then we come to the point where we want all records of the table to be deleted and also want to start the identity column values from 0. For this, we delete existing data using the Truncate command. This will delete data from table and also reset the identity column value to 0.
Solution:
Truncate table [table_name]
   for example:
         Truncate table ColonyName

But the Truncate command fails to delete the data if there is a relationship given to the table and the identity column is not reset.

 In this case, first you need to delete data from the child and the master table.
After that, execute this command and it will reset your identity column to 0.

 DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value])
-- for example

DBCC CHECKIDENT('ColonyName', RESEED, 0)
Note:
In case of, If you want reset identity value with  no data loss:

No Truncate command is used.
Execute following command:

 DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value])
-- for example

 Here  new_reseed_value should be the last identity column value:









here ColonyID is identity Column and I want reseed the value of this column using the following command:

 DBCC CHECKIDENT('ColonyName', RESEED, 5)
Reset Identity column, Hide or Show the Solution File in Solution Explorer

Popular posts from this blog

SQL Basic- SQL Syntax

Image
          SQL Syntax:            SQL Syntax Database Tables A database contains one or more tables. Each table is identified by a name (e.g. "Student" or "Product").  Tables contain records (rows) with data. Below is a selection from the " Student " table: StudentID             StudentName               Address                Country 1             Ajay               Noida                India 2             Ajeet               Bareilly                India 3             Shivam               Nagaland...
Read more

Type Declaration Instruction in C

Type Declaration Instruction This instruction is used to declare the type of variables being used in the program. Any variable used in the program must be declared before using it in any statement. The type declaration statement is written at the beginning of main( ) function. Ex.: int bas ; float rs, grosssal ; char name, code ; There are several subtle variations of the type declaration instruction. These are discussed below: While declaring the type of variable we can also initialize it as shown below. int i = 10, j = 25 ; float a = 1.5, b = 1.99 + 2.4 * 1.44 ; The order in which we define the variables is sometimes important sometimes not. For example, int i = 10, j = 25 ; is same as int j = 25, j = 10 ; However, float a = 1.5, b = a + 3.1 ; is alright, but float b = a + 3.1, a = 1.5 ;         is not. This is because here we are trying to use a even before defining it. The following statements would work int a, b, c, d ; a = b = ...
Read more

SQL Basic- SQL Commands

Image
          SQL Commands                       The SQL commands to interact with relational databases are: CREATE,  SELECT,  INSERT,  UPDATE,  DELETE and  DROP.               These commands can be classified into groups based on their nature:           DDL - Data Definition Language.            DML - Data Manipulation Language.            DCL - Data Control Language.
Read more