Click here to Skip to main content
Rate this: bad
Please Sign up or sign in to vote.
See more: SQL-server-2005

I have a datatable with the columns ID,Value,Date,Cumulative,UniqueID where Unique ID is my primary key. UniqueID is an Identity key which auto increments with every new entry.My question is 2 parts
a) Sometimes the data is inserted multiple times so does the primary key take the number of steps or the number of rows added to table to increment the Identity Column value (UniqueID) ???
b)Also there is a possibility of gap in the UniqueID because data deletion.How do I go about checking whether there is a gap in the table and how to resolve it.Do I have to delete the table and startover again when there is a gap???
Came across this page but could not figure out whether to do this when the table is created or can it be done after the table is created.
Thanx in advance
Posted 11-Sep-11 13:14pm
Edited 11-Sep-11 13:15pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Unique key does what it should do: it guarantees its uniqueness, so it can be safely used for referencing of object from other tables. Every technique based an any assumption of the values of the key (and in particular, on the assumption of consecutive values of the key) is wrong and should never be used.
The assumption about the consecutive values would be apparently wrong. Assume you delete few rows, so some unique values disappears. You could possibly expect that the values for unique ID can be later used again and fill the gaps (but it cannot be guaranteed), but the gap is unavoidable before you add any more rows. An attempt to rely on consecutive values will make your data model inconsistent, and the attempt to delete the whole table in hope to restore the same data later will destroy logical data integrity. You want to remove and re-create some rows only "technically", but "logically" restore the objects later, just shifting unique IDs for the restored object. How are you going to restore references from other table for those objects? Even if you can do it, this operation is not transactional: after you remove some rows, some other user might add some; and what you going to do with that? No, it cannot work. You simply should not use any technique based on any assumption of the key values.
Mehdi Gholam at 11-Sep-11 23:31pm
My 5!
SAKryukov at 11-Sep-11 23:41pm
Thank you, Mehdi. --SA
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Just to add to what SAKryukov said,
Sequentially incremented without "holes" numbers are usually a business requirement and I implement them in the application layer, and not in the database layer.
This is usually done by a renumbering a "document number" column.
SAKryukov at 11-Sep-11 23:41pm
Exactly! My 5. --SA

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

  Print Answers RSS
Your Filters
0 Shai Vashdi 963
1 Tadit Dash 270
2 Sergey Alexandrovich Kryukov 250
3 OriginalGriff 233
4 Peter Leow 180
0 Sergey Alexandrovich Kryukov 9,435
1 OriginalGriff 5,498
2 Peter Leow 4,230
3 Maciej Los 3,540
4 Abhinav S 3,353

Advertise | Privacy | Mobile
Web01 | 2.8.140415.2 | Last Updated 11 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid