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.