I have worked on a project that required a random number, which had to be created within a certain range and be unique within the table that it’s going to be stored in. The uniqueness of the number only needed to apply to numbers that were marked as active within the table. Someone used a .Net approach whereas I tried using both SQL functions and procedures both of which worked in the same manner.
The method used within SQL Server would take a guid,(generated by the NewID) then strip out the numbers and this was then used as a starting point. If the number was not free it would then move up one and down one at a time to see if the number was free. This worked fine until the range of numbers free started to become limited.
The best method which is still within SQL server was to create a table with all the numbers within the range and mark them as free. Then select the top 1 of all the numbers that are marked as free, this will allow the number to be unique, but order them by using the NewID function which will cause the number to come out in a random order. Then take the number, mark it as in use until it becomes free again.
Via http://blogs.conchango.com/stevewright/archive/2006/09/14/Creating-unique-random-number-generator-for-SQL-Server_2E00_.aspx