IDENTITY (Property) (Transact-SQL) in SQL Server

Discussion on IDENTITY.

The guys from the North Texas SQL Server Users Group (NTSSUG) were dorking with IDENTITY yesterday, and it was a pretty good discussion.  Someone asked what happens when an integer column attempts to move past the value of 2147483647, which started the chat.  It brought some things to mind…

1) Integer in SQL Server - is a signed number (integral) that can span –2147483647 to 2147483647;  if you tried to enter 2147483647 + 1 for IDENTITY you’d have a problem of course. Run the following code to test, output below:

DROP TABLE BigIdentity
GO
CREATE TABLE BigIdentity
        (id int IDENTITY (2147483647,1) 
        ,value char (1) DEFAULT ''
        )
GO
INSERT INTO BigIdentity DEFAULT VALUES
GO
SELECT * FROM BigIdentity
GO
SET IDENTITY_INSERT BigIdentity ON
INSERT INTO BigIdentity (id, value) VALUES (2147483648, '')
SET IDENTITY_INSERT BigIdentity OFF
GO

First and foremost, if you get close to the max for your number with Identity property, by all means go ahead and do an ALTER TABLE and move up to a bigint or whatever is needed and be done with it.  If you don’t have a bigint type in the version of SQL Server that you are using, set it to a numeric or decimal type (these two types are exactly the same and can be used interchangeably) with a scale of zero.  The issue of running out of numbers should be a non-issue.

2) Type dependency - It was mentioned that a type-dependency existed on the architecture of the host server. I have no problem using a bigint on my 32-bit laptop, so I’m not sure where this was going.  If the type is available in your database, then it’s available regardless of your processor architecture, either 32-bit or 64-bit technology.

3) Range of numbers – one neat way to increase the span of your numbers for IDENTITY is to start them off with the negative number, as below.  The signed number, starting from the max negative, gives you the full range of values for the type, as if it were an unsigned type.  The following increments up to 2147483647 beginning with 
–2147483647.

DROP TABLE BigIdentity
GO
CREATE TABLE BigIdentity
        (id int IDENTITY (-2147483647,1) 
        ,value char (1) DEFAULT ''
        )
GO

4) SET IDENTITY_INSERT – if you use this to go greater in your range of values, then your seed value picks up with the number left off.  Run the following:

DROP TABLE BigIdentity
GO
CREATE TABLE BigIdentity
        (id int IDENTITY (-2147483647,1) 
        ,value char (1) DEFAULT ''
        )
GO
INSERT INTO BigIdentity DEFAULT VALUES    
GO
SELECT * FROM BigIdentity
GO
SET IDENTITY_INSERT BigIdentity ON
INSERT INTO BigIdentity (id, value) VALUES (1000, '')
SET IDENTITY_INSERT BigIdentity OFF
GO
INSERT INTO BigIdentity DEFAULT VALUES
GO
SELECT * FROM BigIdentity
GO

5) This doesn’t work…why?

DECLARE @int bigint
SET @int =2147483647+2147483647 

Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type int.

Remember that the right side of the expression is evaluated first, and then gets put into the left side of the equation, which in this case it is stored in a variable.  This does:

set @int =cast(2147483647 as bigint)+  2147483647

Thanks for reading!

Lee

-------------------------

IDENTITY will run out, just as the GUID will someday.

Reference:http://weblogs.asp.net/leftslipper/archive/2010/04/01/last-guid-used-up-new-scottguid-unique-id-to-replace-it.aspx