Code generation

Great explanation, Jeff! That's one thing I wanted to ask.

It's strange to see that partition by seems to be faster than distinct. I would have expected to be equally fast (or slow).

Just out of curiosity, how did you come up with this? I suppose this is something you already had in your toolbox.

I'm not 100% sure of the "internals" but it seemed logical to me that the partition would do a single sort whereas DISTINCT has to check each value against a range of other values and then, if within the range, check for the actual value. I'm no expert on the internals so rather than guess, I tried both. The partitioning won. 😉

With random numbers with such a relatively small domain, someone could get lucky with a guess. If you were to use a full NEWID(), instead, it would be like someone guessing the exact linear mile of travel you were in across a diameter of something like 17 quadrillion Milkyway galaxies.

It would be a good test, as well. If you can't successfully and correctly enter a 36 character code, you don't deserve to win. 😛

Maggo is correct... if you're going to use these codes for such a contest, codes with repeating adjacent characters should be eliminated and to prevent "mistrakes", things like lower case "L" and "1" and upper case "0" and zero should be eliminated from the mix. That's actually a lot easier to do than what most people would think although it does take a little extra time to generate. I'll play with this tonight because it IS an interesting problem. I've got a "random password generator" bit of code that I can steal a bit of code from.

Someone will likely beat me to it, though... I've got to get to work. 🙂

Heh... I guess there were no takers while I was at work. Ok, no problem. We've got this covered.

Thorsten, I don't know what your company makes or sells but I'm thinking they owe me a bit for this one. 😉 Ask them if they can send me a couple of boxes of whatever they make. I'm sure that I can find someone or a homeless shelter in the area that needs whatever they sell. Or, if they want, they can make a donation to their favorite charity in my name, please.

The whole thing takes 00:01:40 to run on my 8 year old single P4 CPU desktop and does a lot of things mentioned on this thread. Because random letters frequently spell out swear words, I added a "fix" for that little problem, as well. I also have it pick the "winning" code and store it in a table as well as storing the 1.6 million random codes in another table. If you need more than 1 winning code, then change the TOP 1 to TOP n where "n" is the number of winning codes you want the machine to pick. The winning code table also has a "Place" number in case of such an eventuality. The random code table also has an IDENTITY column so whoever prints up inserts can keep track of what they printed.

We could check against the previous random code list you have but I'm going to hold out for a couple of more boxes of whatever your company sells for a homeless shelter. 😉

Here's the code... as usual, the details are documented in the code itself...

--===== Conditionally drop the permanent random code and winner tables

-- Uncomment if you want to test for reruns

IF OBJECT_ID('dbo.jbmRandomCode','U') IS NOT NULL

DROP TABLE dbo.jbmRandomCode

;

IF OBJECT_ID('dbo.jbmWinnerCode','U') IS NOT NULL

DROP TABLE dbo.jbmWinnerCode

;

--===== Generate 1.6 Million random codes where no two adjacent characters

-- are alike. We also remove all vowels to keep from accidently

-- spelling swear words and we remove things that could be mistaken

-- for each other such as:

-- Capital "O" and "0" (zero)

-- Lower Case "L" and "1" (one)

-- Lower Case "o" removed just to avoid all confusion

-- Jeff Moden

WITH

cteBytes AS

( --=== Generate 2.2 Million random 8 byte numbers.

-- We need this many so that when we remove numbers that

-- have identical adjacent characters and any dupes,

-- we still have enough to gen 1.6 Million random codes.

-- This is done at the byte level because NEWID is expensive to run.

-- If we ran 8 individual NEWID()'s for each code, this code would

-- take a lot longer.

SELECT TOP 2200000 CAST(NEWID() AS BINARY(8)) AS Bytes

FROM Master.sys.All_Columns ac1

CROSS JOIN Master.sys.All_Columns ac2

)

,

cteCharacters AS

( --=== This is just the list of characters that we have to chose from.

-- Vowels and certain other characters have been removed to avoid confusion.

-- Note that both strings must be absolutely identical here.

SELECT LEN('BCDFGHJKLMNPQRSTVWXYZbcdfghjkmnpqrstvwxyz23456789') AS CharLen,

'BCDFGHJKLMNPQRSTVWXYZbcdfghjkmnpqrstvwxyz23456789' AS Characters

)

,

cteChars AS

( --=== This takes each byte from the random 8 byte binary number and converts each into

-- a human readable character by using the random byte to select from the allowed

-- character list. Note that "modulo" (%) plays an important role here.

SELECT SUBSTRING(c.Characters,CAST(SUBSTRING(b.Bytes,1,1) AS INT)%c.CharLen+1,1) AS Char1,

SUBSTRING(c.Characters,CAST(SUBSTRING(b.Bytes,2,1) AS INT)%c.CharLen+1,1) AS Char2,

SUBSTRING(c.Characters,CAST(SUBSTRING(b.Bytes,3,1) AS INT)%c.CharLen+1,1) AS Char3,

SUBSTRING(c.Characters,CAST(SUBSTRING(b.Bytes,4,1) AS INT)%c.CharLen+1,1) AS Char4,

SUBSTRING(c.Characters,CAST(SUBSTRING(b.Bytes,5,1) AS INT)%c.CharLen+1,1) AS Char5,

SUBSTRING(c.Characters,CAST(SUBSTRING(b.Bytes,6,1) AS INT)%c.CharLen+1,1) AS Char6,

SUBSTRING(c.Characters,CAST(SUBSTRING(b.Bytes,7,1) AS INT)%c.CharLen+1,1) AS Char7,

SUBSTRING(c.Characters,CAST(SUBSTRING(b.Bytes,8,1) AS INT)%c.CharLen+1,1) AS Char8

FROM cteBytes b

CROSS JOIN cteCharacters c

)

,

cteRandomCode AS

( --=== Now we take all the individual letters and slam them together to make a random code.

-- In the process, we make sure that no two adjacent characters are alike.

SELECT Char1+Char2+Char3+Char4+Char5+Char6+Char7+Char8 AS RandomCode

FROM cteChars

WHERE Char1<>Char2 AND Char2<>Char3 AND Char3<>Char4 AND Char4<>Char5 AND Char5<> Char6 AND Char6<>Char7 AND Char7<>Char8

)

,

cteNumberDupes AS

( --=== We need to remove dupes and this numbers one or more occurances of the same code

-- with an incrementing number to count occurances of that same code.

SELECT ROW_NUMBER() OVER (PARTITION BY RandomCode ORDER BY RandomCode) AS Occurance,

RandomCode

FROM cteRandomCode

) --=== This selects the top 1.6 million first occurances of all the codes we haven't

-- rejected so far. Since we only select the first occurance of any given code,

-- this effectively removes dupes and does so much faster than using DISTINCT.

-- The final result is stored in a new table called dbo.jbmRandomCode.

SELECT TOP 1600000

IDENTITY(INT,1,1) AS RowNum,

RandomCode COLLATE Latin1_General_BIN AS RandomCode --For case sensitivity

INTO dbo.jbmRandomCode

FROM cteNumberDupes

WHERE Occurance = 1

;

--===== And now, pick what will be the winning number and store it in a new table.

-- Change the TOP 1 to however many 1st, 2nd, 3rd, etc place winners you want.

SELECT TOP 1

IDENTITY(INT,1,1) AS Place,

RandomCode COLLATE Latin1_General_BIN AS RandomCode --For case sensitivity

INTO dbo.jbmWinnerCode

FROM dbo.jbmRandomCode

ORDER BY NEWID()

;

{edit} Added brute force case sensitivity using COLLATE Latin1_General_BIN just to make life a little easier...

I've incorporated some of the good ideas from your post into the new code I posted above. Thanks, Magoo. Using the characters chosen for the codes (49 in all) and removing any codes having duplicate adjacent letters, it works out to about 33 trillion combinations. Heh... we should start a lottery so old DBA's can retire. 😛

Brilliant answer Jeff!

I like this a lot, but every time I see NEWID() used like this it just makes me yearn for a decent random number generator so that you could just

select top 1600000 Number from RandomNumbers

:unsure: