SQL SERVER – Negative Identity Seed Value and Negative Increment Interval

I just had 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 negative value. He did not believe it. I quickly come with example and he was surprised to see it.

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 negative value? If yes, can you come up with proper business need of the same?

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

23 thoughts on “SQL SERVER – Negative Identity Seed Value and Negative Increment Interval

  1. 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?

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

  2. 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 http://msdn.microsoft.com/en-us/library/ms971502.aspx

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

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

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

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

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

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

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

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

  11. Pingback: SQL SERVER – Understanding Identity Beyond its Every Increasing Nature – Quiz – Puzzle – 3 of 31 « SQL Server Journey with SQL Authority

  12. 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…

  13. Pingback: SQL SERVER – RESEED Identity Column in Database Table – Rest Table Identity Value – SQL in Sixty Seconds #051 | SQL Server Journey with SQL Authority

  14. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s