Friday, March 9, 2012

How can I increase the range of an identity column

Hi guys
Im new at SQL Server and I have a problem with an identity column
the max value for the identity column has been reached I dunno if I can increased by an alter table or I need to recreate the table ?
the table has foreign keys
Thanks in advancedYou could go to the next larger datatype but I suspect your foreign keys will start to bomb out. What was the data type of your identity column and what was the original seed?

Consider the following:

create table Tmp(f1 int not null identity(2147483647,1), f2 varchar(5))
go
insert into Tmp (f2) values('ABC')
select * From Tmp
go
raiserror('Try to squeze one more row.',0,1) with nowait
insert into Tmp (f2) values('DEF')
select * From Tmp
go
raiserror(' ',0,1) with nowait
raiserror('change the seed.',0,1) with nowait
DBCC CHECKIDENT (Tmp, RESEED, -2147483648)
go
raiserror(' ',0,1) with nowait
raiserror('Insert more rows.',0,1) with nowait
insert into Tmp (f2) values('DEF')
select * From Tmp
go
insert into Tmp (f2) values('GHI')
select * From Tmp
go
sp_help Tmp
go
drop table Tmp
go

No comments:

Post a Comment