SQL Server generate unique value

Question

SQL Server 10, .NET, C#

This is similar to this question but not exactly the same.

When a user creates new event on my event-scheduling web site, a unique sort-of easy to remember code is to be generated, so this code can be communicated (via the phone, let's say) to the participants. This generated code looks something like:

JohMonBlue5
<abbriviated name><abbriviated week day><some uniquefying suffix><index>

Yes, I could just e-mail a link, but I suppose that's not an option.

Note: I chose to use uniquefying suffix that is a color, since I think it's easier to remember color, than a number. Still I use number in case of a collision.

Question: What's a good way to implement this (I have unique constraint on the column already):

1. I can generate the index on the app side, and use try { insert } catch { retry }. In this case, how do I tell "Duplicate column value" exception from any other exception? by message text?
2. Use something in the MSSQL Server to increment trailing number until uniqueness reached.

Answer 1

I suggest not using the catch to handle your duplicates exclusively. Catch should only be used for true errors and besides that, exception catching is slow and expensive. It would be better to generate a value and test to see whether it already exists via a query rather than allowing an exception to get thrown.

I would also implement the generator in one place. Don't split it by creating a color on the client and the number in the db. Pick one side or the other. On the Sql Server side a good way of implementing this type of feature is to use a UDF. Perhaps you can create a table of keywords (colors in your case) and randomly select from it and then perform your increment for uniqueness.

I also think that you don't have to o colors exclusively. Choose any words that are simple and easy to remember. Dragon, Friday, Blue, House...