Final value of an integer column

Question

Post reply

Final value of an integer column

lucky-80472

SSCertifiable

Points: 7793
More actions
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

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

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

ThomasH SSCertifiable Points: 7437 More actions · Answer 1

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

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 2

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.

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 3

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

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]

ThomasH SSCertifiable Points: 7437 More actions · Answer 5

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

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]

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 7

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

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]

ThomasH SSCertifiable Points: 7437 More actions · Answer 9

"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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

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]

ThomasH SSCertifiable Points: 7437 More actions · Answer 11

I appoligize

Das neste mahl schribe ich es in Duetch.

Once you understand the BITs, all the pieces come together