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.

CREATE TABLE TestTable 
	(ID INT IDENTITY (0,-1), Col1 VARCHAR(100))
GO
INSERT INTO TestTable (Col1) 
VALUES ('SQLAuthority.com') 
GO 20 
SELECT *
FROM TestTable
ORDER BY ID
GO
DROP TABLE TestTable
GO

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 (https://blog.sqlauthority.com)

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

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

    Reply

Leave a Reply

Menu