SQL Server

IDENTITY column with negative increment

Instead of standard setting IDENTITY(1,1) – increasing value from 1 by +1, we can set increment to be negative as by the following example:

CREATE TABLE dbo.SampleTable
(
    Id INT IDENTITY(0, -1) NOT NULL PRIMARY KEY
)
GO

INSERT dbo.SampleTable
    DEFAULT VALUES
GO 5

SELECT * FROM dbo.SampleTable
GO

IdentityWithNegativeIncrement

We can also set value for the first loaded row (SEED) to be negative:

CREATE TABLE dbo.SampleTable
(
    Id SMALLINT IDENTITY(-32768, 1) NOT NULL PRIMARY KEY
)
GO

INSERT INTO dbo.[SampleTable]
	DEFAULT VALUES
GO 5

SELECT * FROM dbo.[SampleTable]
GO

NegativeIdentitySeed

Negative value of the first row has the advantage in case we are working with large tables and we will eliminate table size and storage usage by using the smallest datatype we can. In this case when we will start for example for SMALLINT datatype identity sequence from -32768, we can insert in this column 65,535 values and if your data fits into this range there is no reason to use INT in this case. For sure we should think all the time that (-) sign is presented there, but that´s not an issue for ORMs or properly parameterized queries.

Leave a Reply

Your email address will not be published.