Short answer: 

DBCC CHECKIDENT (<tablename>, RESEED, 0)

To reseed the “Site_Type” table, making the first inserted row having a value of 1:

DBCC CHECKIDENT (Site_Type, RESEED, 0)

Details

Microsoft SQL Server’s identity column generates sequential values for new records using a seed value. The term seed refers to the internal value SQL Server uses to generate the next value in the sequence. By default, an identity column’s first value is 1 and each new value increments by one (1, 2, 3, 4, and so on).

You can control the column’s first and subsequent values, by specifying those values when you create the column. For instance, you might want to begin with a purchase order number of 1001 rather than 1.

Once the table’s in production you can reset, or reseed the column. In other words, you can change the column’s seed value at any time. For instance, you might reseed a column after deleting records or moving data to an archive table.  Note – truncate will not work if you have relationships so you need to delete + reseed.

The good news is that seeding and reseeding an identity column is easy and relatively safe, if you do it correctly. It’s common to seed a new table or to reseed a production, but empty table. However, as a general rule, it’s not a great idea to reseed an identity column in a table that contains data without some serious checks and balances.

About identity

Developers and writers alike tend to refer to SQL Server’s identity column property as a data type. It’s actually a column property.  SQL Server’s identity column is flexible. However, there are a few restrictions:

• An identity column must be one of the following numeric data types: decimal, int, numeric, smallint, bigint, or tinyint.
• An identity column can’t accept or store NULL.
• Each table can contain only one identity column.

When you create an identity column, you specify two values: Identity Seed and Identity Increment. The seed value specifies the column’s first (or next) value. SQL Server adds the increment value to the last identity value to generate the next value, in sequence.

For instance, a seed value of 1 and an increment value of 2 will generate the values 1, 3, 5, 7, and so on. By default, both the seed and incremental values are 1. Business rules often require a bit of customization.

Seeding

Using Management Studio is probably the easiest way to seed an identity column when you create the table. In this case, it’s a simple matter of setting the appropriate property value, as you can see in Figure A. You can also use SSMS to reseed an existing identity column.

Figure A

Seed an identity column when you create it in SSMS.

If you create the table using code, you can easily seed the table’s identity column using the Transact-SQL (T-SQL) CREATE TABLE statement in the following form:

CREATE TABLE tablename
(
  columnname datatype identity [(seed, increment)
  [NOT FOR REPLICATION]],
  [columnname ...]
)

In the code, datatype is a numeric column. Both seed and increment are optional and the default value for both is 1.

Figure B shows the result of using CREATE TABLE to create a table named Orders and setting the OrderID column’s identity seed and increment values to 100 and 10, respectively. As you can see, the first identity value is 100 and each subsequent value increases by 10. (You can decrease identity values by specify a negative value for increment.)

Figure B

Use CREATE TABLE to seed an identity column.

Checking and reseeding

For instance, if you copy all the table’s records to an archive table and then delete all the records in the source table, you might want to reseed the source table’s identity column, so you can control the sequence. Use T-SQL’s DBCC CHECKIDENT as follows to reseed an identity column:

DBCC CHECKIDENT
(
  tablename
  [, [NORESEED | RESEED [, newreseedvalue]]]
)
[WITH NO_INFOMSGS]

Table A defines this statement’s optional parameters

Technically, DBCC CHECKIDENT checks and corrects an identity value. Simply put, use it to learn the current identity value or to reseed an existing identity column.

Example

The DBCC statement is formulated as:

DBCC CHECKIDENT (<tablename>, RESEED, 0)

To reseed the “Site_Type” table, making the first inserted row having a value of 1:

DBCC CHECKIDENT (Site_Type, RESEED, 0)

You notice that SSMS gives you the current identity value with the reset value.  Inserting a row, the identity column now starts with a 1.

Reseeding an identity column is a common task, but you must know the basics or you might get into trouble. Specifically, an identity column can generate duplicate values. Knowing when and why is the key to keeping an identity column in check.