how to reset identity column in sql code example

Example 1: identity insert on sql server

SET IDENTITY_INSERT sometableWithIdentity ON

INSERT sometableWithIdentity (IdentityColumn, col2, col3, ...)
VALUES (AnIdentityValue, col2value, col3value, ...)

SET IDENTITY_INSERT sometableWithIdentity OFF

Example 2: tsql reseed identity

USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);  
GO

Example 3: reset identity column in sql server

DBCC CHECKIDENT ('YourTableName', RESEED, 1)

Example 4: how to reset the identity column in sql server

USE ;  
GO  
DBCC CHECKIDENT ('.', , );  
GO

Example 5: alter column to not null with default value sql server

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Tags:

Recent Posts