Reset Auto Increment field in Microsoft Dynamics NAV Tables

TharangaC

23,114

Like ( 2 )

Report

How to change the current value of an auto increment field? Is it possible without dropping the table? Yes it is possible.
In order to do that you have to use SQL command called DBCC CHECKIDENT

Following is the syntax of the DBCC CHECKIDENT command.

DBCC CHECKIDENT ( <table_name> [ , { NORESEED | { RESEED [, <new_reseed_value> ] } } ] )[ WITH NO_INFOMSGS ]

table_nameIs the name of the table for which to check the current identity value. The table specified must contain an identity column. Table names must comply with the rules for identifiers.NORESEED Specifies that the current identity value should not be changed.RESEED Specifies that the current identity value should be changed. new_reseed_valueIs the new value to use as the current value of the identity column. WITH NO_INFOMSGS Suppresses all informational messages.

To set the IDENTITY column of a table to start from 1, use the following statement :

Use [Database NAV]

DBCC CHECKIDENT ([Table Name], RESEED,0);

By executing the above code you will reset the auto increment number to 0, therefore next number will be 1. That means the number you setting + 1.

You need to have following permission, in order to execute the above code :

Caller must own the table OR member of sysadmin, db_owner or db_ddladmin fixed database role groups.

Please provide your feedback with a comment.
Thank you and Regards,
Tharanga Chandrasekara

This was originally posted here.