CODEWITHSUNDEEP
linqsql-server-2008auto-increment
devdoe
devdoe

Reputation: 4360

Auto increment in SQL Server while using linq

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();
}

Upvotes: 0

Views: 1546

Answers (3)

Vivek
Vivek

Reputation: 382

This is the normal behavior of autoincrement

If you want to reset autoincrement seeds, you can do

  1. Truncate the table
    eg. TRUNCATE TABLE tblcountries

  2. Use DBCC CHECKIDENT
    eg.

    DELETE FROM tblcountries

    DBCC CHECKIDENT('tblcountries',RESEED, 0)

Upvotes: 0

JonH
JonH

Reputation: 33163

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

Upvotes: 1

Justin Niessner
Justin Niessner

Reputation: 245449

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.

Upvotes: 2

Related Questions