Click here to Skip to main content
14,241,305 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi I am new to programming,

I want to autogenerated a sql table column. It is a primary key.

Incase, If user deletes/replaces data, is it possible to regain that (primary key)field in order to improve the efficiency of data storing.


If I am wrong then please try to correct me.
Thank you in advance.
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 1

You can...but it's a bad idea.
The whole idea of an IDENTITY field in SQL is that the database is responsible for the value and you don't care what it is - you don't have to do anything because the system takes care of it for you. If you start re-using values, then you start removing the advantages that having the system generate them for you brings.
Firstly, you have to be damn careful that two users don't try to reuse the same "hole" for their data - generating new values each time means that doesn't happen. And it's not an easy thing to ensure either, particularly when you start scaling up your DB system, because once you start to get concurrent request execution via a server farm, ensuring that you are the only query trying to use a "slot" is very difficult.
Secondly, you start to run the risk of data integrity problems: if any old data is left "lying around" that refers to the old user of the hole - inside the DB or outside even on paper - then you run the risk of attaching new data to the wrong client. For example, if your system is purchase ordering and you use the identity value as a customer ID, then if you close an account because they don't pay their bills then reusing the id number means that the original company could try to place an order under the ID and it get billed to the wrong company.

I'd live with the gaps - SQL Server is very good at being efficient with it's data storage, and the gaps aren't going to cause any efficiency problems.
   
Comments
Patrice T 1-Jan-16 4:32am
   
+5
Rate this:
Please Sign up or sign in to vote.

Solution 2

Quote:
In case, If user deletes/replaces data, is it possible to regain that (primary key)field in order to improve the efficiency of data storing.
You can, but there is no gain. It is just more complicated because you have to keep track of deleted rows.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100