Identity

shantanu4u · #1 (**permalink**) 08-31-10, 09:10

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

MCrowley · #2 (**permalink**) 08-31-10, 09:20

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.

shantanu4u · #3 (**permalink**) 08-31-10, 09:24

ya you are right

but is there any way to alter the identity column

blindman · #4 (**permalink**) 08-31-10, 13:45

If you are updating an identity value, you are doing something dreadfully wrong in the first place.
Why would you want to do this?

MCrowley · #5 (**permalink**) 08-31-10, 13:54

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

apurgert · #6 (**permalink**) 09-01-10, 13:53

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?

MCrowley · #7 (**permalink**) 09-01-10, 13:56

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.

Pat Phelan · #8 (**permalink**) 09-01-10, 14:12

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

Internet Services

Web Development

Digital Marketing

Consumer Tech