Primary Identity Column of table gets random value in sql server 2012

Question

CREATE TABLE [dbo].[tbl_Person](
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Address] [varchar](50) NULL,
[Phone] [varchar](50) NULL,
 CONSTRAINT [PK_tbl_Person] PRIMARY KEY CLUSTERED 
(
    [PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

In this table when inserting value from application using stored procedure with query

INSERT INTO [dbo].[tbl_PersonName]
       ([Name])
 VALUES
       ('name')

the 'PersonID' column got unusual value. The value jumps randomly like, from 1896 to 11896 and again jumps from 11905 to 21905. i worry if such jumps occurs the range could be insufficient and the data seems quite unorganized. I wonder if those unused value would be reused or not? After inserting some data what should i do to re managed the sequence? i am worried with the issue, this didn't happen in 2010 though i used the same pattern of code as in 2012. help me!

enter image description here

@Martin that actually didn't solve the problem to me but that helped to understand the problem quit more. — barcanoj, Oct 26 '13 at 11:28
#barcanoj - This question is definitely an exact duplicate. You say in the comment to Rahul that trace flag 272 worked. That is mentioned in the accepted answer there. Along with a link to the Microsoft Connect thread where this behaviour change is discussed in much more detail. — Martin Smith, Oct 26 '13 at 11:38

Rahul Tripathi · Accepted Answer · 2013-10-26T11:21:52.440

2

That is because you are trying to insert a new column to your primary key column.

If you have a table lets say with 1000 rows and you delete the record at 797 position. Then when you will insert a new row to your table then the value will be inserted at 1001 position not at 797.

I wonder if those unused value would be reused or not?

No the values will not be reused. As whenever you delete a column from the table and then try to insert the value again in the table then the value will be inserted to a new position.

EDIT:-

Also you may find this helpful.

Microsoft added sequences in SQL Server 2012

By default when you create a SEQUENCE you can either supply CACHE size. Caching is used to increase performance for applications that use sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers.

To fix this issue, you need to make sure, you add a NO CACHE option in sequence creation / properties like this.

CREATE SEQUENCE TEST_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE
   NO CACHE

However if you want the same old behavior then you may use trace flag 272

Since the trace flag 272 option worked for OP. Here are the steps to add trace flag 272:-

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
Click "Properties"
Click "Startup Parameters"
On the "specify a startup parameter" textbox type "-T272"
Click "Add"
Confirm the changes

edited Oct 26 '13 at 11:21

answered Oct 26 '13 at 10:50

Rahul Tripathi

168,305
31
280
331

Name is not the primary column and i have not deleted any row. – barcanoj Oct 26 '13 at 10:52
Name is not a primary key column but when you insert a new entry in the table then the primary key is automatically created – Rahul Tripathi Oct 26 '13 at 10:55
increment value is 1 so the value should have increased by 1 but in this case when i insert new data value goes from 1896 to 11896. – barcanoj Oct 26 '13 at 10:58
@barcanoj:- This may be due to the Squence which are added by Microsoft in SQL Server 2012. Updated my answer too. Hope that helps! – Rahul Tripathi Oct 26 '13 at 11:02
I have not used sequence anywhere, is there any default setting? How can i overcome the issue now after i inserted some data. – barcanoj Oct 26 '13 at 11:06
@barcanoj:- You may use trace flag 272 for the old behavior! – Rahul Tripathi Oct 26 '13 at 11:07
@barcanoj:- Also I have provided you the workaround for the same in my answer! – Rahul Tripathi Oct 26 '13 at 11:10
Thanks for the support. specifying startup parameter to "-T272" the problem seemed to be solved. – barcanoj Oct 26 '13 at 11:17

Primary Identity Column of table gets random value in sql server 2012

1 Answers1