none
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

    Monday, January 21, 2013 5:21 AM

Answers

  • 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.

    Monday, January 21, 2013 5:29 AM

All replies

  • 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.

    Monday, January 21, 2013 5:29 AM
  • 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

    Monday, January 21, 2013 5:47 AM
  • 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

    Monday, January 21, 2013 5:51 AM
  • 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)

    Monday, January 21, 2013 5:55 AM
  • 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

    Monday, January 21, 2013 6:14 AM
  • 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

    Monday, January 21, 2013 8:17 AM