February 13, 2009 at 12:25 pm
(This is one possible solution to a problem I'm facing. It is not one that I normally would want to use, but it would be nice to have all the facts before ruling anything out, so here goes.)
What might be the best way to auto-generate a string for a primary key? it would need to have at least partial meaning. For example: 'mar_emp_2899763', where the characters are hardcoded (how, not sure) and the numeric portion is auto-generated (again, if not an identity column I'm not sure how to generate a sequential number). I suspect that the correct answer for this is to use two columns.
Gory background details:
The reason I can't just use an identity: There are several tables involved, which I can't consolidate, that end up feeding a vendor app. The vendor app outputs some results data and I need to be able to relate the results back to the original record. The vendor only lets us supply an "ID", or else I'd give the ID plus a code indicating the original table. So I was thinking to combine an autonumber with a code that I can pull apart to find the original record.
There's some other alternatives that would make this unnecessary but it's kind of a matter of curiousity now if nothing else 🙂
February 13, 2009 at 12:32 pm
well...you can have a calculated column as a primary key...so i'd make it built off of an identity like this:
[font="Courier New"]CREATE TABLE #example(myid INT IDENTITY(1,1),
myCalculatedPK AS 'martmp_' + RIGHT('00000000' + CONVERT(VARCHAR,myid),8) persisted PRIMARY KEY,
myotherstuff VARCHAR(30) )
INSERT INTO #EXAMPLE(MYOTHERSTUFF)
SELECT 'APPLES' UNION
SELECT 'BANANAS' UNION
SELECT 'GRAPES'
SELECT * FROM #example
DROP TABLE #example
[/font]
results:
myid myCalculatedPK myotherstuff
1 martmp_00000001 APPLES
2 martmp_00000002 BANANAS
3 martmp_00000003 GRAPES
Lowell
February 13, 2009 at 9:24 pm
I agree with Lowell... otherwise, you'd have to resort to some nasty trickery like MAX or a sequence table and both will cause a world of hurt.
--Jeff Moden
February 13, 2009 at 9:26 pm
Jeff Moden (2/13/2009)
I agree with Lowell... otherwise, you'd have to resort to some nasty trickery like MAX or a sequence table and both will cause a world of hurt.And since, once established, it is not likely to change, you should look into the option of PERSISTED.
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply