Computed primary key using identity and a letter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Computed primary key using identity and a letter

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-10-29 : 00:09:45

How can I create table with a primary key which is C_x
where x is identity 1,2,3,etc.
I do not want to have an extra field for the identity

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-29 : 01:00:06

if you dont want additional column for identity you can make use of row_number() function to generate it. but it wont be autogenerating you have include this in your insert statement to generate new value each time.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-10-29 : 02:06:11

Not sure what you mean by "computed" primary key.Is this is something like a computed column which you want it to be a primary key ?

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-29 : 03:00:56

i think op wants something like below but dont want to use additional identity column

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-10-29 : 03:07:42

quote:
Originally posted by visakh16

i think op wants something like below but dont want to use additional identity column

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Oh..Ok

PBUH

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-10-30 : 13:30:50

Thanks all
From reading the article in the link above I think it is better to use the identity column then generate from it the other column
C_1, I_1 and so on
because the other methods do have there own issues

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-30 : 13:38:19

quote:
Originally posted by sarahmfr

Thanks all
From reading the article in the link above I think it is better to use the identity column then generate from it the other column
C_1, I_1 and so on
because the other methods do have there own issues

sarah

thats the usual way of doing it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-10-30 : 15:43:50

A issue you should consider in using the method in the article is that your COMPUTED column is not the primary key or using any type of clustered index. If this is the column that all other tables are going to link on and you want it to be your PRIMARY KEY, then you will need to handle the computed column more like so


--drop table Customers
create table Customers
(
	dbID int identity(1,1) not null,
	CustomerName varchar(100)
)
go

alter table Customers add CustomerNumber  as ISNULL('C_' + right('0' + convert(varchar(10), dbID), 4),'0000') Primary Key

insert into Customers(CustomerName)
select 'hello-1' union all
select 'hello-2' 

select * from Customers

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources