Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1574

Re: update field with the same value

Show all headers | View raw

migurus (migurus@yahoo.com) writes:
> To see all pros and cons (complexity, extra effort, etc) I'd like to
> undesrtand is SQL Server smart not
> to update columns whose value was not changed?
 
SQL Server updates the row, and logs the row, so on this level it does not
matter. What may be relevant is indexes. If an unchanged colunm appears in
an index, will SQL Server still update those indexes?

To study this, try this batch in the Northwind database:

BEGIN TRANSACTION

UPDATE Orders
SET    CustomerID = 'ALFKI',
       EmployeeID = 9
WHERE  OrderID = 11000

Then look at the locks (for instance with beta_lockinfo, 
http://www.sommarskog.se/sqlutil/beta_lockinfo.html.) You will see that
there are locks on the indexes CustomerOrders and EmployeeOrders.

Now rollback, and change 9 to 2 (which is the current value for order 1100)
and run again and study the locks. You will see that there is no lock on
EmployeeOrders. Thus, SQL Server did realize that it not have to update 
the index.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next | Find similar

Thread

Re: update field with the same value Erland Sommarskog <esquel@sommarskog.se> - 2011-02-12 00:03 +0100

csiph-web