Is an autoincrement key always guaranteed to increase in T-SQL?

Question

I am using an AUTOINCREMENT PRIMARY KEY in-luau of a timestamp for ordering purposes in a GROUP BY clause.

This is a minimal example to illustrate only:

CREATE TABLE Items(
    ItemId BIGINT           IDENTITY (1, 1) NOT NULL,
    CategoryId BIGINT,
    CONSTRAINT [PK_Items] PRIMARY KEY NONCLUSTERED ([ItemId] ASC)
);

INSERT INTO Items(CategoryId)
--      1    2    3    4    5
VALUES (1), (1), (2), (2), (3);

SELECT
    LatestItemForCategory = MAX(ItemId)
FROM Items
GROUP BY CategoryId;

This should print:

LatestItemForCategory
2
4
5

I think this should work as well as a timestamp, generally, but I'm worried maybe T-SQL can do something "smart" like try to reuse deleted record's IDs which would mess up this kind of query.

I assume BIGINT has enough values to last till the end of time even for the biggest databases without rolling over?

Can I count on the primary key only increasing or does it roll-over at some point?

Answer 1

In T-SQL, every time you put a new record into a table that uses an auto-incrementing key, this key gets a number that's one more than the previous one.

It doesn't recycle numbers from entries you've deleted, so each record gets its own, higher number.

Regarding your concern about BigInt ID: The number type used, BIGINT, has a really large range, so it's unlikely you'll ever use them all up.

Notice: Just keep in mind, there might be some missing numbers if you remove records or if there are errors. Generally, this system works well for keeping your entries organized.

However, if your auto-increment ID reaches the maximum number it can go, it can't increase anymore. This is a problem because it means you can't add any more new entries. When this happens, the database will give you an error message.

and about comments which are placed under your posts I should mention that:

• Usually, in T-SQL, each new record you add to a table gets a higher number than the last one if you're using auto-increment.

• Deleting stuff doesn't mess up this order – if you delete something, the next new thing still gets the next number in line. But, if you manually reset this number system (called re-seeding), then you might get numbers that don't follow the usual order.

So, under normal and regular use, the numbers keep going up, but if you change the settings yourself, they might not.

The column gap ex:

Answer 2

Can I count on the primary key only increasing or does it roll-over at some point?

An IDENTITY isn't a PRIMARY KEY; these are different things. A IDENTITY doesn't have to be a PRIMARY KEY and likewise a PRIMARY KEY doesn't need to be an IDENTITY. The answer to that question, therefore, is there is nothing stopping the value of a PRIMARY KEY being lower than a prior value.

Your title, however, asks a different question:

Is an autoincrement key always guaranteed to increase in T-SQL?

The documentation states what an IDENTITY is(n't) guaranteed to do, and it specifically states:

Identity columns can be used for generating key values. The identity property on a column guarantees the following conditions:

• Each new value is generated based on the current seed and increment.
• Each new value for a particular transaction is different from other concurrent transactions on the table.

So yes, an IDENTITY will always increase. It does not loop. If you get to the upper limit of the IDENTITY the INSERT will simply fail:

CREATE TABLE dbo.I (ID tinyint IDENTITY(1,1),
                    N char(1) NULL);
GO

INSERT INTO dbo.I (N)
SELECT NULL
FROM GENERATE_SERIES(1,255);
GO

INSERT INTO dbo.I (N)
VALUES(NULL);
GO

DROP TABLE dbo.I;

(255 rows affected)

Msg 8115, Level 16, State 1, Line 13
Arithmetic overflow error converting IDENTITY to data type tinyint.

Of course, the next value generated can be made to be lower than the last one if the seed of the IDENTITY is changed, however, this means someone needs to explicitly change the behaviour of said IDENTITY. Also, if a table is TRUNCATEd then the value of the IDENTITY is also reset to its initial sed.