Reset Identity column to default in SQL Server

The following line resets the Identity value for the Customer table to 
0 so that the next record added starts at 1.

Syntax:

DBCC CHECKIDENT('Customer', RESEED, 0)

Adding Identity Column in SELECT INTO Statement in SQL SERVER

Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

Note: More than one identity column cannot be in table.

-- Create table
CREATE TABLE MyOrders3
(
    ProductName varchar(20)
);

-- Creating Copy of 'MyOrders3' with additional IDENTITY column
select IDENTITY(int, 1,1) AS Id,* INTO MyOrdersIdentity
from MyOrders3

insert into MyOrdersIdentity values ('Samsung')
select * from MyOrdersIdentity

Usage of IDENT_INCR and IDENT_SEED function of Identity column in sql server

Here we are see how to get the Increment & Seed Settings of Indentity in the table.

IDENTITY(SEED,INCREMENT)== IDENTITY(10,5)

Query Snippet:

 -- Create table with identity column
CREATE TABLE MyOrders2
(
    OrderID int IDENTITY(10,5),
    ProductName varchar(20)
);
select IDENT_INCR('MyOrders2') -- 5
select IDENT_SEED ('MyOrders2') -- 10
drop table MyOrders2

-- Create table with identity column
CREATE TABLE MyOrders2
(
    OrderID int IDENTITY(100,50),
    ProductName varchar(20)
);
select IDENT_INCR('MyOrders2') -- 50
select IDENT_SEED ('MyOrders2') – 100
Try After Reseting the Indentity by the below command:

dbcc CHECKIDENT('MyOrders2',RESEED,10)

It still shows the original setup in the table not the reset value by “CHECKIDENT” command.

select IDENT_INCR('MyOrders2') -- 50
select IDENT_SEED ('MyOrders2') – 100