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.













No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts