CODEWITHSUNDEEP
sql-server
user4451265
user4451265

Reputation:

Identity column not incremented by 1?

I've created 4 tables:

`Patient` (Id, Name, ..)

`Donor` (Id, Name, ..)

`BloodBank` (Id, Name, ..)

BloodBankDonors(DonorId, BloodBankId, ..)

And set the Id columns to Identity incremented by 1, seed 1. and made a relationship between (Donor, BloodBank) and (BloodBankDonors). The problem is when I entered some data in the tables BloodBank and the patient, the auto generated Id column was: 1,3,4 and 1,4,5,8 respectively?!

Upvotes: 1

Views: 1682

Answers (3)

jitendra joshi
jitendra joshi

Reputation: 687

So many things can cause gaps in an IDENTITY column. For example rollbacks not resetting IDENTITY, deletes, etc.

So, why do you care about gaps? You shouldn't. If you need a contiguous sequence of numbers, stop using IDENTITY.

Upvotes: 2

Ahsan Aziz Abbasi
Ahsan Aziz Abbasi

Reputation: 168

Deleting data from table holds the log info holding ID (auto generated columns) for recovery purpose.

Try to truncate table and re-enter the data

truncate table Patient

May this help

Upvotes: 0

Anupama Singh
Anupama Singh

Reputation: 11

You might deleting (DELETE command) some records from tables "BloodBank" and the "patient".Deleting record from table holds the log info of column ID(auto generated column) for recovery Purpose. Instead use below mentioned code snippet after "DELETE" command:

DBCC CHECKIDENT('databasename.dbo.tablename', RESEED, number)
if number=0 then in the next insert the auto increment field will contain value 1

if number=101 then in the next insert the auto increment field will contain value 102.

for more clear answer, please share sql script which you are using to create tables and insert records.

Upvotes: 0

Related Questions