Selecting alphanumeric combinations that do not exist already intable?????

[rbr]

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 help would be greatly appreciated.
Best regards,
rbr

[Erland Sommarskog]

rbr (ry********@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, es****@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

[rbr]

On Mar 24, 4:15 pm, Erland Sommarskog <[e-mail]:

rbr ([e-mail]) 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, [e-mail]

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

[Erland Sommarskog]

rbr (ry********@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, es****@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

[rbr]

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 <[e-mail]:

rbr ([e-mail]) 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, [e-mail]

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