How to set identity column off and on in sql server

SYNTAX:

OFF IDENTITY

SET IDENTITY_INSERT TEST ON

ON IDENTITY

SET IDENTITY_INSERT TEST OFF

Lets Explorer It…….

STEP 1:- CREATE TABLE

CREATE TABLE TEST

(

       SRNO INT IDENTITY (1, 1),

       NAME VARCHAR (100)

)

STEP 2:- INSERT THE VALUES IN THE TABLE

INSERT INTO TEST (NAME) VALUES ('RAM')

INSERT INTO TEST (NAME) VALUES ('RAM')

STEP 3:- SELECT THE RECORD FROM TABLE

SELECT * FROM TEST

RESULTS

SRNO        NAME

----------- -------

1           RAM

2           RAM

(2 ROW(S) AFFECTED)

STEP 4:- SET IDENTITY ON (MEANS DISABLE THE AUTO IDENTITY NUMBER GENERATION)

SET IDENTITY_INSERT TEST ON

STEP 5:- INSERT THE VALUES IN THE TABLE

INSERT INTO TEST (NAME) VALUES ('RAM')

RESULTS

MSG 545, LEVEL 16, STATE 1, LINE 26

EXPLICIT VALUE MUST BE SPECIFIED FOR IDENTITY COLUMN IN TABLE 'TEST' EITHER WHEN IDENTITY_INSERT IS SET TO ON OR WHEN A REPLICATION USER IS INSERTING INTO A NOT FOR REPLICATION IDENTITY COLUMN.

STEP 6:- INSERT THE VALUES IN THE TABLE

INSERT INTO TEST (SRNO, NAME) VALUES (10, 'RAM')

STEP 7:- SELECT THE RECORD FROM TABLE

SELECT * FROM TEST

RESULTS

SRNO        NAME

----------- -------

1           RAM

2           RAM

10          RAM

(3 ROW(S) AFFECTED)

STEP 8:- SET IDENTITY OFF (MEANS ENABLE THE AUTO IDENTITY NUMBER GENERATION)

SET IDENTITY_INSERT TEST OFF

STEP 9:- INSERT THE VALUES IN THE TABLE

INSERT INTO TEST (NAME) VALUES ('RAM')

STEP 10:- SELECT THE RECORD FROM TABLE

SELECT * FROM TEST

RESULTS

SRNO        NAME

----------- -------

1           RAM

2           RAM

10          RAM

11          RAM

(4 ROW(S) AFFECTED)