Identity column value gives 1001 instead of 1 in sql

Question

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.

Varun Setia · 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.

Rajanikant Hawaldar · Answer

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

Farhan Ahmed · Answer

https://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server

Navaneeth Krishnan · Answer

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

Identity column value gives 1001 instead of 1 in sql

Answers (4)