Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,088 questions
Record count difference during update query execution.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(230.39999999999998, 23%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENK%3C/text%3E%3C/svg%3E)
NIKHIL KUMAR
101
Reputation points
We are trying to update around 5 millions of data in Azure SQL DWH at a time.
While updating the same it has been observed the count of records on table is getting frequently changed, until the update query is completed.
What could be underlying reason for the same ? And how can it be made sure that count of records in table doesn't change during update query is being executed.
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 61%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
Bruce (SqlWork.com) 68,406 Reputation points2024-12-11T20:40:32.33+00:00 sql does not update in place. 5 million updates is 5 million inserts and 5 million deletes. if you do a dirty read, you may see the new rows.
-
Oury Ba-MSFT 19,826 Reputation points Microsoft Employee2024-12-11T22:15:32.18+00:00 NIKHIL KUMAR Thank you for reaching out.
You can utilize a suitable isolation level like serializable or use locking hints to lock the table exclusively to ensure the consistency of your data during large update operations in Azure SQL Data Warehouse (Azure Synapse Analytics). The visibility of changes during an update operation depends on the isolation level set for the transaction
SET TRANSACTION ISOLATION LEVEL (Transact-SQL) - SQL Server
Regards,
Oury
-
Erland Sommarskog 114.2K Reputation points MVP2024-12-11T22:22:38.4066667+00:00 Nah, that is necessarily not correct. SQL Server is perfectly able to perform in-place updates.
Then again, this relates to Azure Synapse Analytics, I assume since Nikhil says Azure SQL DWH. I am less versed in how things work in Synapse, but it is a distributed environment, so things work a little differently.
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(268.8, 8%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EST%3C/text%3E%3C/svg%3E)
Smaran Thoomu 18,625 Reputation points Microsoft Vendor2024-12-12T17:26:29.3233333+00:00 Hi @NIKHIL KUMAR
Could you please confirm whether you are referring to Azure SQL Data Warehouse (Azure SQL DW) or Azure Synapse Analytics SQL (serverless or dedicated pools)? This will help us better understand your setup and provide more accurate guidance. Thank you! -
Smaran Thoomu 18,625 Reputation points Microsoft Vendor
2024-12-13T16:31:55.79+00:00 @NIKHIL KUMAR We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sign in to comment
1 answer
Sort by: Most helpful
-
Deleted
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more