Hello, I'm looking for suggestion on how to get the DB to auto generate Ticket numbers (preferably via the SQL DB) for a varchar column. I have the following tables in the DB: Activities & Cases and would prefer the format to be "Act000001" or "Cse000001". This would be something similar to the identity column property.
Any suggestion would be highly appreciated.
Thanks. Rusty
Trigger.
Whatever, do NOT USE THIS AS PRIMARY KEY - on SQL Server you just have a hugh performance hit.
Put in a normal identity column as PK, then use this as normal business property (unique index on it).
You can generate them via trigger, stored procedure, whatever you like. Pregenerate, postgenerate. Whatever you like.
If you can handle the inevitable missing numbers from rollbacks, etc. and if you only need one prefix per table, I suggest an identity column (to create the numbers) and a calculated column to add the letters and leading zeros to the identity. If more complex than that, I suggest a trigger. This should not be handled by the application only or you will eventually have records that someone had to manually enter that are messed up. You also need to handle race conditions, so design carefully.