When the update value is the same as the original value, does SQL Server actually update or ignore it?

Consider the following:

When the update value is the same as the original value, does SQL Server actually update or ignore it? For example:

 
UpdateTbnameSetName='ABC' --The original value of name is ABC.WhereID=1
 

Another example is:

UpdateTbnameSetName='ABC' --The original value of name is ABC.WhereName='ABC'
 

Next we will test:

 
--Microsoft SQL Server 2008 R2 (SP1)-10.50.2500.0 (x64) JUN 17 2011 00:54:03 copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <x64> (build 6002: Service Pack 2)
 

 
1. First, we should close the checkpoint. Here we use traceflog 3505. For more information, see here;

 
DBCCTraceon (3505);
 

2. Prepare test data:

 Create   Database  Db_test  Go  Use  Db_test  Go  Create   Table  T (  Int  , B  Char ( 1  ),  Constraint Pk_t Primary   Key   Clustered  ());  Insert   Into T Values ( 1 , '  A  '  ); Insert   Into T Values ( 2 , '  B  '  );  Insert   Into T Values ( 3 , '  C  ' );  Insert   Into T Values ( 4 , '  D  '  );  Insert   Into T Values ( 5 , '  E '  );  Checkpoint ;

3. View transaction logs

 
Select [Current lsn], Operation, context,[Transaction ID], AllocunitnameFromFn_dblog (Null,Null);
 

The following result is displayed:

The checkpoint record of step 2 is displayed here. At this time, there are only two records

4. view the page of the newly created table.

DBCCInd ('Db_test','T',1);
 

Result:

We can see the abovePage78Is the page where the inserted five data entries are located. (pagetype = 1 is datapage, pagetype = 10 is Iam page)

5. Execute an update SQL statement, and then read the transaction log and dirty data in the memory.

 Update  T  Set B =   '  C  ' Where A =  3  ;  --  View logs  Select   [  Current lsn  ] , Operation, context, [  Transaction ID  ]  , Allocunitname  From Fn_dblog ( Null , Null  );  --  View dirty data  Select   *   From  SYS. dm_ OS _buffer_descriptors  Where Database_id =   Db_id () And Is_modified =   1  Order  By Page_id;

The result is as follows:

From the above results, we can see that there is a transaction log record, but not our table t, but SYS. sysobjvalues. clst. What is it? Check from the online document:

 
SYS. sysobjvalues exists in each database. Each common value attribute of an object has a corresponding row.
 

From the transaction log, SQL server does not actually update this record. Then, let's see if there are any modifications to this page in the dirty data:

We can see from the above that the modified pageid in the memory is 152, not the page78of table t.

Therefore, we can think that SQL server does not actually perform an update operation with the same original value.

6. If we update several operations with the same original value, for example:

 Update  T  Set B =   ' D  '  Where A =  4  ;  --   View logs  Select   [  Current lsn  ] , Operation, context, [  Transaction ID  ]  , Allocunitname From Fn_dblog ( Null , Null  );  --   View dirty data  Select   *   From  SYS. dm_ OS _buffer_descriptors  Where Database_id =   Db_id () And Is_modified =  1  Order   By Page_id;

The result is as follows:

We can see that no new records are added to the transaction log, and the dirty data remains unchanged.

7. What if we manually checkpoint and then perform an update operation on the original value?

 Checkpoint  Go  Update  T  Set B =   '  E  '  Where A = 5  ;  --   View logs  Select   [  Current lsn  ] , Operation, context, [  Transaction ID  ]  , Allocunitname  From Fn_dblog ( Null , Null );  --   View dirty data  Select   *   From  SYS. dm_ OS _buffer_descriptors  Where Database_id =   Db_id () And Is_modified =   1  Order   By Page_id;

The result is as follows: