Behavior update

Question

When I made a mistake in the text of the update request, I found an unpredictable result of the request. Here is the request text for the update.

DECLARE @T TABLE (Id int,[Name] nvarchar(100),RNA int) INSERT INTO @T(Id,[Name]) SELECT [Id],[Name] FROM (VALUES (1,N'D'), (2,N'B'), (3,N'S'), (4,N'A'), (5,N'F') ) AS vtable([Id],[Name]) UPDATE @T SET RNA = T.RN FROM ( select PP.Name,ROW_NUMBER() OVER(ORDER BY PP.Name) RN,PP.RNA from @T PP ) T select * from @T 

I know where the error was made:

 UPDATE @T 

it should be

 UPDATE T 

But why the result (with a "bad" request) looks like this:

 Id Name RNA ---- ----- ------- 1 D 1 2 B 5 3 S 1 4 A 5 5 F 1 

I suspect that the 1 and 5 values ​​are MIN (Id) and MAX (Id). The execution plan looks like this:

Will such a situation be the same in every situation with such an error?

If so, does this behavior have any practical significance?

Answer 1

The situation will not be the same for all errors. You have a non-deterministic update statement, i.e. Theoretically, any of the RN values ​​in your subquery T can be applied to any of the values ​​in @T . You mainly use the UPDATE version:

 SELECT * FROM @ta CROSS JOIN ( SELECT TOP 1 PP.Name, ROW_NUMBER() OVER(ORDER BY PP.Name) RN, PP.RNA FROM @T PP ORDER BY NEWID() ) T OPTION (FORCE ORDER); 

The online manual states:

The results of the UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each updated column, that is, if the UPDATE statement is not deterministic.

Interestingly, if you run above, each time you get a different result (ban 1/25 to get the same result twice in a row), if you delete random sorting with NEWID() you will get the same RN value for each row, but the update successively returns the same results with two different RN s. I am not surprised that the result is not consistent with any random order, because without any changes to the data and without a random factor, I would expect the optimizer to come up with the same execution plan no matter how many times it is executed.

Since your update request does not indicate explicit ordering, the order is determined by the order of the entries on the sheet, if the order of the entries changes, the result changes. This can be shown by inserting @T records into a new table with different identifiers

 DECLARE @T2 TABLE (Id int,[Name] nvarchar(100),RNA int); INSERT @T2 SELECT id, Name, NULL FROM @T ORDER BY ROW_NUMBER() OVER(ORDER BY NEWID()) OPTION (FORCE ORDER); UPDATE @T2 SET RNA = T.RN FROM ( select PP.Name,ROW_NUMBER() OVER(ORDER BY PP.Name) RN,PP.RNA from @T2 PP ) T SELECT * FROM @T2; 

I see no reason why this is always the minimum or maximum value of RN , although, I think, you will need to go deeper into the optimizer to find this. This is probably a new question that is better suited for sharing dba stacks .