Results 1 to 8 of 8

Thread: Identity

  1. #1
    Join Date
    Feb 2010
    Posts
    9

    Unanswered: Identity

    create table table1 (col1 int identity(1,1),col2 varchar(10))

    i created the table like this
    now i want to alter col1 which can increase by value 2


    something like that

    alter table table1 alter column col1 int identity(1,2)

    plz help

    i have done this through wizard

    i want sql query
    Last edited by shantanu4u; 08-31-10 at 10:22. Reason: solution

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If the table is not large, the easiest thing to do would be to create a new table with the desired identity column, then transfer over the data from the old table to the new table. sp_rename will complete the process.

  3. #3
    Join Date
    Feb 2010
    Posts
    9
    ya you are right but is there any way to alter the identity column

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you are updating an identity value, you are doing something dreadfully wrong in the first place.
    Why would you want to do this?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Another way to do it:
    Code:
    create table test1 
    (col1 int identity (1, 1) not null,
     col2 varchar(20),
     col3 varchar(30),
     constraint PK_Test1 primary key (col1))
    go
    insert into test1 (col2, col3) values ('hello', 'good bye')
    insert into test1 (col2, col3) values ('hi', 'bye')
    select *
    from test1
    
    go
    alter table test1 drop constraint PK_test1
    alter table test1 drop column col1
    alter table test1 add col1 int identity (1, 2)
    go
    select *
    from test1
    go
    drop table test1

  6. #6
    Join Date
    Jul 2009
    Posts
    47
    If I'm not mistaken you could always just drop the column then add it with a reseed.

    IE

    Code:
    ALTER TABLE table1
    DROP COLUMN col1
    Then

    Code:
    ALTER TABLE table1
    ADD col1 int IDENTITY(1,2)
    Why do you need it to increment by two anyways?

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Most common reason would be for some sort of merge operation, when you want to make sure that two copies of the table can not conflict with each others' primary keys. Less common is a general bias against even numbers.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As blindman pointed out early in this thread, knowing what the values of an identity column is a mistake from the outset and caring what those values are is even worse. This is a disaster searching for its next victim, do NOT bellyflop into the maw!!!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •