Unique ID Creation

Question

Unique ID Creation

bpowers

SSCertifiable

Points: 5483
More actions
November 9, 2011 at 8:25 am

#245916

Is there a way to combine the two columns into one?
R_ID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
REPAIR_ID AS 'R' + RIGHT('00000' + CAST(R_ID AS VARCHAR(5)),5),

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 1

If you mean as part of the table definition, absolutely. Your syntax for the "computed column definition" seems to be correct. Are you having a problem with creating a table with such a column in it?

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

bpowers SSCertifiable Points: 5483 More actions · Answer 2

Yes. I am trying to create column REPAIR_ID as an identity with a leading "R". My current syntax works, but I would like to do this without creating the additional column. I figured it was possible, but I just can't seem to get the right syntax down.

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

bpowers (11/9/2011)
Yes. I am trying to create column REPAIR_ID as an identity with a leading "R". My current syntax works, but I would like to do this without creating the additional column. I figured it was possible, but I just can't seem to get the right syntax down.

Ok... I'm confused a bit. If you don't want the table to exist in the column, do you just want it in a query result? If so, use very similar syntax in a SELECT statement. Either of the following methods will do...

SELECT REPAIR_ID = 'R' + RIGHT('00000' + CAST(R_ID AS VARCHAR(5)),5)

FROM dbo.yourtable

;

SELECT 'R' + RIGHT('00000' + CAST(R_ID AS VARCHAR(5)),5) AS REPAIR_ID

FROM dbo.yourtable

;

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

bpowers SSCertifiable Points: 5483 More actions · Answer 4

I want the unique id to include an "R" at the beginning of each record created:

R00001

R00002

R00003

Etc..

The following works, when creating the table, however I would like to consolidate the two columns shown into one if possible.

CREATE TABLE [REPAIR_PART]

(

R_ID int IDENTITY(1,1) PRIMARY KEY NOT NULL,

REP_ID AS 'R' + RIGHT('00000' + CAST(R_ID AS VARCHAR(5)),5),

Etc...

bpowers SSCertifiable Points: 5483 More actions · Answer 5

I do not think I can accomplish this without having both columns. I will just persist my computed column, set it as the primary key, and roll on.

Thanks for taking the time to reply.

CREATE TABLE [REPAIR]

(

R_ID int IDENTITY(1,1) NOT NULL,

REPAIR_ID AS 'R' + RIGHT('00000' + CAST(R_ID AS VARCHAR(5)),5) PERSISTED PRIMARY KEY NOT NULL,

Etc...