Reset Identity column in SQL Server

Question

I am making an SQL database that stores contacts. I want to be able to delete contacts, and the correct id for each contact is crucial for my software connecting to it. Lets say I have contact James and he is the first one. His id is 0. I add Mary and her id is 1. If I delete James, how can Mary's id be set to 0 instead of staying 1? It has to reset since she is the first one now. In other words, how can I reset all of the IDs in the database when someone gets deleted? Thanks

Answer 1

This is such a bad idea in so many ways. I am debating if I should show you how to do this. There should never be a reason to change a row's identity once it's set.

If there is you are probably using the wrong field as your PK identifier. I am making an assumption here that you're talking about your PK field which is also an identity column.

Keep in mind if you create any tables which link to your contact table and you start changing your Id you need to update all those tables as well. Which will get expensive...