#0271 – SQL Server – IDENTITY columns – Myths – IDENTITY values (including SEED & INCREMENT) cannot be negative


When one thinks of an incremental number, a series of positive numbers comes to mind. Since IDENTITY columns are an auto-incrementing series of numbers, it is human to associate IDENTITY columns with positive numbers. However when we talk about numbers and the number line, we can go to infinity from both sides of 0 (-Infinity….-5, -4, -3, -2, -1, 0, +1, +2, +3, +4, +5,….+Infinity).


Hence, the question that comes up is whether IDENTITY values (including the seed & increment values) can be negative. The only way to test it out is to use an example:

USE tempdb;
GO

SET NOCOUNT ON;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table
CREATE TABLE dbo.tIdentity (IdentityId INT IDENTITY (-1, -1),
                            IdentityValue VARCHAR(20)
                           );
GO

--2. Add some test data
INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('Minus One'),
       ('Minus Two'),
       ('Minus Three'),
       ('Minus Four'),
       ('Minus Five');
GO

--3. Check the values inserted into the table
SELECT IdentityId, IdentityValue
FROM dbo.tIdentity;

----3b.One may also want to check the value of IDENT_CURRENT
--SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValue;
--GO

--4. Cleanup
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

/**********************************************
               RESULTS
**********************************************/
/*
IdentityId  IdentityValue
----------- --------------
-1          Minus One
-2          Minus Two
-3          Minus Three
-4          Minus Four
-5          Minus Five
*/

As can be seen from the result of the script above, it is possible for the SEED, INCREMENT and the IDENTITY column value itself to be negative. Keeping in mind the rules of the number line (from elementary school), please note that as one moves away from 0 (in either direction), the number keeps increasing in value and is therefore incrementing.


Conclusion


As shown by the quick experiment above, it is quite possible to have negative values for IDENTITY columns. As of writing this post, I do not have a practical implementation of this in mind. However, if you have ever encountered a situation where auto-incrementing negative values might be useful, do drop a line in the blog comments below.


Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

One thought on “#0271 – SQL Server – IDENTITY columns – Myths – IDENTITY values (including SEED & INCREMENT) cannot be negative

  1. Pingback: #0390 – SQL Server – SSIS – OLE DB Destination – Table Fast Load – Keep Identity option | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s