Post reply

Final value of an integer column

  • January 8, 2004 at 8:36 am

    #106724

    Hi,

    I want to know the final value when data rows are inserted into a table. The column is a primary key and an identity is set starting from 1. In all, i am having 22 tables related to each other.

    So in advance i am trying to know the value, rather than re-designing the entire database for incorporating the necessary change. In BOL, it is documented as -2^31 to + 2^31.

    All SQL Gurus, please help me in this aspect.

     


    Lucky

  • January 8, 2004 at 8:43 am

    #489144

    Are you talking about when the identity value reaches the max value of the data type?

    or will SQL functions like @@IDENTITY, IDENT_CURRENT() or SCOPE_IDENTITY( ) be of help.

     



    Once you understand the BITs, all the pieces come together

  • January 8, 2004 at 8:47 pm

    #489272

    BOL 2000:

    The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns.

    bigint

         Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).

    int

         Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

    smallint

         Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).

    tinyint

         Integer data from 0 through 255.

     

  • January 8, 2004 at 11:05 pm

    #489283

    Hi ThomasH,

    Are you talking about when the identity value reaches the max value of the data type?

    Yes i want to know the max value of the data type "int" when it is assigned an identity.

     


    Lucky

  • January 9, 2004 at 1:08 am

    #489295

    In this case look at 5409045121009 (consider changing your username, Len, see what copy and paste now yields ) quoting of BOL

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 9, 2004 at 7:13 am

    #489356

    Yes i want to know the max value of the data type "int" when it is assigned an identity.

    As Mr./Mrs. #### says... 2,147,483,647

    BTW, what does SQL do for the next record when the max number is reached?



    Once you understand the BITs, all the pieces come together

  • January 9, 2004 at 7:26 am

    #489359

    Server: Nachr.-Nr. 8115, Schweregrad 16, Status 1, Zeile 1

    Arithmetic overflow error converting IDENTITY to data type int.

    Arithmetic overflow occurred.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 9, 2004 at 12:03 pm

    #489444 

    Example:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Drop Table [dbo].[Test]
GO
    CREATE TABLE [dbo].[Test] (
 [IntIdentityCol] [int] IDENTITY (2147483647, 1) NOT NULL ,
 [ColB] [varchar] (50) NULL )
GO
Insert Test values('A') -- Ok 2147483647 get 
Insert Test values('B') -- Error integer overflow!
GO
Select * from Test
Go
Drop Table Test
Go

  • January 9, 2004 at 1:18 pm

    #489455

    If this will ever happen you can

    CREATE TABLE [dbo].[Test] (

    [IntIdentityCol] [int] IDENTITY (2147483647, 1) NOT NULL ,

    [ColB] [varchar] (50) NULL

    )

    GO

    Insert Test values('A') -- Ok 2147483647 get

    Insert Test values('B') -- Error integer overflow!

    GO

    alter table Test alter column IntIdentityCol bigint

    go

    Insert Test values('B') -- Working again 2147483648

    Select * from Test

    Go

    Drop Table Test

    Go

    Should leave enough room for expansion

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 9, 2004 at 1:29 pm

    #489456

    "Should leave enough room for expansion"...

    Only if we purge Frank's posts every so often



    Once you understand the BITs, all the pieces come together

  • January 9, 2004 at 1:36 pm

    #489458

    Could a moderator please delete the above post?

    I feel offended

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 9, 2004 at 1:42 pm

    #489459

    I appoligize

    Das neste mahl schribe ich es in Duetch.



    Once you understand the BITs, all the pieces come together

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply