Hello all, I have an odd requirement. I have a column with a system
generated username that is a 6 character, alphanumeric, field. These
usernames are randomly generated by code. I need to create a stored
procedure that will return all combinations that are not already
being
used. Maybe the result of still trying to wake-up from a long
weekend.
But, I cannot think of an easy way to do this.

Any would be greatly appreciated.


Best regards,


rbr

That will not be a fast check. There are 26 letters in the English alphabet
(assuming we count lower case). There are 10 digits. The number of
combinations for a 6 position user name is (number_of_chars ^ length), or in
this case (36 ^ 6), which is 2,176,782,336 combinations. If you add the
upper case letters, then there are 56,800,235,584 combinations (62 ^ 6).

Here is one method to generate all possible combinations. It is for all
lower case letters and digits. You can uncomment the OR condition to add the
upper case letters. And this does not make any assumptions that the names
may start with letter only, etc.

CREATE TABLE Chars (c CHAR(1))

INSERT INTO Chars (c)
SELECT CHAR(number)
FROM master..spt_values
WHERE type = 'P'
AND (number BETWEEN 48 AND 57
-- OR number BETWEEN 65 AND 90
OR number BETWEEN 97 AND 122)

CREATE TABLE Users (usr CHAR(6))

INSERT INTO Users (usr)
SELECT I.c + II.c + III.c + IV.c + V.c + VI.c
FROM Chars AS I
CROSS JOIN Chars AS II
CROSS JOIN Chars AS III
CROSS JOIN Chars AS IV
CROSS JOIN Chars AS V
CROSS JOIN Chars AS VI

Then you can simply use the the EXCEPT operator in SQL Server 2005 or NOT
EXISTS to check against the list of existing user names and return only
those that are not in use.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

>> I need to create a stored procedure that will return all combinations that are not already being used. <<

This is not a good idea; do the math. In the old UNIX systems, we had
a password generator that created 6-8 letter words that were
pronounceable but non-sense. Compuserve used a two-word pass phrase
that was long but easy to remember -- "purple bagpipes", "vividly
moose", etc. Then there are "bingo card" systems that are quite
safe. Just what do you want to do?

rbr (ryankbrown@gmail.com) writes:
> Hello all, I have an odd requirement. I have a column with a system
> generated username that is a 6 character, alphanumeric, field. These
> usernames are randomly generated by code. I need to create a stored
> procedure that will return all combinations that are not already
> being
> used. Maybe the result of still trying to wake-up from a long
> weekend.
> But, I cannot think of an easy way to do this.

All? There are 2176782336 combination, so unless a large number of the
combinations are already in use, that will be a huge result set.

I started to compose a solution that would return 1000 random codes
currently not, but I realised that it was not trivial to write. So I
abandoned it for the moment, as I may not solve the right problem for
you.

Of course, if you store all 2176782336 combinations in a table, it's
trivial. But would be over 20 GB in size... (Have to count with some
8-9 bytes overhear per row.)

Anyway, if you could be a little more specific, that would be nice.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Mar 24, 4:15 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> rbr (ryankbr...@gmail.com) writes:
> > Hello all, I have an odd requirement. I have a column with a system
> > generated username that is a 6 character, alphanumeric, field. These
> > usernames are randomly generated by code. I need to create a stored
> > procedure that will return all combinations that are not already
> > being
> > used. Maybe the result of still trying to wake-up from a long
> > weekend.
> > But, I cannot think of an easy way to do this.
>
> All? There are 2176782336 combination, so unless a large number of the
> combinations are already in use, that will be a huge result set.
>
> I started to compose a solution that would return 1000 random codes
> currently not, but I realised that it was not trivial to write. So I
> abandoned it for the moment, as I may not solve the right problem for
> you.
>
> Of course, if you store all 2176782336 combinations in a table, it's
> trivial. But would be over 20 GB in size... (Have to count with some
> 8-9 bytes overhear per row.)
>
> Anyway, if you could be a little more specific, that would be nice.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I have considered the speed and space issues. Unfortunately, there are
over 14 million usernames currently in use and another 10 million
reserved for currently inactive customers. The process of generating
names and checking if they are taken is getting tougher. The idea my
boss had was to create a table (once) of unused usernames and randomly
pick one from the list each time. If this is not feasible (you all
make very good points) I would be more than happy to tell him so.
However, I figured it would be worth investigating before saying it
was impossible.

Best regards,

rbr

rbr (ryankbrown@gmail.com) writes:
> I have considered the speed and space issues. Unfortunately, there are
> over 14 million usernames currently in use and another 10 million
> reserved for currently inactive customers. The process of generating
> names and checking if they are taken is getting tougher. The idea my
> boss had was to create a table (once) of unused usernames and randomly
> pick one from the list each time. If this is not feasible (you all
> make very good points) I would be more than happy to tell him so.
> However, I figured it would be worth investigating before saying it
> was impossible.

Impossible it isn't. But it's definitely a waste of disk space.

Even if you have 24 million codes already taken, that's only a little
more than 1% of the total space consumed, so I think that if every time
you need a code, generate a random code, check if it is in use this
should be efficient enough. For about each 10000 customer you will have
to generate three codes before you have an unused code.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Very good. Thank you all very much for the feedback. It is much
appreciated.

Best regards,

rbr




On Mar 25, 4:30 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> rbr (ryankbr...@gmail.com) writes:
> > I have considered the speed and space issues. Unfortunately, there are
> > over 14 million usernames currently in use and another 10 million
> > reserved for currently inactive customers. The process of generating
> > names and checking if they are taken is getting tougher. The idea my
> > boss had was to create a table (once) of unused usernames and randomly
> > pick one from the list each time. If this is not feasible (you all
> > make very good points) I would be more than happy to tell him so.
> > However, I figured it would be worth investigating before saying it
> > was impossible.
>
> Impossible it isn't. But it's definitely a waste of disk space.
>
> Even if you have 24 million codes already taken, that's only a little
> more than 1% of the total space consumed, so I think that if every time
> you need a code, generate a random code, check if it is in use this
> should be efficient enough. For about each 10000 customer you will have
> to generate three codes before you have an unused code.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx