make Identity column

Question

hi Friends

I need to make one column Identity with value with increment seed with 1 like I need

001 

002

003

004

----

----

----

I already have a column with identity

as 1

2

3

4 but i need

001

002

003

004 and so on.

Kindly help me

Answer 1

A table can have ONLY ONE identity column. Hence, You may need to created another COMPUTED column for the same.

BTB, What is you objective to have such character way of representation as long as you have an identity column? If this is for representation purpose, you may think of doing such formatting in front end or T-SQL in procedure.

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

U can try something like as below,

Declare @Table Table
(
Slno Int Identity(1,1),
Slno_1 As Right('00'+Cast(Slno As Varchar(5)),3),
Val Varchar(5)
)

Insert @Table(Val)
Select 'A' Union all
Select 'A' Union all
Select 'A' Union all
Select 'A' Union all
Select 'A' Union all
Select 'A' Union all
Select 'A' Union all
Select 'A' Union all
Select 'A' Union all
Select 'A' Union all
Select 'A' Union all
Select 'A' Union all
Select 'A' Union all
Select 'A' Union all
Select 'A' Union all
Select 'A' Union all
Select 'A' Union all
Select 'A' ;

Select * from @Table

---

Please have look on the comment

Answer 3

I think you can do this at the display level rather than storing ht values formatted.

declare @tab table(id int identity(1,1),Val char(1))

insert into @tab values
('a'),
('b'),
('c')

select replace(STR(id,3),' ',0) as NUM from @tab

Regards
Satheesh

Answer 4

It is technically possible as demonstrated in the script below but not recommended.

As you see in the script, you will have to use a calculated column. For this, you will need to pre-define the width of the calculated column. You have given it as three character in your example i.e. 001, 002 etc. that can go up to 999. What do you plan to do to if the value goes beyond 999 to 1000? The calculated column will return NULLs. You might want to use a normal IDENTITY column and format the leading zeros on the front-end, as suggested by Latheesh NK.

We can give better suggestions if you give details about the actual requirement.

create table CalculatedIdentityTable
(
Id int identity,
VarcharId as right( replicate('0', 3-len(Id)) + cast(Id as varchar(3)), 3),
EmpName varchar(10)
)
go

insert into CalculatedIdentityTable (EmpName)
values('Tom')
go 1005 -- insert values

select * from CalculatedIdentityTable
where Id between 1 and 15 -- example of 1 and 2 digit Id
or Id between 95 and 105 -- example of 3 digit Id
or Id > 995 -- example of when Id is more than 3 digits. Returns NULLs.

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

Answer 5

Are you using SQL Server 2012? 

create table #t (id int not null identity(1,1))

insert into #t default values
go 100


select id, format(id,'000') as new from #t

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 6

Why do you need 00100 instead of 100?

The leading zeros do not add anything, you can generate them on the fly any time.

Can you post the DDL for the table?  Thanks.

---

Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012