I have table having id which is primary,notnull & Autoincrement & name field. It default value is 1.
I inserted 4 record so the id became 4.Now I delete 4th record & then again insert a row.Now instead of getting id=4 I get id=5 skipping the 4th id.
Can any body help me out
Sybase identity works that way. It can skip some numbers too. What is happenning with you is normal behaviour. You will have to write your own routine to do a (prev + 1) to get perfectly sequential numbers. A trigger might be more appropriate for this.
What behavior would you expect if you have rows 1 through 10000, and you delete row with id 10. Should the next inserted value use the id 10?
As a general design rule - if you are using surrogate primary keys (i.e. id numbers as primary keys) then the ID values should NOT have any meaning beyond identifying the correct row. In particular there should not be any assumption that the ID values are sequential, or strictly increasing, etc.