How to create primary key value

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

General SQL Server Forums

Database Design and Application Architecture

How to create primary key value

Author

Topic

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-11-06 : 05:06:35

Hai,

I want to set primary key ID value like '000001' instead 1 and '000002' instead of 2 and so on..

How to create table? kindly help me.

Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-06 : 05:14:40

create an identity column and then a computed column based on that

something like


CREATE TABLE TableName
(
ID int IDENTITY(1,1),
PKID AS RIGHT('000000' + CAST(ID AS varchar(6)),6) PRIMARY KEY,
...
CONSTRAINT PK_TableName_PKID
               PRIMARY KEY CLUSTERED (PKID)
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-06 : 05:15:43

also see

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-11-07 : 03:26:47

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 04:20:21

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-11-09 : 03:30:38

I want to create table like this Member Id value is EM201300001.
But i got error.

create table Celenew1 (ID INT IDENTITY ( 1 , 1 )PRIMARY KEY,
MemberId AS 'EM' + Cast(year(getdate() as varchar) +RIGHT('0000' + CAST( ID as varchar),5),
name varchar(20))

Kindly help me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-09 : 10:40:22

there were few syntax errors


create table Celenew1 (ID INT IDENTITY ( 1 , 1 )PRIMARY KEY,
MemberId AS 'EM' + Cast(year(getdate()) as varchar(4)) +RIGHT('0000' + CAST( ID as varchar(5)),5),
name varchar(20))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-11-13 : 04:58:56

Thank you

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-11-13 : 05:03:41

I want to get year last two digit only. that is 2013 means only 13
I used this query. But its display 2013

select datepart(yy,getdate())

Kindly help me

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-13 : 05:57:08

SELECT RIGHT(YEAR(getdate()), 2) YY

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-13 : 05:58:47

[code]select
[Year] = right(a.Yr,2)
from
( --Test Data
select Yr = 1998 union all
select Yr = 1999 union all
select Yr = 2000 union all
select Yr = 2001 union all
select Yr = 2002 union all
select Yr = 2004
) a[/code]

--
Chandu

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-11-13 : 22:03:26

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources