How we can make identity column to have a value next to the value of previous identity column row?This sequence breaks when we delete some rows.how we can maintain this sequence?
You could turn identity off and on but I don't recommend this. It's not maintainable.
-
set identity_insert[mydatabase.dbo] mytable on
-
Now you can insert whatever identity you want as long as it's unique.
Keep in mind that sql server keeps all transactions logged. If you delete a record from a table with the ID of 50, that record actually still exists, you just don't see it. Now if you insert a totally new record with the ID of 50, you've lost the ability to use your transaction logs in any meaningful way.
Basically, unless you have a really really good reason to maintain a number sequence as your identity column, I wouldn't recommend it.