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)
4 Comments. Leave new
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 totally agree with you.
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.