generating non-numeric primary key

Question

Post reply

generating non-numeric primary key

randerson-917627

SSC Journeyman

Points: 95
More actions
February 13, 2009 at 12:25 pm

#197153

(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 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions