| Identity column value jumps to 1001 in Sql Server or Gap in identity column
Microsoft has done some changes in SQL Server 2012 this will result in some gaps in identity column. This could be from some reason like rebooting of machine or automatic server restart for installing update.
To overcome this issue we have 2 below choices
(1)We can use trace flag 272 o this will record all the logs for each item created in identity column. The drawback of this is performance of machine will be little impacted for large system.
(2)We can use Sequence generator with NO CACHE setting and change some seeting by following below steps
(i)First Open SQL Server Configuration Manager
(ii)Now Click SQL Server Services on the left
(iii)Right-click on your SQL Server instance name on the right pane Default: SQL Server(MSSQLSERVER)
(iv)Now Click Properties
(v)Now Click Startup Parameters
(vi)Specify a startup parameter textbox type "-T272"
(vii)Click Add
(viii)Confirm the changes
So above 2 methods helps to controlon the gap in identity columns | | |