IDENTITY is a SQL keyword to specify that a column in the table will be used as a Surrogate Key. Surrogate Key column contains numerical data to uniquely identify a row in the table. A surrogate key is used to assign unique row identifiers in various scenarios. One of the most common use cases for IDENTITY column is, when we are inserting new rows into a Dimension Table while loading data into a Data Warehouse.
Following is an example of IDENTITY column definition in Azure Synapse Analytics Hash distributed table:
CREATE TABLE dbo.T1 ( ADE1 INT IDENTITY(1,1) NOT NULL , ADE2 INT NULL ) WITH ( DISTRIBUTION = HASH(ADE2) , CLUSTERED COLUMNSTORE INDEX ) ;
Now that we have a basic understanding of the IDENTITY column, let’s have a look at the key differences between SQL Server and Synapse Analytics in terms of the features of the IDENTITY column:
|IDENTITY column Behaviour||SQL Server (including SQL Azure DB)||Azure Synapse Analytics|
|Unique Row Identifier||Yes||Yes|
|Column value order1||Sequential||Random|
|Data Types Supported2||Decimal, Int, Numeric, Smallint, Bigint, Tinyint||Int, BigInt|
|Number of allowed IDENTITY columns per table||1||1|
|Support for @@IDENTITY system function3||Yes||No|
As we can see above, there are some major differences between how the IDENTITY column works in SQL Server(both on-prem and Azure SQL Database) and Azure Synapse Analytics.
1. Column Value Order: The IDENTITY column in Synapse Analytics is populated with RANDOM values (not sequential). Phew!! Yes, it is hard to believe, but the architecture of Synapse Analytics doesn’t allow the IDENTITY column to have sequential values as is the case with traditional SQL Server.
2. Data Types Supported: Only INT and BIGINT data types are supported in Synapse Analytics. This is done to avoid premature exhaustion of key values for the identity column, so only the biggest datatypes are supported.
3. Support for @@IDENTITY: The @@IDENTITY is a system function that returns the last value of the most recently used IDENTITY column in the current session. @@IDENTITY is not available as a system function in Synapse Analytics.