Negative Identity Column – SQL in Sixty Seconds #101

Negative Identity Column - SQL in Sixty Seconds #101 101-NegativeIdentity-cover1-800x450 I had a very interesting story to tell about a negative identity column from my recent Comprehensive Database Performance Health Check.

Real-World Story

Recently I had the opportunity to work on a project where my client had a negative identity column. The column was continuously decreasing. It starts at zero (0) but keeps on decreasing. When I asked them why the column is negative, they were really not sure about the reason. After carefully looking at their deployment code they figured out that they had accidentally put -1 as interval instead of +1 and that was the reason for the decrementing identity column.

Here is the video which discusses the negative identity column. I hope you find it useful.

After looking at the identity column my client was indeed worried if it has any negative impact on their SQL Server Performance or if they will face any issue in the future. The answer, there is no impact on performance on performance due to how you keep your identity or its interval.

During my career, I have seen examples where my client ran out of the identity column value. They were not in the situation of the using the same identity value again and hence decided to reset (with reseeding) identity value to zero and started to decrement it with a negative value.

Script for Negative Identity Column

Here is the script which I have used in the video.

	(ID INT IDENTITY (0,-1), Col1 VARCHAR(100))
INSERT INTO TestTable (Col1) 
VALUES ('') 
GO 20 
FROM TestTable

Please leave feedback to this blog post and let me know what you think about the SQL in the Sixty Seconds video series and also this demonstration.

Here are a few related blog posts:

Reference: Pinal Dave (

SQL Identity, SQL in Sixty Seconds, SQL Performance, SQL Scripts, SQL Server
Previous Post
List All Parallel Queries – SQL in Sixty Seconds #098
Next Post
Are Not Equal to Operators Equal to Not In? – SQL in Sixty Seconds #102

Related Posts

4 Comments. Leave new

  • myprogrammingexp
    August 14, 2020 9:30 pm

    The negative identity is not an issue. It is just that positive numbers are easy on the eyes. A lot of systems have the identity column as the only key value. As an example a customer number of 1001 is more readable compared to say -1001.

    Just my two cents.

  • I have used a used a negative seed for the identity column and incremented by 1 to utilize all the values of an int in order to not run out of values.

  • The best use case I’ve seen is a temporary fix when you run out of values. You switch the seed back to 0 (or -1 depending) and the increment to -1 and you have a whole new set of values to use. You’ll probably still need to alter the datatype to something like BIGINT to get more values but this is a quick and easy way to get you up and running again.

    Another version I’ve seen is when you have 2 tables where the data has to be merged. Say a sales table at two different locations. You can make one of them have positive values and the other negative and then the two tables can be merged together without any issues.


Leave a Reply