How does sql server choose values in an update statement where there are multiple options?

by

It sets all of the results to the Data. Which one you end up with after the query depends on the order of the results returned (which one it sets last).

Since there’s no ORDER BY clause, you’re left with whatever order Sql Server comes up with. That will normally follow the physical order of the records on disk, and that in turn typically follows the clustered index for a table. But this order isn’t set in stone, particularly when joins are involved. If a join matches on a column with an index other than the clustered index, it may well order the results based on that index instead. In the end, unless you give it an ORDER BY clause, Sql Server will return the results in whatever order it thinks it can do fastest.

You can play with this by turning your upate query into a select query, so you can see the results. Notice which record comes first and which record comes last in the source table for each record of the destination table. Compare that with the results of your update query. Then play with your indexes again and check the results once more to see what you get.

Of course, it can be tricky here because UPDATE statements are not allowed to use an ORDER BY clause, so regardless of what you find, you should really write the join so it matches the destination table 1:1. You may find the APPLY operator useful in achieving this goal, and you can use it to effectively JOIN to another table and guarantee the join only matches one record.

More Related Contents:

  1. Subquery in select not working in SQL Server
  2. Get first day of week in SQL Server
  3. What is the significance of 1/1/1753 in SQL Server?
  4. How to create jobs in SQL Server Express edition
  5. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  6. How to delete large data of table in SQL without log?
  7. Insert Update trigger how to determine if insert or update
  8. SQL Server: UPDATE a table by using ORDER BY
  9. What is the best way to truncate a date in SQL Server?
  10. Do I really need to use “SET XACT_ABORT ON”?
  11. What is a Trusted Connection?
  12. Understanding SQL Server LOCKS on SELECT queries
  13. Do while loop in SQL Server 2008
  14. How do I pass value to a stored procedure parameter in OLE DB Source component?
  15. Handling date in SQL Server
  16. How to get difference of days/months/years (datediff) between two dates?
  17. How do you change the datatype of a column in SQL Server?
  18. What happens if you don’t commit a transaction to a database (say, SQL Server)?
  19. Dynamically assign filename to excel connection string
  20. SQL Server Group by Count of DateTime Per Hour?
  21. Temporarily disable all foreign key constraints
  22. How to import a DBF file in SQL Server
  23. SQL Server Dependencies
  24. T-SQL query to show table definition?
  25. How can I insert binary file data into a binary SQL field using a simple insert statement?
  26. Why do I get the error “Xml data type is not supported in distributed queries” when querying a linked server for non-xml data?
  27. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  28. Date range overlapping check constraint
  29. Update SQL with consecutive numbering
  30. Nested transactions in Sql Server

Leave a Comment