Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JWong
Starting Member
5 Posts |
 Posted - 2010-04-16 : 14:48:57
|
| Hi Friends,I have a person table with each new person needing an 8-digit non-sequenced numeric ID (like a student ID). The table is small - no more than about 2,000 new records per year. I was thinking about having a Unique ID table, and then just pull from that table (and then delete the pulled record from it) as needed. But that seems like an awefully large table (millions of records just to pull 2,000 per year) for such a small job.I was also thinking about just running a loop: inserting into the person table with a randomly generated 8-digit number to gether with other information. If a key violation error is raised, loop back with another randomly generated number. But this just feel so trial-and-error and not very elegant.I'm wondering if there is a better way to do this. . .Thanks for your help in advance!Justin |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-16 : 14:55:27
|
| Must the numbers NOT be sequential / contiguous?We have built systems in the past where the number had to be "not guessable". We did this by having two parts: an IDENTITY number, that was the PK, and an "authorisation component" that was needed in order to gain access. This was taken as the last 6 characters (without hyphens) from a GUID (and was stored in a separate field). So there was an ID which was sequential, and a part-GUID that was used as a kind of "checksum" |
 |
|
|
JWong
Starting Member
5 Posts |
Posted - 2010-04-16 : 15:27:04
|
| Interesting Kristen... But if the user has to use an 8-digit number to log on, and 6 of those digits come from the GUID, then I can use only two of the PK number to combine it with the 6-digit portion of the GUID. Also, could 2 GUIDs potentially have the same last 6 digits? If so, then I would still somehow need to check for uniqueness during the insert. . . Or am I missing your point entirely? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-16 : 15:45:43
|
Yes 2 GUIDs could have the same last 6 digits.We could have two IDs:123456 and 567890and their 6-digit "random checksum" (e.g. made by using part of a GUID) could be the same. But equally they could be any of the other 999999 6-digit combinations The fact that both IDs may have the same random-checksum is not relevant. The GUID generated by NewID() is not calculated mathematically starting from the ID itself, so there is no way to reverse engineer what it might have been ... and there are 0-999999 possible numbers it could have been - which is a lot to attempt to hack  .Hopefully that is clear as to the usage? If not please ask for clarification  I hasten to add this this is not cryptographically secure. Lots of clever work has been put into HASH and SALT algorithms to make them hard to break, so this is just cheap-and-cheerful, but it does mean that you cannot use the ID 1234546 unless you also know the 6-digit-random-checksum (or can try to crack it by brute force) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-04-16 : 15:52:36
|
[code]SELECT ABS(CHECKSUM(NEWID())) % 90000000 + 10000000[/code]
N 56°04'39.26"E 12°55'05.63" |
|
|
|
JWong
Starting Member
5 Posts |
Posted - 2010-04-16 : 16:18:00
|
| Thanks a bunch, Peso and Kristen. I think the ABS(CHECKSUM(NEWID())) solution is exactly what I needed. Have a great weekend.Justin |
|
|
|
|
|
|
|
|
Unique Non-sequenced 8-digit Numeric ID