none
Resetting IDENTITY value for a column in Azure SQL DW is not working RRS feed

All replies

  • Hello,

    I am trying to reset a current value of an IDENTITY (1,1) column in a Azure SQL DW table and it is not working.


    I have faced this issue but not sure if there is any solution to this. 

    What I saw in documentation is below. Looks like values are not in sequence and this is as per design in ADW.

    The IDENTITY property doesn't guarantee the order in which the surrogate values are allocated, which reflects the behavior of SQL Server and Azure SQL Database. However, in Azure SQL Data Warehouse, the absence of a guarantee is more pronounced.

    Reference: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-identity#allocation-of-values


    Cheers
    Vaibhav
    MCSA (SQL Server 2014)



    Tuesday, September 10, 2019 11:29 AM
  • Hello Vaibhav,

    I do understand that IDENTITY does not guarantee an order but it was important to be able to reset the value to 1. It is not that critical if I have some gaps so not that required to have correct order and always incremented by 1 number.

    However, it looks this absence of guarantees is further aggravated in Azure SQL DW and more obvious there.

    Can it be due to the nature of Azure SQL DW and these distributions we have under the hood?

    For your information, I've tested with tables with ROUND_ROBIN and then REPLICATE distributions and the behavior was similar - not able to get the values back to 1.

    Thanks.

    Tuesday, September 10, 2019 12:05 PM
  • Hi Miroslav,

    Please see the following with regard to IDENTITY column:

    Identity column value might lose in some CTAS operation - The value of an identify column may not be preserved when CTASed to another table. Reported in a blog.

    There is also a completed feature request: Add IDENTITY Types (Completed June 27, 2017)

    Support SEQUENCE types in SQL DW - Under Review

    It sounds as if you need to create a new user voice entry to request this functionality. 

    Regards,

    Mike

    Tuesday, September 10, 2019 9:40 PM
    Moderator