Identity column skipping sequence RRS feed

All replies

  • Hi Postman,

    Firstly, the IDENTITY property cannot be used in the following scenarios:

    • Where the column data type is not INT or BIGINT
    • Where the column is also the distribution key
    • Where the table is an external table

    As for the identity behavior skipping a given sequence, a separate mechanism will need to be implemented per the Remarks:

    Consecutive values after server restart or other failures -SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

    One of those mechanisms is to explicitly insert your own Identity value: Explicitly inserting values into an IDENTITY column

    Please let me know if this is a suitable solution.



    Friday, February 21, 2020 5:08 AM
  • Hi postman,

    Due to the nature of the ADW with 60 distributions each distribution will get different subsections numbers. So distribution 1 will insert 60,120,180, distribution 2 will insert 61,121,181, distribution 3 will insert 62,122,182 etc.

    You can read more about it here: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-identity

    Tuesday, February 25, 2020 8:41 AM