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.
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.
If you do not have SQL Server Books Online (BOL) installed - please do so. The majority of questions asked in the SQL Server forum could be eliminated if people had access to bol. BOL