Increment varchar primary key? or convert varchar primary key to seeded integer

Question

Increment varchar primary key? or convert varchar primary key to seeded integer

Isaac Vallee

SSC Rookie

Points: 41
More actions
August 30, 2012 at 4:04 pm

#266052

Hey all,
I have a table with a varchar(10) primary key. This value is determined and input by the user via web page.
This field is no longer needed to be populated by the user and will be hidden on all web pages.
example values: A057, A174, A175, B298, R159, R999, etc...
The value doesn't matter, so I am wondering for an INSERT, how I can auto-increment the field.
How/Can I increment a varchar field? ie X001 -> X002, X003, X004, etc...
or can I convert the field to an int with identity and increment of 1?
I can't access the DB directly so anything will need to be done via scripts that our DBA executes.
Any help you can provide would be greatly appreciated.
THanks.

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

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

These quotes are slightly re-arranged for pertinance and organization, so please bear with me.

Isaac Vallee (8/30/2012)
I have a table with a varchar(10) primary key. This value is determined and input by the user via web page.
example values: A057, A174, A175, B298, R159, R999, etc...
This field is no longer needed to be populated by the user and will be hidden on all web pages. The value doesn't matter, so I am wondering for an INSERT, how I can auto-increment the field.

With great difficulty, however, I'm confused.

I can't access the DB directly so anything will need to be done via scripts that our DBA executes.
or can I convert the field to an int with identity and increment of 1?

This confused me at first. Anything you do will need to be done via script with your DBA, or you cannot make changes to the database itself?

If you can't make db changes, you're not going to be able to adjust this issue.

Automating incrementors like this are fraught with peril from concurrency concerns. There are tricks to deal with it but you'll need to make it pretty robust. Before you travel down that path, what are the concerns about adding a new column that's a normal surrogate key (TableID INT IDENTITY( 1,1)) and translating all your foreign keys to look at that instead of the original column? While it'll end up being a bit more work up front, it'll save you a few hundred headaches as time marches on.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 2

There's no need to do that when SQL server can manage the ID itself like stated in the post above.

If you have permission to alter the table, add a new ID field and remove the old one or simply make it not required.

Mark

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

Based on what you said...

The value doesn't matter,

... then I believe that Mark's solution above is the best way to go.

The problem may be if you've used that PK value anywhere else in any other tables. It's not difficult to do but can be a complex "discovery" process.

--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)