refresh identity value

hi,
how can I refresh a identity value of a table after a record gets deleted.

for example a table has 100 rows. One of its column name is rowIndex set to identity 1, incremental 1. After I remove the row with rowIndex value 100, if I insert a new record the rowIndex will be 101, but I want its rowIndex value to be 100. Is there a way to update the identity value after a record gets removed?Yes, but that's not the intention of an identity column. An identity column is intended to automaticaly give you unique record ids.

If you want your rowindex to represent something else then add a trigger to get the max(rowindex) and then add 1.sql