Jun 02

Reset Auto Increment field in Microsoft Dynamics NAV Tables

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 foridentifiers.NORESEEDSpecifies that the current identity value should not be changed.RESEEDSpecifies 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_INFOMSGSSuppresses all informational messages.

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

Use [Database NAV] 
GO
DBCC CHECKIDENT ([Table Name], RESEED,0);
GO
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

Click on a star to rate it!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?