DBCC CHECKDENT

DBCC CHECKDENT

 

Gives info about current identity value , also we can reset identity value to new value.

DBCC CHECKIDENT

 (

    table_name

        [, { NORESEED | { RESEED [, new_reseed_value ] } } ]

)

[ WITH NO_INFOMSGS ]

Arguments

table_name

Name of table

NORESEED

Current value will not be changed

RESEED

Current identity value should be changed.

new_reseed_value

Is the new value to use as the current value of the identity column.

WITH NO_INFOMSGS

Suppresses all informational messages.

Lets see with examples here

CREATE TABLE test

  (

     id   INT IDENTITY(1, 1),

     data VARCHAR(100)

  )

go

DECLARE @i INT = 1

INSERT INTO TEST

VALUES      ('data' + CONVERT(VARCHAR(10), @i) )

go 10

*NORESEED

DBCC CHECKIDENT (TEST, NORESEED)

Will give list value of current identity value and current column value

Checking identity information: current identity value '10', current column value '10'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

It will give value of 10 current identity value

Now we will delete 2 rows from here

DELETE FROM test

WHERE  id > 8

Now we will run this query again

DBCC CHECKIDENT (TEST, NORESEED)

Checking identity information: current identity value '10', current column value '8'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now we will set this value to 8

*RESEED

Will set current identity value to 8.So newly added row will have value of 9

DBCC CHECKIDENT (TEST,RESEED,8)

Checking identity information: current identity value '10'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

now we will insert new row in table

INSERT INTO test

VALUES      ('data9')

Now lets check identity value again

DBCC CHECKIDENT (TEST,NORESEED)

Checking identity information: current identity value '9', current column value '9'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.