identity error

Help!

I have said yes to identity for my id in some tables. I left the values of identity seed and increment as 1, so it should really start at 1 and keep increasing by 1. When I input and then decide delete a row it affects the increment seed!!!e.g. if i delete row 2, the next row should be 2 but it becomes 3!! how do i stop this?That's the normal behavior. You can reset the seed with DBCC checkident,reseed (see manual), but that'll only set a new starting point and you'll keep getting "holes" when you delete rows. To prevent that, you should disable "identity" and increment manually.|||Its not a good idea in general to rely on surrogate keys for sorting or ordering data. It is better to use a natural key or add a datetime value.|||asbirpam, my advice is, just don't worry about it

leave the holes

are you worried about running out of numbers?

let's say you add new rows to your table at the rate of 100 per hour (that's more than one per minute)

with an integer identity field, you will not run out of number for over two thousand years

i personally would not worry about it|||Not to mention that you can use the datatype bigint (ss2k) as well for identities. So for the same 2000 year time span you could have over 500 billion transactions per hour before the identity would rollover ... Just think that when our grandchildren (future dbas) talk to us about their trillion records per hour processing we can warn them of this.