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:
- SQL SERVER – Having Two Identity Columns for A Single Table
- SQL SERVER – Last Page Insert PAGELATCH_EX Contention Due to Identity Column
- SQL SERVER – Jump in Identity Column After Restart
Reference: Pinal Dave (https://blog.sqlauthority.com)
