Search code examples
sqlsql-serversql-server-2012identityclustered-index

New IDENTITY column on existing table - what will be the order?

I added an IDENTITY column to an existing table in SQL Server 2012. Expected the numbers to be generated in the order of clustered index (non-sequential GUID), but to my surprise they got generated in the order of one of the indexes which is not even unique, and also coincidentally exactly the order I wanted!

Can anyone explain this? Here are the details of the table:

   id - guid (non-sequential), clustered index, primary key
   eventdate - bigint (unix date), not null, non-unique index
   more columns, some indexed, all indexes non-unique

Identity values got assigned in the order of eventdate. I even found a few examples where several rows had the same eventdate, and they always had sequential identity numbers.

Solution

  • MSDN says that the order in which identity values are generated for the new column of existing table is not defined.

    IDENTITY

    Specifies that the new column is an identity column. The SQL Server Database Engine provides a unique, incremental value for the column. When you add identifier columns to existing tables, the identity numbers are added to the existing rows of the table with the seed and increment values. The order in which the rows are updated is not guaranteed. Identity numbers are also generated for any new rows that are added.

    So, you'd better check thoroughly that you got new IDENTITY values in the order that you need. Check all rows of the table.

    Edit

    "The order is not guaranteed" doesn't mean that it is random, it just means that optimizer is free to pick any method to scan the table. In your system it apparently picked that index on eventdate (maybe it has the least amount of pages), but on another hardware or another version of the server the choice may change and you should not rely on it. Any change to the table structure or indexes may change the choice as well. Most likely the optimizer's decision is deterministic (i.e. not random), but it is not disclosed in the docs and may depend on many internal things and may change at any time.

    Your result is not unexpected. Identity values were assigned in some unspecified order, which coincided with the order of the index.