The benefits of random numbers are distribution and unpredictability. If you don't need those, then asking for randomness just makes your life more difficult.

James Goodwin (8/13/2010)

---

Jeff,
An arbitrary number is a number that you don't care how it is generated. As in the example in my previous post, since you just need an arbitrary number you can use Rank() to generate the numbers. They're not random, but they solve the deidentification issue presented.

A random number, on the other hand, is a number generated through a random (or pseudo-random) process. This means that any number has an equal chance of coming up on any given test and therefore a set of random numbers is not guaranteed to contain unique numbers. The benefits of random numbers are distribution and unpredictability. If you don't need those, then asking for randomness just makes your life more difficult. Putting constraints (such as uniqueness) on a set of random numbers makes the set less random.


To see why asking another person for a random number is bad, see http://scienceblogs.com/cognitivedaily/2007/02/is_17_the_most_random_number.php

--
JimFive

Bert De Haes (8/13/2010)

---

You can easely resolve the "unique" problem by adding a distinct to the select top().
I tried this, and it works fine on SQL 2005. The distinct will not generate an ordered list ... ( you can use newid() or rand() )

CREATE TABLE dbo.#RandomTaxID (TaxID int);
GO
INSERT INTO dbo.#RandomTaxID (TaxID)
SELECT distinct TOP (70000) 
 RIGHT(CAST(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT) AS VARCHAR(30)),9)
 FROM dbo.Tally;
 
ALTER TABLE dbo.#RandomTaxID ADD MyID int Identity(1,1) NOT NULL primary key clustered;
go
-- verify uniqueness :
create unique index UQ_RandomTaxID on #RandomTaxID(TaxID);

select top 10 * from #RandomTaxID order by MyID;

TaxID MyID
514443407 1
959020860 2
287024448 3
269261913 4
575137863 5
423204402 6
566434316 7
66537729 8
82584852 9
752384001 10

Brandie Tarvin (8/13/2010)

---

Oh. Now I get to play with more of your code. Thanks, Jeff. @=)

pete callaghan (8/22/2010)

---

Support the animated giff comment - they get do very old very quickly.

Brandie Tarvin (8/23/2010)

---

I had no idea what that comment was supposed to mean. Since you replied, maybe you can explain it to me? @=)

PS - I wish some people would have pity on my underpowered netbook and its tiny screen trying to render these pages and go easy on the animated gifs and 10 line sigs attached to one word comments

jcrawf02 (8/23/2010)

---

Related to the PS on magarity's post