To start off, an updatable CTE, derived table or view may be the target of an `UPDATE` directly, even if it has multiple base tables, as long as all columns are from the same source table. But they cannot be the target of a `DELETE`, even if columns from only one table are selected. ``` Msg 4405 Level 16 State 1 View or function 'x' is not updatable because the modification affects multiple base tables. ``` To get around this restriction, I attempted to use a `MERGE` against a dummy table. (Obviously this simplistic example could be written using a `DELETE...WHERE EXISTS` or by putting one table as the merge source, but the point stands. The original used `ROW_NUMBER` so these weren't possible.) ``` WITH Joined AS ( SELECT t1.* FROM t1 JOIN t2 ON t2.id1 = t1.id1 ) MERGE Joined USING (VALUES(0)) v(dummy) ON 1=0 WHEN NOT MATCHED BY SOURCE THEN DELETE; ``` db<>fiddle This was actually allowed. But what I found was that the table that was modified did not depend on the columns being selected, or their order. It depended purely on the order that the tables were joined. This, to my mind seems completely buggy behaviour. Experimenting with `THEN UPDATE` shows far more sensible behaviour: it depends on which columns are used in the `THEN UPDATE` clause, in the same way as a normal `UPDATE` statement. So, I think SQL Server should: - Either continue allowing updatable CTEs to be deleted from, but ensure that only one table's columns are selected (like an `UPDATE`), ensuring no ambiguity. - Or completely disallow `THEN DELETE` in `MERGE` when the source is an updatable CTE with multiple base tables. Do I have some misunderstanding in how updatable views work, or is there an actual bug here? A bug report has now been filed on Azure Feedback. Please vote for it here.

Within a .NET 6 application the EntityFramework is used to keep track of entities and to update related fields. To improve bulk updates, the EfCore.BulkExtensions package has been added. During a .BulkUpdate a SQL statement is generated and executed (by the described package). I noticed the `WITH (HOLDLOCK)` part. I've read some documentation about the hint and possible race conditions, but do they occur in a MERGE with only the UPDATE command? In short, is this WITH (HOLDLOCK) hint necessary in case the MERGE statement only includes an UPDATE command or can it safely be removed? Sample query: ``` MERGE TargetProducts WITH (HOLDLOCK) AS Target USING SourceProducts AS Source ON Source.ProductID = Target.ProductID WHEN MATCHED THEN UPDATE SET Target.ProductName = Source.ProductName, Target.Price = Source.Price; ```

I found again an issue with SQL Server and MERGE statement and need some confirmation. I can reproduce my issue constantly on a Azure Database (but not on a on premise SQL Server 2017/2019). Please execute following steps (step by step, not in one command execution)! 1) Script for Schema: ``` CREATE TABLE [dbo].[ImpactValueHistory] ( [Rn] BIGINT NOT NULL, [ImpactId] UNIQUEIDENTIFIER NOT NULL, [ImpactValueTypeId] INT NOT NULL, [Date] DATE NOT NULL, [Value] DECIMAL(38, 10) NOT NULL, [ValidFrom] DATETIME2 NOT NULL CONSTRAINT [DF_ImpactValueHistory_ValidFrom] DEFAULT CONVERT(DATETIME2, '0001-01-01'), [ValidTo] DATETIME2 NOT NULL CONSTRAINT [DF_ImpactValueHistory_ValidTo] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'), [ImpactPeriodId] INT NOT NULL, [NormalizedValue] DECIMAL(38, 10) NOT NULL, ) GO CREATE CLUSTERED COLUMNSTORE INDEX [COLIX_ImpactValueHistory] ON [dbo].[ImpactValueHistory]; GO CREATE NONCLUSTERED INDEX [IX_ImpactValueHistory_ValidFrom_ValidTo_ImpactId_DimensionItemId] ON [dbo].[ImpactValueHistory] ([ValidFrom], [ValidTo], [ImpactId], [ImpactValueTypeId], [Date]); GO CREATE TABLE [dbo].[ImpactValue] ( [Rn] BIGINT NOT NULL IDENTITY(1,1), [ImpactId] UNIQUEIDENTIFIER NOT NULL, [ImpactValueTypeId] INT NOT NULL, [Date] DATE NOT NULL, [Value] DECIMAL(38, 10) NOT NULL, [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL CONSTRAINT [DF_ImpactValue_ValidFrom] DEFAULT CONVERT(DATETIME2, '0001-01-01'), [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL CONSTRAINT [DF_ImpactValue_ValidTo] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'), [ImpactPeriodId] INT NOT NULL, [NormalizedValue] DECIMAL(38, 10) NOT NULL, PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]), CONSTRAINT [PK_ImpactValue] PRIMARY KEY NONCLUSTE

I've got a SQL table that needs to be updated daily. There may or may not be queries against that table while the update is happening. It's around 500,000 rows. We have an issue where there is locking conflict when the job to update the table is running at the same time as a query against it. So I have rewritten the process to update the table as follows: ``` ALTER procedure [dbo].[Table_Generate] as declare @d datetime = getdate(), @c as int --Check temp tables IF OBJECT_ID('tempdb..#final') IS NOT NULL DROP TABLE #final IF OBJECT_ID('tempdb..#base') IS NOT NULL DROP TABLE #base --Get source data from linked server select ID, Reference, StartDate, EndDate, Description, SomeCode into #base from [LinkedServer].[Database].dbo.[A_View] --Generate row_hash select ID, Reference, StartDate, EndDate, Description, SomeCode, hashbytes('SHA2_256',( select ID, Reference, StartDate, EndDate, Description, SomeCode from #base sub where sub.ID = main.ID for xml raw)) as row_hash into #final from #base main select @c = count(*) from #final if @c >0 begin merge [The_Table_Staging] as target using #final as source on source.ID = target.ID --New rows when not matched by target then insert ( RunDate, ID, Reference, StartDate, EndDate, Description, SomeCode, Row_Hash ) values ( @d, source.ID, source.Reference, source.StartDate, source.EndDate, source.Description, source.SomeCode, source.row_hash) --Existing changed rows when matched and source.row_hash != target.row_hash then update set target.RunDate = @d ,target.Reference = source.Reference ,target.StartDate = source.StartDate ,target.EndDate

Sometimes for refactoring, I use a View to abstract the changes and use `INSTEAD OF` Triggers to mimic the previous functionality. I've run into error 414 (or 415) in the past UPDATE is not allowed because the statement updates view "%.*ls" which participates in a join and has an INSTEAD OF UPDATE trigger. I can rewrite the `UPDATE FROM` to a `MERGE` statement and that works. But why? I found these references, but none of them answers why. https://stackoverflow.com/questions/3085036/update-is-not-allowed-because-the-statement-updates-view-table-name-which-part https://social.msdn.microsoft.com/Forums/sqlserver/en-US/87c173b0-8f87-4aa3-b861-d40f23803a43/views-and-instead-of-update-trigger-limitation-why?forum=transactsql https://sqlserverfast.com/blog/hugo/2008/03/lets-deprecate-update-from/

I have a view with `instead of` triggers and I'm trying to use it with EF Core, which tries to batch inserts together in a form of `merge` statement. Here's my table and view: ``` create table tbl (id uniqueidentifier not null primary key, data nvarchar(max) null) go create view vwTbl as select * from tbl go create trigger vwTblInsert on vwTbl instead of insert as insert into tbl (id, data) select id, data from inserted go ``` Here's the approximate SQL that EF generates: ``` declare @inserted1 table ([id] uniqueidentifier); merge vwTbl using ( values (newid(), 'xxx') ) as i (id, data) on 1=0 when not matched then insert (id, data) values (i.id, i.data) output inserted.Id into @inserted1; select * from @inserted1 ``` Here's the error I'm getting: The column reference "inserted.id" is not allowed because it refers to a base table that is not being modified in this statement. The help page for the `merge` statement says: If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action done by a MERGE statement, it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement. I believe I fulfil this condition and I can't see any other relevant limitations. Why doesn't it work? It's EF who generates the query, not me. I have no control over it. I just want to understand why it doesn't work and if I can fix my trigger somehow to make it work.

I have a table for statistic values, it holds millions of records, which is defined like this: ``` CREATE TABLE [dbo].[Statistic] ( [Id] [INT] IDENTITY(1, 1) NOT NULL , [EntityId] [INT] NULL , [EntityTypeId] [UNIQUEIDENTIFIER] NOT NULL , [ValueTypeId] [UNIQUEIDENTIFIER] NOT NULL , [Value] [DECIMAL](19, 5) NOT NULL , [Date] [DATETIME2](7) NULL , [AggregateTypeId] [INT] NOT NULL , [JsonData] [NVARCHAR](MAX) NULL , [WeekDay] AS (DATEDIFF(DAY, CONVERT([DATETIME], '19000101', (112)), [Date]) % (7) + (1)) PERSISTED , CONSTRAINT [PK_Statistic] PRIMARY KEY NONCLUSTERED ([Id] ASC) ); CREATE UNIQUE CLUSTERED INDEX [IX_Statistic_EntityId_EntityTypeId_ValueTypeId_AggregateTypeId_Date] ON [dbo].[Statistic] ( [EntityId] ASC , [EntityTypeId] ASC , [ValueTypeId] ASC , [AggregateTypeId] ASC , [Date] ASC ); CREATE NONCLUSTERED INDEX [IX_Date] ON [dbo].[Statistic] ([Date] ASC); CREATE NONCLUSTERED INDEX [IX_EntityId] ON [dbo].[Statistic] ([EntityId] ASC) INCLUDE ([Id]); CREATE NONCLUSTERED INDEX [IX_EntityType_Agg_Date] ON [dbo].[Statistic] ([EntityTypeId] ASC, [AggregateTypeId] ASC, [Date] ASC) INCLUDE ([Id], [EntityId], [ValueTypeId]); CREATE NONCLUSTERED INDEX [IX_Statistic_ValueTypeId] ON [dbo].[Statistic] ([ValueTypeId] ASC) INCLUDE ([Id]); CREATE NONCLUSTERED INDEX [IX_WeekDay] ON [dbo].[Statistic] ([AggregateTypeId] ASC, [WeekDay] ASC, [Date] ASC) INCLUDE ([Id]); ALTER TABLE [dbo].[Statistic] ADD CONSTRAINT [PK_Statistic] PRIMARY KEY NONCLUSTERED ([Id] ASC); ``` During updates with merge, sql server locks the whole table instead of pages/rows, `@inTbl` is a key/value datatable passed as parameter ``` MERGE INTO Statistic AS stat USING (SELECT inTbl.EntityId, inTbl.Value FROM @p0 AS inTbl) AS src ON src.EntityId = stat.EntityId AND stat.EntityTypeId = @p1 AND stat.ValueTypeId = @p2 AND s

I was looking at PostgreSQL's `INSERT INTO .. ON CONFLICT (..) DO UPDATE ..` syntax and realized, you cannot do multiple unique constraint checks with it. I mean, you either refer to a composite unique index by the column names `ON CONFLICT (Name, Symbol)` (if the unique index is defined for these two columns), or you use the primary key. If you define two separate unique indexes for the columns, you can only check for one. ``` CREATE TABLE student (Id int primary key, Name varchar(50), Symbol varchar(50), CONSTRAINT col1_unique UNIQUE (Name), CONSTRAINT col2_unique UNIQUE (Symbol) ); INSERT INTO student (Id, Name, Symbol) VALUES (1, 'John', 'J'), (2, 'David', 'D'), (3, 'Will', 'W'); INSERT INTO student (Id, Name, Symbol) VALUES (4, 'Jeremy', 'J') on conflict(Name) DO UPDATE set Name = 'Jeremy'; ``` Could throw an error, saying `J` is a duplicate. However, this example is simply a bad design, because the Symbol should be in another table and be connected to the student table via a one to many relationship. Which is why I am wondering, maybe PostgreSQL's `on conflict` was designed this way, because you can ALWAYS restructure the tables in a way, where there is only a single unique index. Is it true or there is an another reason? Example fiddle: http://www.sqlfiddle.com/#!17/9c0ce

I have started looking at `MERGE` as an option that I would use in my application to process `UPSERT` transactions. Seems that a lot of SQL Server experts are recommending this approach. However, while exploring this method I found some common problems that `MERGE` can cause, for example in Use Caution with SQL Server's MERGE Statement by Aaron Bertrand. My system is state wide and I use ColdFusion on the back end. It is multi-thread and we expect a huge volume of requests to our database. Each of these `INSERT/UPDATE` transactions will handle a single row transaction. That means the user is inserting or updating one row at the time. I use transaction with rollback in ColdFusion when I call stored procedure. Is that enough or would be better to have that in SQL instead? Here is an example of my `MERGE` statement used in a stored procedure: ``` CREATE PROCEDURE [dbo].[SaveMaster] @RecordID INT = NULL, -- Auto increment ID @Status BIT = NULL, @Name VARCHAR(50) = NULL, @Code CHAR(2) = NULL, --Primary Key @ActionDt DATETIME = NULL, @ActionID UNIQUEIDENTIFIER = NULL AS MERGE dbo.Master WITH (HOLDLOCK) AS Target USING (SELECT @RecordID,@Status,@Name,@Code,@ActionDt,@ActionID) AS Source (RecordID,Status,Name,Code,ActionDt,ActionID) ON Target.RecID = Source.RecordID WHEN MATCHED THEN UPDATE SET Target.Status = Source.Status, Target.Name = Source.Name, Target.Code = Source.Code, Target.ActionDt = Source.ActionDt, Target.ActionID = Source.ActionID WHEN NOT MATCHED THEN INSERT( Status,Name,Code,ActionDt,ActionID )VALUES( Source.Status, Source.Name, Source.Code, Source.ActionDt, Source.ActionID ) OUTPUT inserted.RecID,$action as Action; ``` You will see that I use `WITH(HOLDLOCK)` to prevent primary key violations and deadlocks. After I read the article that is attached in my post, it seems that even having `HOLDLOCK` still you

From the PostgreSQL wiki, `MERGE` is typically used to merge two tables, and was introduced in the 2003 SQL standard. The `REPLACE` statement (a MySQL extension) or UPSERT sequence attempts an `UPDATE`, or on failure, `INSERT`. This is similar to `UPDATE`, then for unmatched rows, `INSERT`. Whether concurrent access allows modifications which could cause row loss is implementation independent. Further PostgreSQL's `INSERT ... ON CONFLICT DO NOTHING/UPDATE` is marketed as UPSERT and was added in 9.5 What then is `MERGE`? And how does it fit into the mix?