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
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
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.