Creating a customized identity column

Question

Post reply

Creating a customized identity column

Dong Lee

SSC-Addicted

Points: 449
More actions
November 9, 2007 at 1:48 pm

#119461

Hi,
I'm developing vb6 and ms sql2k application. I'd like identity column to look like '001', '002', or 'SS003'. These IDs are unique. I'll have a multiple users. Is there any way to create IDs automatically in the database? Thank you.
Dong

Viewing 9 posts - 1 through 8 (of 8 total)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

See http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

N 56°04'39.16"
E 12°55'05.25"

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

Heh... or, you could just shoot yourself in the head now and save on the anticipation 😀

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

Don't do this. USe an identity, add a 2nd computed column if you must that pads things out with the 0s (meaning it's a char ) col.

SQL handles this well. You have a good chance of messing things up.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

Sorry for my bit of sarcasm... you do realize that you get 999 ID's and that's it, don't you?

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

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 5

The best way to do this is to have an identity column and then have a calculated column that adds whatever prefix you need to the identity. If you really need to avoid the calculated column for some reason, you can add an on insert trigger that adds that column's value based on the current max, but you will get greater efficiency from the computed column and identity.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 6

The best way to do this is to not do it at all... OP wants to limit the number of ID's to 999.

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 7

As soon as you limit it, someone will come along and ask for more.

You could easily do this with an identity and set a trigger to stop anything after 999. Or find the holes and insert there, or do any number of things.

As Jeff said, the easiest way is not to do this.

steve block SSCertifiable Points: 7698 More actions · Answer 8

It sounds like you want to see the data in a different way than it is stored. In that case, just use a format statement when you retrieve the data. You could set up a view to do this. Then when you go beyond 999 you could change the format.

Steve