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