Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2003
    Location
    Mexico
    Posts
    1

    Unanswered: 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 advanced

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    You 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:

    Code:
    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
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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