CODEWITHSUNDEEP
sqlsql-serverazure
Nayyar
Nayyar

Reputation: 79

Random numbers when using identity instead of sequential

I am creating a table on an azure data warehouse (synapse). I need to add a surrogate key to my dimension table. I have created the table with idenity as below:

CREATE TABLE DimAccount
(
    AccountKey INT NOT NULL IDENTITY(1,1)
    ,AccountID CHAR
)

when I insert values, I am getting random numbers in the AccountKey such as

AccountKey
17
21
56

I want the AccountKey to be sequential starting at 1. Can someone please advise?

Upvotes: 1

Views: 608

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270411

Identity values are not guaranteed to have no gaps. They are only guaranteed to be larger than previous numbers in the table.

There is no issue; identity is behaving as it should be. The gaps are actually important for performance. Any effort to generate gap-less numbers tends to require lots of additional locking and synchronization -- for little gain. And then, the gaps just re-appear if you delete any rows or roll back transactions.

In SQL Server, you could use a sequence to get the results you want. I don't think that option is available in SQL Azure.

Upvotes: 2

Related Questions