Search code examples
sqlsql-server-2017

IDENTITY SEED is incrementing based on other tables seed values

I'm using SQL SERVER 2017 and using SSMS. I have created a few tables whose Primary Key is int and enabled Is Identity and set Identity Increment = 1 and Identity Seed=1 For all the tables I have used the same method. But When I added one record in a table say Lead it's ID was 2, Then added value to the table say Followup then its ID was 3. Here I'm adding the screenshots for a better understanding

Lead Table Lead Table

Followup Table Followup Table

Is there any option available to avoid this? can we keep the identity individual for each table?

Solution

  • The documentation is quite specific about what identity does not guarantee:

    The identity property on a column does not guarantee the following:

    • Uniqueness of the value . . .

    • Consecutive values within a transaction . . .

    • Consecutive values after server restart or other failures . . .

    • Reuse of values

    In general, the "uniqueness" property is a non-issue, because identity columns are usually the primary key (or routinely declared at least unique), which does guarantee uniqueness.

    The purpose of an identity column is to provide a unique numeric identifier different from other rows, so it can be readily used as a primary key. There are no other guarantees. And for performance SQL Server has lots of short-cuts that result in gaps.

    If you want no gaps, then the simplest way is to assign a value when querying:

    row_number() over (order by <identity column>)

    That is not 100% satisfying, because deletions can affect the value. I also think that on parallel systems, inserts can as well (because identities might be cached on individual nodes).

    If you do not care about performance, you can use a sequence for assigning a value. This is less performant than using an identity. Basically, it requires serializing all the inserts to guarantee the properties of the insert.

    I should note that even with a sequence, a failed insert can still produce gaps, so it still might not do what you want.