question

sakuraime avatar image
0 Votes"
sakuraime asked michaelchau2 answered

Azure synapse tablock

Dear All expert.
In Azure sql datawarehouse. are there any way to do tablock ?

I have a table with a column having an id int column .
I want to insert a record in this table with increment of 1 . how can I do that ?

azure-synapse-analytics
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Good day,


My guess is that you did not know that as of 2018 Identity is supported in Azure Data Warehouse :-)


Well... the answer is that you can use IDENTITY


 CREATE table dbo.T1(
     C1 INT IDENTITY(1,1) NOT NULL
     ,C2 INT NULL
 )
 WITH(
    DISTRIBUTION = HASH(C2)
     ,CLUSTERED COLUMNSTORE INDEX
 );
 GO

0 Votes 0 ·

Are you sure it's incremental ? or random ?


0 Votes 0 ·

No, my mistake @michaelchau2

You are right. IDENTITY does not fit in Azure Data Warehouse, since it does not guarantee the order

And unfortunately, SEQUENCE does not supported at all

0 Votes 0 ·

It's good question @michaelchau2

I don't think there is a built-in DDL solution for your need. Moreover, any solution which is based on the application side might not fully guarantee the behavior. If you guarantee that the all DML queries are executed only in your application and you can control the app, then maybe a solution in the application side might solve your need. For example allow only single user to connect to your warehouse for the task.

0 Votes 0 ·
michaelchau2 avatar image
0 Votes"
michaelchau2 answered pituach edited

it's a bit pain point for me right now ~really don't know the reason that it doesn't include in DW . as well as the foreign key ………………………...

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I think it is simple to explain or at least understand why it does not exists or at least why it is more complex to implement.

Azure Data warehouse (DW) can be spread over multiple physical machines. Sync the identity can cost a lot and it might reduce performance dramatically. DW meant to be used as warehouse and not as OLTP database.

Notice that other features related to this are not supported. For example data type ROWVERSION (TIMESTAMP) is not supported, and I mentioned before SEQUENCE is not supported, even create unique constraints are not supported... I assume it is all related to the architecture of the DW

I remind you that even the Transact-SQL commend are not exactly the same as SQL Server

Are you sure that you need DW and not SQL Server? Why don't you use Azure SQL Database for example?

0 Votes 0 ·
michaelchau2 avatar image
0 Votes"
michaelchau2 answered

Hi. thanks . for data modelling (from DW) , I still need the "unique key" so to relate the tables ….

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.