February 21, 2011 at 4:05 am
How to create a primarykey like REC_1,REC-2 and so on?
February 21, 2011 at 4:33 am
Let's begin with the question "Why?":
What would be the benefit to add a set of character before a number? Please explain the business case in more detail so we can try to understand itt.
Now to the technical drawbacks you'll have to deal with:
Let's assume you have the records Rec_1, Rec_2, Rec_3, Rec_20 and Rec_100. How would you find the latest record?
Furthermore, you'd need to have some sort of a Sequence table to make sure you'll not end up with duplicates (a SELECT MAX() won't work here due to the effect described above).
But if you insist in having such kind of a column, you could add a computed persisted column that would add "Rec_" to a numeric identity column.
As a side note: I assumed REC_1,REC-2 is just a typo. Otherwise we'D need the rule when to use an underscore vs. a dash.
February 21, 2011 at 5:43 am
I'm with Lutz. I think you'd be better off using an Identity column, even make that the primary key, and then have a calculated column or just put it into a view to put the 'REC-' in front of the value when you retrieve it from the database.
Another point, let's assume this primary key is the main access path for the table, so it makes sense for it to be the clustered index on the table as well. You've got a bit of a funky cluster with the first four values in every key being identical it's going to reduce the effectiveness of the key, just a tad. It's not something I've experimented with, but I'm sure it would make a difference.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply