{"id":3740,"date":"2017-06-28T00:00:00","date_gmt":"2017-06-28T07:00:00","guid":{"rendered":"https:\/\/azure.microsoft.com\/blog\/identity-now-available-with-azure-sql-data-warehouse"},"modified":"2025-06-20T07:18:29","modified_gmt":"2025-06-20T14:18:29","slug":"identity-now-available-with-azure-sql-data-warehouse","status":"publish","type":"post","link":"https:\/\/azure.microsoft.com\/en-us\/blog\/identity-now-available-with-azure-sql-data-warehouse\/","title":{"rendered":"Identity now available in SQL Data Warehouse"},"content":{"rendered":"\n

Azure SQL Data Warehouse (SQL DW) is a SQL-based, fully managed, petabyte-scale cloud solution for data warehousing. SQL DW is highly elastic, you can provision in minutes and scale capacity in seconds. You can scale compute and storage independently, allowing you to burst compute for complex analytical workloads or scale down your warehouse for archival scenarios, and pay based off what you’re using instead of being locked into predefined cluster configurations.<\/p>\n\n\n\n

IDENTITY has been a long standing customer ask for SQL Data Warehouse. We\u2019re excited to announce that Azure SQL Data Warehouse now supports an IDENTITY column property as well as SET IDENTITY_INSERT syntax and generating IDENTITY on load. In data warehousing, IDENTITY functionality is particularly important as it makes easier the creation of surrogate keys.<\/p>\n\n\n\n

Surrogate keys are fundamental to dimensional modelling because they often uniquely identify a row. Since they are typically integer values, they also tend to compress and compare with better performance. While UUIDs can often be used for similar purposes, they are harder to manage, don\u2019t intrinsically contain temporal information, and are non-performant. For large data warehouses, the 4x size of UUIDs compared with a traditional 4-byte IDENTITY value really adds up. The previous method of assigning monotonically increasing surrogate keys involved using left outer joins from a staging table combined with the application of getting the max id on the surrogate key column with a ROW_NUMBER function. This solution was clunky and invoked a costly broadcast data move.<\/p>\n\n\n\n

We hope that by adding this feature, we\u2019ve made data management in SQL DW easier and better for our customers.<\/p>\n\n\n\n

Keep in mind, this IDENTITY property is not synonymous with uniqueness constraints which are often imposed on IDENTITY columns!<\/p>\n\n\n\n

Next steps<\/h2>\n\n\n\n

Get started today by creating IDENTITY columns in a table today. It\u2019s as simple as:<\/p>\n\n\n\n

\n
CREATE TABLE dbo.T1\n(\tC1 INT IDENTITY(1,1) NOT NULL\n,\tC2 INT NULL\n)\nWITH\n(   DISTRIBUTION = HASH(C2)\n,   CLUSTERED COLUMNSTORE INDEX\n)\n;<\/pre>\n<\/blockquote>\n\n\n\n
Bear in mind that the IDENTITY property cannot be used in the following scenarios:<\/p>\n\n\n\n