I just had an interesting conversation with one of my friend who said identity value can not start from Zero. I told him that it can even start from a negative value. He did not believe it. I quickly come with example and he was surprised to see it. Let us learn today about Negative Identity Seed Value and Negative Increment Interval.
USE [AdventureWorks] GO IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[IdentityNegative]') AND TYPE IN (N'U')) DROP TABLE [dbo].[IdentityNegative] GO CREATE TABLE dbo.IdentityNegative ( ID INT NOT NULL IDENTITY (-100, -1), Firstcol VARCHAR(100) NULL ) GO INSERT INTO dbo.IdentityNegative (Firstcol) SELECT 'First' UNION ALL SELECT 'Second' UNION ALL SELECT 'Third' UNION ALL SELECT 'Fourth' UNION ALL SELECT 'Fifth' GO SELECT * FROM dbo.IdentityNegative GO DROP TABLE dbo.IdentityNegative GO
Now let us see the resultset. Here we have specified negative identity Seed value as well negative Increment Interval.
Did you know that Identity Value can have a negative value? If yes, can you come up with the proper business need of the same? You can subscribe to me on my YouTube Channel.
Here are a few additional blog post related to this topic:
SQL SERVER – Having Two Identity Columns for A Single Table
In yesterday’s session, a DBA asked an interesting question that if it is possible to have two identity columns for a single table. Yes and No.
SQL SERVER – Last Page Insert PAGELATCH_EX Contention Due to Identity Column
today’s goal is to understand what is the Last Page Insert PAGELATCH_EX Contention Due to Primary Key. Let us start understanding it together.
SQL SERVER – Identity Jumping 1000 – IDENTITY_CACHE
SQL Server 2017 has a wonderful feature which fixes a very old issue which DBA and developers have been struggling for a while.
SQL SERVER – Jump in Identity Column After Restart
The SQL Product team makes a continuous change in the product which sometimes causes unpredictable behaviours in the SQL Server engine.
Reference: Pinal Dave (https://blog.sqlauthority.com)
27 Comments. Leave new
Hi,
I check thie Negative Identity in my Server, Its Nice.
I dont think like that, whether its possible or not.
But i try to reseed the table, it works fine.
create a table :
CREATE TABLE dbo.IdentityNegative
(
ID INT NOT NULL IDENTITY (1, 1),
Firstcol VARCHAR(100) NULL
)
Insert few records, then reseed the identity
dbcc checkident (‘dbo.IdentityNegative’,’reseed’,0)
Same like as
CREATE TABLE dbo.IdentityNegative
(
ID INT NOT NULL IDENTITY (-1, -1),
Firstcol VARCHAR(100) NULL
)
Insert few records, then reseed the identity
dbcc checkident (‘dbo.IdentityNegative’,’reseed’,0)
Here, i will use the same RESEED query for both negative and positive identity tables. Its identify the table have positive or Negative Identity?
How this action is performed in SQL Server?
Great post. I wouldn’t have guessed this was possible. You are a SQL authority.
Hi Pinal Dave,
Could you please tell me why we can’t use
Order By Cluase , While Creating a view….?
For same reason you can’t use order by clause why creating a table ;) You order it when you use it.
What you can do is schemabind the view and add clustered index to the field you want to order it by. Just take notice that not all views can be schemabound or indexed and indexed view lowers the performance of insert/update/delete operations.
Awsome example… I never thought in this direction…
Negative identity values are useful in programming: if you have build part of your database in memory as a dataset and you want to submit the changes back to the server, you cannot be sure of the identity values it will assign (especially in a shared environment). Therefore it is common to use negative identities and increments for the memory dataset and positive ones for the server database. The server does understands that mechanism and will assign correct values (It will take some tricks to refresh these values into the memeory dataset).
For a more elaborate artikel see: Managing an @@Identity crisis at https://docs.microsoft.com/en-us/previous-versions/dotnet/articles/ms971502(v=msdn.10)
needed it once when i had to merge two identical tables from different DB’s.
Set identity insert on and adding “-” sign prevents from duplicate identities.
I’ve seen this used where a value was needed at the beginning of an existing code table. Since the existing code table had the identity value start a 1, then 0 and -1 was used for the new values that were to be at the beginning.
Best Business reason I’ve seen is that you know you need more than 2 billion identity values but are unlikely to ever need 4. Think a read only historical database or something along those lines. Starting with a negative seed and working your way up allows you to use the more compact int datatype instead of bigint.
I always seed my identity columns with the smallest value possible to get the full use out of the data type, i.e. negative numbers.
create table #x
(
x smallint identity(-32768,1)
)
create table #x
(
x int identity(-2147483648,1)
)
The smallint identity gives you the use 65535 identities, which under many business rules is plenty of tuples to play with and saves space (just a little)…
mjfii
In this “big data” age I don’t see a reason to not use the lowest possible value for a data type. When you are dealing with billions of rows the possibility of running out of IDs is very high. When the seed value is the lowest number you are in reality doubling the amount of data that can be easily stored. A client I know of has had to write table functions to “wrap around” ID columns to the lowest number. It’s a pain when it gets to this and there is a performance hit as well because the routine is called all the time to check if the upper limit is near/has been reached.
Hello!
I did know that a negative identity value can be used (tried that myself a long time ago) – however, I have been thinking about it but have failed to come up with a practical use of the same for data storage.
However, my belief is that if Microsoft has provided some feature, they definitely use it, and it’s only a matter of time and research before we find where.
My guess is that such a feature would be used when we are trying to decrement age (eg. lazy writer or applications that count towards 0 upon occurance of an event). But, that’s only a guess.
Have a great day, everyone!
Interesting take on negative identity values, always used the default positive values… Thanks for the link BEL8490
We have an application where some users can fill out a questionnaire that gets stored to a database inside the firewall and some users can fill out a questionnaire using a public facing web app that get stored to a database outside the firewall. The questionnaire records from outside the firewall have negative identity values and get replicated to the database inside the firewall whose records have positive identities.
It is often used in peer-to-peer replication, where one server increments positively and other negatively, thus avoiding the conflicts when the sync up occurs.
nice example
Hello Pinal Sir,
I have done some arithmetic operation on table fields using select query and insert that result into anther table,
But Problem is If i subtract two fields then some result getting negative value just i have check condition if result is negative then inset zero value in that row and column
Please suggest me
How can Possible it
Metric SI prefixes table:
ID NAME SYMBOL
-9 nano n
-6 micro µ
-3 milli m
3 kilo k
6 mega M
…
This was very useful, Saved me half a day checking 2 tables for conflict in IDs to show them in 1 interface grid!!!!
I have an application in which a task can be assigned to either a specific user or a user group. The user table has a primary key with a positive increment starting from 1, the user group table has a primary key with a negative increment starting from -1. If the task references a negative ID, I know it has been assigned to a user group.
You may argue that this is crummy design (and you’d probably be right), but the concept of user groups was added long after the application had been delivered and this was the quickest way to implement it…
I Insert data in to a Fact table using two sources. one table has the positive primary key while the other has negative ID. In my staging area i’m creating a view to union these sources and insert into the fact using SP’s. But this transformation to the fact take unbearable time which i cannot understand. If anyone has come across this situation, i would love to know how you guys handle it. Thanks in advance.
Assuming my database will have only thousands or millions of rows, not billions of rows, then I like using negative numbers in some of my tables and positive in others.
It helps reduce the chance that somebody might join something like a CustomerID to a VendorID.
If all your tables have the same domain of values, then joining with the wrong keys will return the wrong data. If the domain of values is different, then no data would be returned, (which is more likely to be noticed).
I also seed my keys at different numbers. For example, I start some at 1, some at 10000 and some at 1000000. In a production environment, the values might eventually overlap, but on some guy’s test database, they are not likely to do so.
FYI: I tested the impact on fragmenting and found that very small tables see more fragmentation with reverse identities. But when you get to 100,000 rows, there is no difference.