Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2003
    Posts
    34

    Unanswered: Random unique ID...

    How would I go about generating a RANDOM UNIQUE five (could be more or less, but 5 would be best) digit number for use as a primary key for my database table?

  2. #2
    Join Date
    Sep 2003
    Posts
    14

  3. #3
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    why would you that? You can use the identity property with auto numbering which will generate a unique number.
    Johan

  4. #4
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    Jora is right,

    However if you still need a random 5 digit number to be generated for other purposes, you can still take your identity column and rand() it to get the value. This is a better way to do it.

    A simple code would be:

    Code:
    Note: Recordid is the identity column
    
    
    declare @recordid int,
                @randomnum varchar(5)
    
    set @recordid=((select max(recordid) from Table)+1)
    set @randomnum=( right(cast(rand(@recordid) as varchar(8)),5))
    
    select @randomnum
    Haven't tested the code, but it should be something like that.
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    My personal preference is to use the UniqueIdentifier datatype with the NewID function. It's a long value, but I never have to worry about duplicates within or between databases, and internal IDs should never need to be seen by the users anyway.

    You can do a lot of creative SQL with UniqueIdentifier values that you can't with incremented values.

    blindman

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Also, using only five digits (maximum 99999 values) and creating numbers at random you have a 50% chance of duplicating a value before you create 315 records. I doubt that five digits is enough.

    blindman

  7. #7
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    Hmm...I just got to know about UniqueIdentifier recently.
    From what I see, it is a good reference key for a table that is not bound to the identity column datatype range....

    But basically, what else is it good for? Is it much faster during index searching??


    With UniqueIdentifiers, I can't select max(identitycolumn) to get the last row, and some SQL statements that I can do with numeric identity columns.


    Blindman, can you share some examples or explain in which areas the uniqueidentifiers are good for?

    Thanks.
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are better using datetime values to select last rows.

    GUID values allow you to combine records from different databases or partitioned tables and never have to worry about duplicate IDs.

    GUID values allow you to create and assign a value within a procedure and not have to lookup whatever value was assigned by an identity column.

    GUID values allow you to create "Reverse relationship" schemas, where one table stores detail information for records in multiple tables. For instance, a Log table could record changes to specific records in multiple tables with GUIDS, and you don't have to worry about a record in TableA haveing the same ID as a record in TableB.

    GUIDS can be implicitly converted to and from Char(36) values.

    Think of GUIDs as a virtual table of UniqueIDs with one-to-many joins to every table in your database.

    I think the use of sequential ID fields can lead to bad programming that relies too heavily on a fabricated index to establish an order of events. Then the application gets messed up when a procedure crashes and leaves gaps, or when data has to be merged or inserted. GUIDs encourage you to use internal keys for internal logic, and to use natural values, like datetime, for external data presentation.

    Others will disagree with me on this, I'm sure, but the more I use GUIDs the more I am convinced that I'll never design a database with a sequential ID again.

    blindman

    PS: Be aware that SLQ Server's RAND function is a crappy random number generator, and it's output is not normally distributed if your seed values are not significantly different.

  9. #9
    Join Date
    Sep 2003
    Posts
    34
    I was using the IDENTITY function to do what I wanted, but I hated the gaps that were caused when I deleted, or records were not inserted but the IDENTITY still incremented. Then after i posted on here I was told that I shouldnt use IDENTITY for what I wanted, and after much research I had to agree that random numbers were a better choice. I have a made a UNIQUE random number generator that I insert in my SP for when I want to insert a new record, seems to work well enough.

    SET @transaction_id = 'TRN' + CAST(((99999 - 10000) * Rand() + 10000) AS char)
    WHILE EXISTS (SELECT * FROM expenditure WHERE transaction_id = @transaction_id)
    BEGIN
    SET @transaction_id = 'TRN' + CAST(((99999 - 10000) * Rand() + 10000) AS char)
    END

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That will work, but I'll still like the GUIDs. In a well designed database I believe that internal IDs should be invisible to both the users and the developers; meaning they are used for associating records only and are never typed in. Resist the temptation of other developers using your system to create hybrid IDs from your five digit IDs by prepending strings or some other such nonsense.

    Five digits is not a lot, but I suppose you know your data and you don't expect it to grow that large. Still, the day you try to insert record 99990 be prepared for a long wait.

    blindman

  11. #11
    Join Date
    Sep 2003
    Location
    Portland, Oregon
    Posts
    11
    Originally posted by blindman
    That will work, but I'll still like the GUIDs. In a well designed database I believe that internal IDs should be invisible to both the users and the developers; meaning they are used for associating records only and are never typed in. Resist the temptation of other developers using your system to create hybrid IDs from your five digit IDs by prepending strings or some other such nonsense.

    Five digits is not a lot, but I suppose you know your data and you don't expect it to grow that large. Still, the day you try to insert record 99990 be prepared for a long wait.

    blindman
    So do you use the GUID for the primary key? That creates a large string as the primary key. This would work fine for a relatively small database but would have performance penalties in a large, high-volume system.

    Also, do you use REPLACE or some way to use a 32-character string and not the normal 36-character string returned by NEWID()? The dashes are worthless.

    Code:
    DECLARE @id1 char(32)
    DECLARE @id2 char(36)
    
    SELECT @id1 = REPLACE(NEWID(), '-', '')
    SELECT @id2 = NEWID()
    
    PRINT @id1
    PRINT @id2

  12. #12
    Join Date
    Sep 2003
    Posts
    522
    actually character field is almost as fast when indexed as integer is. but i would allow myself to disagree with blindman on this peculiar design concept with guids on all tables simply because in medium to large systems there may be a need to have those guids in one table to be fk's to let's say 10 other tables. with guids such a table will become a high maintenance (36X10=360 characters of just key fields per row), vs. having integer of 4 bytes (4X10=40 per row).

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    GUIDS aren't 36-character strings; they are just displayed that way. They are actually 16 byte hexadecimal values, and sort very efficiently.

    Yes, they are larger than integers (at 4 bytes) or bigints (at eight bytes), but chances are this still represents only a small portion of the size of the entire record. If you have tables in the multi-gigabyte to terabyte range, I might think twice about GUIDS, but for anything under that their power and convenience is worth an extra 8 bytes per record.

    I've been able to solve a lot of application design problems by using GUIDS, and I see a lot of issues posted on this forum regarding identity values that wouldn't be an issue with GUIDS.

    All this is opinion, of course, so think about your application and decide what is best.

    blindman

  14. #14
    Join Date
    Sep 2003
    Posts
    522
    the following defines the nature of uniqueidentifier data type. unfortunately, contrary to blindman statement, it is not a hexadecimal data type, but binary, that uses hexadecimal values.
    The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites.
    issues posted in this forum may relate primarily to a clumsy, awkward, or otherwise incorrect usage of identity for uniqueness of primary key. resolving design issues as blindman puts it, replacing identity with guid means completely rewriting the entire application, - an undertaking that may last months. it appears his company is very rich if it could afford to wait for months for the entire system to be rewritten.

    the following is from ms site:

    The values are long and obscure. This makes them difficult for users to type correctly, and more difficult for users to remember.


    The values are random and cannot accept any patterns that may make them more meaningful to users.


    There is no way to determine the sequence in which uniqueidentifier values were generated. They are not suited for existing applications that depend on incrementing key values serially.


    At 16 bytes, the uniqueidentifier data type is relatively large compared to other data types such as 4-byte integers. This means indexes built using uniqueidentifier keys may be relatively slower than implementing the indexes using an int key.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •