SQL Server Performance

Generate random number for PK?

Discussion in 'General Developer Questions' started by DilliGrg, Jul 19, 2007.

  1. DilliGrg Member

    I believe it's not a good idea at all but I am exploring the possibility of generating a unique random numbers with fixed length barcode for every record and define this as primary key based on the Front End developer's request. What are the other good alternatives to define table with primary key besides defining as identity. Since identity will not have same data length as required for this, I can't use identity as Item_Barcode_Number for primary key. Any suggestion?<br /><br />TableStructure:<br /><pre id="code"><font face="courier" size="2" id="code"><br />MyTable<br />Item_Barcode_Number int PRIMARY KEY<br />Item_Name varchar(50)<br />Item_Description varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> = NULL<br /></font id="code"></pre id="code"><br /><br />The variable length random numbers can be generated like this but how Can I generate fixed length (let's say 9 charaters: 111111111 - 999999999?) each time.<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />--|Generate random numbers<br />DECLARE @RandomNumber int;<br />DECLARE @Max int;<br />DECLARE @Min int<br /><br />SET @Min = 1 -- The lowest random number<br />SET @Max = 999999999 -- The highest random number<br />SELECT @RandomNumber = ROUND(((@Max - @Min -1) * RAND() + @Min), 0)<br />SELECT @RandomNumber<br /></font id="code"></pre id="code"><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><br />Name<br />--------- <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">
  2. satya Moderator

    The output of the RAND function will always be a value between 0 and 1. If you want to generate a random integer number, all you have to do is multiply it by the maximum value you want generated and then get rid of the decimal places. One way of getting rid of the decimal places is by CASTing it to INT. Also BOL suggested that Repetitive invocations of RAND() in a single query will produce the same value."

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. DilliGrg Member

    First of all congratulations for reaching over 18000 posts.

    Ok the issue of generating random number here is the FIXED LENGTH. Also, let's take this example which generates the variable length with max value as defined below. Is there a way to make this as fixed lengh of 9 digits every time? I believer once this number reaches to the max, it will start repeating some of the earlier numbers. Probably this is a bad approach to define as primary key value.

    SELECT CAST(RAND() * 999999999 AS INT) AS [RandomNumber]

    Dilli Grg

    (1 row(s) affected)

  4. Adriaan New Member

    Why cause yourself so much headache? A value on an identity column is as much an arbitrary key as a random value would be.
  5. satya Moderator

    Thanks Dilli[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />Will the length be 9 digits on this table?<br />If you don't want to use an auto-numbering column but still want a single column, numeric key you'll have to either use random numbers or something along these lines.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br />@<a target="_blank" href=http://www.askasqlguru.com/>http://www.askasqlguru.com/</a><br /><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  6. Chappy New Member

    If you want it to bge random, then it clearly has no defined purpose, other than to act as an identifier
    And in that case, identity will be perfect

    Using random as a PK is not good at all. It will cause lots of page splits over time
    Also you would need to keep looping and generating a new random number, until you find one that hasnt been used yet. As the database grows this could take longer and longer

  7. DilliGrg Member

    Thanks Everyone for your comments/suggestions. Like I mentioned it is not my preference to use the random number as primary key but it is kind of business requirement unless there are better alternatives. So what our application developers are trying to do is generate unique barcodes with fixed lengh of 9 characters everytime, otherwise it will not work on the skew of the page, to use to read while faxing the pages. I don't see any good use of this random functionality in this case. I just wanted to have experts' opinion before I decide not to use. I agree with Chappy's assessment of using random numbers. So, are there any alternatives to this? Thanks again for your valuable suggestions.


    Dilli Grg

    (1 row(s) affected)

  8. Adriaan New Member

    You can use a calculated field to add leading zeroes to the identity value to complete the 9 positions, like this:

    CREATE TABLE dbo.MyKey
    (MyIdentityKey INT IDENTITY(1,1),
    MyCharacterKey AS RIGHT('00000000' + LTRIM(CAST(MyIdentityKey AS VARCHAR(9))), 9))

    Note that you must also add a constraint on the identity column, in that it must be smaller than 1,000,000,000.
  9. hongdida New Member

    "So what our application developers are trying to do is generate unique barcodes with fixed lengh of 9 characters everytime", what do you mean by saying unique barcode? not the common barcode types, like upc a or ean-8?

Share This Page