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 (

Author: admin

Leave a Reply

Your email address will not be published.