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

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.
http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx
 
Thanx in advance
Ananth
Posted 11-Sep-11 13:14pm
Edited 11-Sep-11 13:15pm
v2
Rate this: bad
good
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.
 
—SA
  Permalink  
v2
Comments
Mehdi Gholam at 11-Sep-11 23:31pm
   
My 5!
SAKryukov at 11-Sep-11 23:41pm
   
Thank you, Mehdi.
--SA
Rate this: bad
good
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.
  Permalink  
Comments
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
0 OriginalGriff 555
1 Sergey Alexandrovich Kryukov 518
2 CPallini 490
3 George Jonsson 231
4 Afzaal Ahmad Zeeshan 194
0 OriginalGriff 5,450
1 CPallini 4,500
2 Sergey Alexandrovich Kryukov 4,032
3 George Jonsson 3,057
4 Gihan Liyanage 2,445


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

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