I would not be so sure.

I changed the script to bring out more info:

USE [TEST]

GO

CREATE TABLE [dbo].[Test](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](100) NULL

) ON [PRIMARY]

insert into Test (Name) Values ('TOM')

insert into Test (Name) Values ('Andy')

insert into Test (Name) Values ('Bob')

insert into Test (Name) Values ('Tim')

Checkpoint -- all dirty pages to disk, truncate log

SELECT OBJECT_ID('[dbo].[Test]') ObjID, * FROM TEST

Checkpoint -- all dirty pages to disk, truncate log

UPDATE test

SET name = 'Tom'

WHERE NAME = 'Tom'

SELECT [Current LSN] ,

Operation ,

Context ,

[Transaction ID] ,

AllocUnitId ,

AllocUnitName ,

[Checkpoint Begin] ,

[Num Transactions] ,

[Checkpoint End] ,

[Dirty Pages] ,

[Log Record] FROM fn_dblog(NULL, null)

Checkpoint -- all dirty pages to disk, truncate log

SELECT * FROM TEST

DECLARE @I int

SET @I = 4

WHILE @I > 0

BEGIN

BEGIN TRANSACTION

UPDATE test

SET name = NAME

WHERE @I%2 = 0 OR name <> NAME

EXEC sp_lock

COMMIT

SELECT [Current LSN] ,

Operation ,

Context ,

[Transaction ID] ,

AllocUnitId ,

AllocUnitName ,

[Checkpoint Begin] ,

[Num Transactions] ,

[Checkpoint End] ,

[Dirty Pages] ,

[Log Record] FROM fn_dblog(NULL, null)

Checkpoint -- all dirty pages to disk, truncate log

SET @I = @I - 1

END

GO

Please run the code with Execution Plan recorded.

1. Define "the same".

the code

UPDATE test

SET name = 'Tom'

WHERE NAME = 'Tom'

updates the value with the same, according to the column definition.

But for some reason SQL Server ignores the collation settings and actually writes the page.

So, it's not "the same" by data definition, it's "the same" by binary contents of the page(s).

2. Execution plan shows that "even" updates have 4 records committed to the UPDATE part, and "odd" ones have 0 records committed.

3. Messages show "(4 row(s) affected)" for "even" updates and "(0 row(s) affected)" for "odd" updates.

4. SP_LOCK indicates exclusive locks applied on each of 4 records in "even" updates, when condition name <> NAME is not applied.

To me it means that SQL Server treats those records as perfectly "dirty".

5. "Even" updates add LOP_BEGIN_XACT and LOP_COMMIT_XACT to the log, when "Odd" ones do not do this.

But you're right - those updates don't end up in the log file.

Considering all of the above I can see only one explanation:

1. UPDATE actually writes to the pages, regardless if the new values are the same or different from the old values.

But it writes to the pages in memory.

2. CHECKPOINT compares binary contents of the pages in memory and writes to disk only those ones which have become different after completed transactions.

When same values are updated there is a record about a committed transaction, so CHECKPOINT has to verify the dirty pages and write to the disk any ones that have been changed. When the query filters out the same values there is no record about a committed transaction, so CHECKPOINT will not even start analysing contents of the pages.

3. DMV shows the outcome of CHECKPOINT, not UPDATE, therefore the pages updated in memory don't appear there.