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

Original: Does SQL Server really go to update or ignore when the update value is the same as the original value?

Consider the following scenario:

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

Update Tbname Set name='abc'--The original value of name is the ABCwhere ID = 1

Again such as:

Update Tbname Set name='abc'--The original value of name is ABCwhere name = ' ABC '

Next we will actually test:

-- Microsoft SQL Server R2 (SP1)-10.50.2500.0 (X64)   June 00:54:03   Copyright (c) Microsoft Corporation  

1. First we shut down the checkpoint, here to use a traceflog 3505, specific information see here;

DBCC TRACEON (3505);

2. Prepare the test data:

CREATE DATABASEdb_testGO Usedb_testGOCREATE TABLET (aINT, bCHAR(1),   CONSTRAINTpk_tPRIMARY KEY CLUSTERED(a)); INSERT  intoTVALUES(1,'A');INSERT  intoTVALUES(2,'B');INSERT  intoTVALUES(3,'C');INSERT  intoTVALUES(4,'D');INSERT  intoTVALUES(5,'E'); CHECKPOINT;

3. View the transaction log

SELECT [currentLSN] [Transaction ID] , Allocunitname  from Fn_dblog (nullnull);

The following results are obtained:

Here is a record of the previous checkpoint of Step 2, at which time only two records

4. Look at the page condition of the table you just created

DBCC IND ('db_test','t',1);

Results:

We can see that the above page78 is the page that contains the 5 data you just inserted. (Pagetype=1 is DataPage, pagetype=10 is Iam Page)

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

UPDATETSETB= 'C'WHEREA=3; --View LogsSELECT [Current LSN], operation, Context,[Transaction ID], Allocunitname fromFn_dblog (NULL,NULL); --View Dirty DataSelect *  fromsys.dm_os_buffer_descriptorswheredatabase_id= db_id() andIs_modified= 1Order  bypage_id;

The results are as follows:

From the above results, we see a record of the transaction log, but not our table T, but sys.sysobjvalues.clst, what is it? From the online documentation, check the following:

Sys.sysobjvalues   exist in each database. Each regular value attribute for an entity has a corresponding row.

From the transaction log, SQL Server did not really go to update this record, and then we look at the dirty data to see if there is a modification to this page:

The modified PageID from the memory above is 152, not the Page78 of the table T.

From this we can assume that SQL Server does not actually make an update operation that is the same as the original value.

6. If we update a few of the same operations as the original value, such as:

UPDATETSETB= 'D'WHEREA=4; -- View Logs SELECT [Current LSN], operation, Context,[Transaction ID], Allocunitname fromFn_dblog (NULL,NULL); -- View Dirty data Select *  fromsys.dm_os_buffer_descriptorswheredatabase_id= db_id() andIs_modified= 1Order  bypage_id;

The results are as follows:

You can see that the transaction log does not add new records, the dirty data does not change, the data is still just.

7. If we manually checkpoint at this time, then do an update the original value operation?

CheckpointGOUPDATETSETB= 'E'WHEREA=5; -- View Logs SELECT [Current LSN], operation, Context,[Transaction ID], Allocunitname fromFn_dblog (NULL,NULL); -- View Dirty data Select *  fromsys.dm_os_buffer_descriptorswheredatabase_id= db_id() andIs_modified= 1Order  bypage_id;

The results are as follows:

8. What happens if we update a different value?

UPDATETSETB= 'Z'WHEREA=1; -- View Logs SELECT [Current LSN], operation, Context,[Transaction ID], Allocunitname fromFn_dblog (NULL,NULL); -- View Dirty data Select *  fromsys.dm_os_buffer_descriptorswheredatabase_id= db_id() andIs_modified= 1Order  bypage_id;

The results are as follows:

We can clearly see the log of its update and the Dirty data page.

9. So, as can be seen from the above test results, if the update value is the same as the original value, SQL Server will not really do one of these operations, but ignored.

10. This conclusion can also be demonstrated through the tool ApexSQL, which only records the insert and the last update;

11. Finally, remember DBCC TRACEOFF (3505);

This article basic reference: http://www.bobpusateri.com/archive/2010/10/updates-that-really-arent/

The

Update value is the same as the original value, does SQL Server really go to update or ignore it?