Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Finding Unique Non-Repeating Random Numbers Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, August 11, 2010 9:56 PM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 11:26 AM Points: 7,502, Visits: 8,651
 Comments posted to this topic are about the item Finding Unique Non-Repeating Random Numbers Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #967923
 Posted Thursday, August 12, 2010 1:17 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, November 19, 2013 3:22 AM Points: 2, Visits: 21
 Regarding Method 1: Running this code against a table with 55,000 records took me 4 minutes (longer if you have more columns in your table).I currently have a table with 17 columns, containing 278,998 records. The "key" field (used for URLs) is a varchar(8) filled with random alphanumeric strings. Creating a new unique key, using the loop method, takes 0.0620035 seconds according to Management Studio. Why would the amount of columns have anything to do with it? (Did you create an Index on the TaxID column?)ByeA.
Post #967962
 Posted Thursday, August 12, 2010 2:13 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, August 12, 2010 7:13 AM Points: 5, Visits: 16
 I know this is a much more 'techie' type of solution, but for an alternative to your methods, have a look at this:Linear Feedback Shift RegistersLFSRs are basically very simple, very fast pseudo-random number generators that generate a set of unique, non-repeating (but cyclic) numbers.This is possibly not a solution for implementing in T-SQL, but if you're able to use C# (or maybe even C++) assemblies, it might just work for you.Alex.
Post #967984
 Posted Thursday, August 12, 2010 2:27 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, August 23, 2010 9:20 AM Points: 3, Visits: 18
 Great article! I found it as I was looking for a solution to a slightly different problem. Creating a table seating chart for 70 people who are seated seven to a table, there are 10 tables. The goal is to have everyone meet each other at the end of ten rounds, each round is seven minutes. So I am looking to show a matrix of numbers in column row format from 1 to 70. 7 rows deep, 10 columns wide. The seven rows represent a 'table' of people, each person represented by a number. The first row of numbers one to seven are the seven people sitting at that table. 8 - 14 the people at the second table, etc up to the last table - numbers 64 -70. This is were they are start.From there each iteration, or next round would represent shifting the numbers to show a new set of rows with unique numbers, but different from the previous set and so forth till every number has been matched with another. So number one would have been matched with 2 - 70, and two would have been matched with one - 70, etc.I have been looking for scripts that already exisit for this, no luck yet. Any help would be greatly appreciated and of course the solution credited to the author. HELPUsing SQL to solve this problem is vexing me to no end. I am an intermediate SQL user.
Post #967988
 Posted Thursday, August 12, 2010 2:39 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, February 24, 2015 3:26 PM Points: 21, Visits: 100
 Great post. Good example of use of random numbers.Rand versus NewID has been discussed. Rand only runs once per query, NewID is like other functions, but requires conversion and is not proven to have good distributions and seems a bit slow.An alternative is to union a bunch of "select Rand()" statments to create the list of unique random values.Select top 10 from(select cast(rand()*100000 as int) as rnd unionselect cast(rand()*100000 as int) as rnd union...select cast(rand()*100000 as int) as rnd unionselect cast(rand()*100000 as int) as rnd) as tUnion All if you want non-unique numbersit may be a long sql query, but its very fastBTW if the multiple can easily be a count(*) from some other table, instead of a constant.
Post #967993
 Posted Thursday, August 12, 2010 3:10 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, August 13, 2010 1:57 AM Points: 4, Visits: 12
 There is a terminolgy issue here. The numbers in this sequence are not actually random as we could predict the next number in the sequence by looking at the previous values, the accuracy of this prediction would get better as we went along.For example, if there were 9 numbers in the range and we specified that they would not repeat then we might get a sequence3,2,5,4,7,8,1,6The next number would have to be 9 as it's the only number left in the sequence.I'm not sure if what you described would clasifiy as pseudo random but I know that the Linear Feedback Shift Register technique definately is.However, with regards to generating test data both these techniques are great and it's a well written article so many thanks.Other functions to play with with regards to generating test data are the HashBytes function and CHECKSUM function which both have the useful property of being able to take a string and generate a unique value from it.So for example Checksum('John Doe') would always return the same value.See also:http://msdn.microsoft.com/en-us/library/aa175776(SQL.80).aspx
Post #968013
 Posted Thursday, August 12, 2010 4:22 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 11:26 AM Points: 7,502, Visits: 8,651
 wppatton (8/12/2010)I have been looking for scripts that already exisit for this, no luck yet. Any help would be greatly appreciated and of course the solution credited to the author. HELPHave you posted this problem to the T-SQL Forums yet? There are several people lurking there who love assisting with problems that might not be reading the this particular thread.Just an FYI: It would help for you to post what you've already come up with in terms of table structure, data, and the actual T-SQL code. People around here help with problems, they don't solve the entire problem for you. If you can show you've been making an effort, that will get you a lot more responses than if you just post the problem and wait for someone else's solution. Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #968048
 Posted Thursday, August 12, 2010 4:24 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 11:26 AM Points: 7,502, Visits: 8,651
 Alexander-1013105 (8/12/2010)LFSRs are basically very simple, very fast pseudo-random number generators that generate a set of unique, non-repeating (but cyclic) numbers.This is possibly not a solution for implementing in T-SQL, but if you're able to use C# (or maybe even C++) assemblies, it might just work for you.It's definitely something to keep in mind, but for my solution, I needed T-SQL code since I'm restoring backups to dev environments and "washing" the numbers before letting the developers & testers at the back end data.Thanks for the link, though. I like hearing about new stuff. Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #968049
 Posted Thursday, August 12, 2010 4:28 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 11:26 AM Points: 7,502, Visits: 8,651
 alexander.schaaf (8/12/2010)Why would the amount of columns have anything to do with it? (Did you create an Index on the TaxID column?)Answering the questions backwards:2) No, I did not create an Index on TaxID. I can't because it belongs to a table that already has more than enough indexes on it as it is. And the index wouldn't be used because of this.1) In my experience, wider tables actually do consume more processing resources. Part of that, though, is the design of our database and the fact that our tables aren't just wide, but super long. You can have a wide, short table that processes fast, or a narrow, long table that processes fast. But put the wide and long together and you've just created a monster that takes forever to update. Especially when (as I said in #2) you have developers that like to index every column they call in query.We're working on breaking that habit, but it's a long row to hoe. @=) Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #968052
 Posted Thursday, August 12, 2010 5:27 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, August 13, 2010 1:57 AM Points: 4, Visits: 12
 I've tried to implement a LFSR as follows but it's not quite working. But in principle it should be possible.set nocount ondeclare @lfsrTable table(period int,value int NOT NULL)declare @lfsr intdeclare @period intdeclare @nextbit intset @lfsr = 1set @period = 0while (@period=0 or @lfsr <> 1)begin -- taps: 16 14 13 11; characteristic polynomial: x^16 + x^14 + x^13 + x^11 + 1 set @nextbit = ((@lfsr & 65536)/65536) ^ ((@lfsr & 16384)/16384) ^ ((@lfsr & 8192)/8192) ^ ((@lfsr & 2048)/2048) set @lfsr = ((@lfsr * 2) & 65535) + @nextbit set @period = @period + 1 insert @lfsrTable values(@period,@lfsr)end set nocount offselect * from @lfsrTable
Post #968079

 Permissions