Auto increment in SQL Server while using linq

Question

I want to update three fields/columns in my database table. One of them is primary key "id", I made it autoincrement using visual studio's set column properties:

Identity Specification: Yes
(Is Identity)         : Yes
Identity Seed         :  1
Identity Increment    :  1

Everything going ok, but after checking for 2 times that every thing is going fine I emptied the contents of the table so that the columns can be newly populated. But first row autoincrement column got the value 3. I deleted the row and tried again but It became 4 after row was updated. I don't know what is happening. My linq code has nothing to do with it I suppose, because its just insertion.

Code behind linq:

//DLCountryies - .dbml filename
// tblcountry - tablename
// CountryName and CountryCode are Column names
//txtCountryName - textbox

using (DLCountryiesDataContext countries = new DLCountryiesDataContext()) 
{                                                                         
            tblcountry country = new tblcountry
            {
                CountryName = txtCountryName.Text.Trim(),
                CountryCode = txtCountryCode.Text.Trim()
            };

            countries.tblcountries.InsertOnSubmit(country);
            countries.SubmitChanges();
}

You can use DBCC CHECKIDENT in some simple TSQL to reset your identity, open up management studio and fire up a new query. Enter DBCC CHECKIDENT("YourTableName", RESEED, 0). This will reset your auto field. — JonH
– JonH, Commented Oct 19, 2012 at 18:51
You also shouldn't pay attention so much to silly ident numbers. — JonH
– JonH, Commented Oct 19, 2012 at 18:52

Justin Niessner · Accepted Answer · 2012-10-19 18:48:32Z

2

You're actually seeing the proper behavior for auto-increment columns. The identity value isn't determined by using the current values in the table. SQL Server tracks identity values elsewhere.

Those values don't reset after deleting records. The column simply continues counting from the last value.

answered Oct 19, 2012 at 18:48

Justin Niessner

246k40 gold badges416 silver badges546 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

devdoe Over a year ago

"The column simply continues counting from the last value" But after deleting the row last value is preserved ?

Justin Niessner Over a year ago

@rd4code - Yes. The value isn't determined by using data in the table. SQL Server tracks the identity values elsewhere.

JonH Over a year ago

@rd4code - you can reset that identity by using DBCC CHECKIDENT.

devdoe Over a year ago

@Justin Niessner Restarting the server will also do (reseed,0) ?

Justin Niessner Over a year ago

@rd4code - No. The only way to reset is to reseed the identity column. A quick google search (or reading other answers here) will tell you how to do that.

|

JonH · Accepted Answer · 2012-10-19 18:49:07Z

1

That is normal read about DBCC CHECKIDENT in BOL.

This will allow you to reseed your primary key:

http://msdn.microsoft.com/en-us/library/ms176057.aspx

From BOL:

USE AdventureWorks2012; GO DBCC CHECKIDENT ("Person.AddressType", RESEED, 0); GO

answered Oct 19, 2012 at 18:49

JonH

33.2k13 gold badges94 silver badges149 bronze badges

Comments

Vivek · Accepted Answer · 2012-10-20 11:04:02Z

0

This is the normal behavior of autoincrement

If you want to reset autoincrement seeds, you can do

Truncate the table
eg. TRUNCATE TABLE tblcountries
Use DBCC CHECKIDENT
eg.

DELETE FROM tblcountries

DBCC CHECKIDENT('tblcountries',RESEED, 0)

answered Oct 20, 2012 at 11:04

Vivek

3824 silver badges12 bronze badges

Collectives™ on Stack Overflow

Auto increment in SQL Server while using linq

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related