Friday 21 March 2014

Reset Autoincrement field in NAV

In SQL Server we have command called DBCC CHECKIDENT 

Checks the current identity value for the specified table and, if needed, corrects the identity value.
You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

The syntax of the DBCC CHECKIDENT command is as follows:

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

The <table_name> parameter is the name of the table for which to check the current identity value and it must contain an identity column.  The NORESEED clause specifies that the current identity value should not be changed.  The RESEED clause specifies that the current identity value should be changed.  The <new_reseed_value> is the new value to be used as the current value of the identity column.  Lastly, the WITH NO_INFOMSGS clause suppresses all informational messages.


If the table has no rows and execute below statement

Use [Demo Database NAV (7-1)]
GO
DBCC CHECKIDENT ([CRONUS Nederland BV$Test Increment], NORESEED);
GO

Result:
Checking identity information: current identity value 'Null', current column value 'Null'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If it has one row then result of above statement is:

Checking identity information: current identity value '1', current column value '1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


To set the IDENTITY column of a table to start from 1, the following statement can be issued:
The following example forces the current identity value in the table to a value of 0. The next row inserted will use 1 as the value, that is, the new current increment value defined for the column value plus 1.

Use [Demo Database NAV (7-1)]
GO
DBCC CHECKIDENT ([CRONUS Nederland BV$Test Increment], RESEED,0);
GO

Permissions:
Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.