Identity column value gives 1001 instead of 1 in sql

Guest User

Hello..

Can anybody have any idea why it gives value 1001.... i also tryed

Open "SQL Server Configuration Manager"
Click "SQL Server Services" on the left pane
Right-click on your SQL Server instance name on the right pane ->Default: SQL Server(MSSQLSERVER)
Click "Properties"
Click "Startup Parameters"
On the "specify a startup parameter" textbox type "-T272"
Click "Add"
Confirm the changes.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Varun Setia6y ago
Accepted answer
Hi,

As per the doc https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver15

"If gaps are not acceptable then the application should use its own mechanism to generate key values."

You can create a separate table to maintain the value, else insert 1st row as dummy then and then do max(id)+1 to get the sequence value for every new that will be added.
Farhan Ahmed6y ago
https://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server
Navaneeth Krishnan6y ago
https://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server
https://stackoverflow.com/questions/14162648/sql-server-2012-column-identity-increment-jumping-from-6-to-1000-on-7th-entry
Rajanikant Hawaldar6y ago
Hello Nivi sharma,
-On unplanned shutdown the gap in IDENTITY and SEQUENCE values is by design.
-On planned shutdown the gap was fixed in SQL .
-If you want prevent gaps by committing every value,
use trace flag 272. This will cause a log record to be generated for each generated identity value.
The performance of identity generation may be impacted by turning on this trace flag.
-Use a sequence generator with the NO CACHE setting
Setting Trace Flag 272 on SQL Server 2012 that you are expecting here