This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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 ?
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
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 ………………………...
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?
Hi. thanks . for data modelling (from DW) , I still need the "unique key" so to relate the tables ….
Are you sure it's incremental ? or random ?
No, my mistake @Michael
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
It's good question @Michael
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.
Sign in to comment