July 26, 2011 at 3:51 pm
Hello,
So I have been tasked with creating a table where the Primary Key is a 10 digit number and is randomly generated with each insert. NEWID() and NEWSEQUENTIALID() are both out of the question because they make GUID's. This is what I have.. which isn't random.
CREATE TABLE [dbo].[Testing](
[TranscriptID] [int] IDENTITY(0000000000,1) PRIMARY KEY NOT NULL,
[Email] [varchar](50) NOT NULL)
Should I be using RAND() somewhere in the first columns line of code?
Thanks for your assistance!
CHADP
July 26, 2011 at 4:31 pm
Does it have to be decimal based or can it be HEX?
Also, why should that key need to be random?
Lastly, will you only ever accept a 10 digit number for that key? In other words, will you possibly have more than 1 billion records stored in this table?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 26, 2011 at 10:43 pm
chadphillips807 (7/26/2011)
Hello,So I have been tasked with creating a table where the Primary Key is a 10 digit number and is randomly generated with each insert. NEWID() and NEWSEQUENTIALID() are both out of the question because they make GUID's. This is what I have.. which isn't random.
CREATE TABLE [dbo].[Testing](
[TranscriptID] [int] IDENTITY(0000000000,1) PRIMARY KEY NOT NULL,
[Email] [varchar](50) NOT NULL)
Should I be using RAND() somewhere in the first columns line of code?
Thanks for your assistance!
CHADP
Gosh... this same problem comes up once in a while and although the problem can be solved pretty easily, it's always an ugly solution that will get slower and slower over time because you have to 1) check to make sure a newly generated number isn't already used in the target table and, 2) if it is, continue to generate new numbers until you find one that hasn't and 3) most people only gen 1 number at a time which is guaranteed RBAR. That might be ok for an insert from a GUI but not for anything else if any kind of scalable performance is sought.
If someone insists, the easiest way to do this in to gen the 10 digit random number and then do an EXCEPT against that from the table. A WHERE NOT EXISTS would do it, as well.
I also don't understand why anyone would ever require such a thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 27, 2011 at 2:40 pm
Jeff Moden (7/26/2011)
I also don't understand why anyone would ever require such a thing.
Hear Hear!!! I often tell business people (which are the ones that usually ask for this kind of silliness) that primary keys are used by the system and therefore are not visible to the end user. This of course is not always 100% accurate but more often than not it stifles this type of stuff. I usually tell people in this situation that I will manage the primary key (identity or other appropriate key) and will generate a unique number that they can use. I also warn about the performance degradation over time. More often than not just pointing out how silly the request is makes it disappear before any code is actually written.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 27, 2011 at 2:46 pm
Why can't it be a GUID? Sounds like the "easiest" solution here. You'll have 99.999999999999999% success on the first try to generate a new unique key and it scales pretty well on large datasets.
July 29, 2011 at 11:02 am
Sean Lange (7/27/2011)
I often tell business people (which are the ones that usually ask for this kind of silliness) that primary keys are used by the system and therefore are not visible to the end user. This of course is not always 100% accurate ...
Thanks God you included the last comment Sean - I was already jumping with the "how about a natural PK?" 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 29, 2011 at 12:42 pm
PaulB-TheOneAndOnly (7/29/2011)
Sean Lange (7/27/2011)
I often tell business people (which are the ones that usually ask for this kind of silliness) that primary keys are used by the system and therefore are not visible to the end user. This of course is not always 100% accurate ...Thanks God you included the last comment Sean - I was already jumping with the "how about a natural PK?" 😀
LOL. WE know there are such things but very few users even have a concept of a primary key so it usually wins the argument. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 29, 2011 at 6:37 pm
PaulB-TheOneAndOnly (7/29/2011)
Sean Lange (7/27/2011)
I often tell business people (which are the ones that usually ask for this kind of silliness) that primary keys are used by the system and therefore are not visible to the end user. This of course is not always 100% accurate ...Thanks God you included the last comment Sean - I was already jumping with the "how about a natural PK?" 😀
Alright, Paul... I'll take the bait. What would YOU use as a "natural PK" on a Customer table? 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
July 30, 2011 at 1:27 pm
chadphillips807 (7/26/2011)
Hello,So I have been tasked with creating a table where the Primary Key is a 10 digit number and is randomly generated with each insert. NEWID() and NEWSEQUENTIALID() are both out of the question because they make GUID's. This is what I have.. which isn't random.
CREATE TABLE [dbo].[Testing](
[TranscriptID] [int] IDENTITY(0000000000,1) PRIMARY KEY NOT NULL,
[Email] [varchar](50) NOT NULL)
Should I be using RAND() somewhere in the first columns line of code?
Thanks for your assistance!
CHADP
Before going any further, note that 10 digit integers won't fit into int. To cover 10 digits you need bigint.
Or do you mean only small 10 digit integers?
The second point is that autogenerating a unique random number on insert needs some code; how complex may depend on whether you have to cope with insert statements that insert more than a single row. Where is that complex code going to be? If I interpret "with each insert" literally I reach the conclusion that you have to do it in an instead of trigger, and either disable server trigger recursion or do the inserts on a view of the table rather than directly on the table (the insert inside the trigger will be on the table itself, not the view, so it doesn't cause the trigger to be called again). If messing around with an instead of trigger is not what you want to do, you have to insist that inserts are done as stored procedure calls, to have somewhere to put the code.
If you pick a number, say 10000000 and another number co-prime to it and quite a lot smaller you can produce an efficient PRNG (with good randomness, but no cryptographic strength) which ranges over all positive integers less than 10000000, which will give you all possible 10 digit values (so less than 2 billion values, but to do that you have to go to bigint instead of int. If you keep a single row single column bigint table with the current seed in it (a bigger seed table if you want to use anything like a mersenne twister as the PRNG, but that would probably be over the top) you can have an extremely efficient PRNG, and you can make it an inline table-valued function for SQL efficiency. If you aren't allowed a table to hold seed data, you are into inefficient existence checking in a loop - no hope of efficiency there.
So probably you should go back to whoever set this task and find out whether it's a real requirement or something else (eg would they be happy with a separate function that delivers a random integer not already used as a primary key that has to be called before doing an insert and all inserts being restricted to single row), and if it's real is the requirement then is it acceptable for all inserts to be done by calling a stored procedure (and NO application or users other than the the DBAs have direct insert access to the table). If it's a real requirement and the stored procedure approach isn't acceptable, you are stuck with doing it in an instead of trigger, which is not particularly difficult provided you can switch trigger recursion off (or use a view for all inserts). But you may find that they also object to using a view for the update, but want to have server trigger recursion switched on, in which case it can't be done.
Tom
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply