I recently was bitten by some not so obvious behavior with DBCC OPENTRAN() that I would like to share.  Basically this command is supposed to show you the oldest open transaction within the specified database or the current one if none is specified. If you run the example below you can see the expected behavior.

USE Tempdb ;
GO
CREATE TABLE [dbo].[T1]([Col1] int NOT NULL, [Col2] char(3) ) ;
GO
INSERT INTO [dbo].[T1] ([Col1], [Col2]) VALUES (101, 'abc');
GO
BEGIN TRAN ;

    UPDATE [dbo].[T1] SET [Col2] = 'abc' WHERE [Col1] = 101 ;
GO

DBCC OPENTRAN();

ROLLBACK TRAN;
GO
DROP TABLE [dbo].[T1];
GO 

 This should show you something similar to this:

But now add a PK constraint into the mix and run it again. 

USE Tempdb ;
GO
CREATE TABLE [dbo].[T1]([Col1] int NOT NULL, [Col2] char(3) ) ;
GO
ALTER TABLE [dbo].[T1] ADD CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED ([Col1] ASC) ;

GO
INSERT INTO [dbo].[T1] ([Col1], [Col2]) VALUES (101, 'abc');
GO
BEGIN TRAN ;

    UPDATE [dbo].[T1] SET [Col2] = 'abc' WHERE [Col1] = 101 ;
GO

DBCC OPENTRAN();

ROLLBACK TRAN;
GO
DROP TABLE [dbo].[T1];
GO

You now get this:

   No active open transactions.
   DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 Wait a minute, what's going on here. Are you telling me that just by adding a PK I changed the transaction? Well yes & no. If you look closely at the UPDATE statement it is updating the value with the same value that it already had. If you substitute the UPDATE with the one below you will get the expected output from DBCC OPENTRAN().

    UPDATE [dbo].[T1] SET [Col2] = 'xyz' WHERE [Col1] = 101 ;
 

In reality the transaction is not logged even if we create a clustered index vs. the PK constraint. A Non-Clustered index has the same effect as no index. So what does this all mean?  Based on some assumptions that I and some fellow MVP's had regarding this along with some further testing I came to two conclusions.

1.  As expected the Clustered Index or PK allows SQL Server to pinpoint the row and optimize the work done. But it must also be deeper rooted than that when it comes to how it actually logs this information in the transaction log.  

2.  The Engine is smart enough to realize that no changes have actually been made and does not log anything into the transaction log when the column in the WHERE clause has a Clustered index or PK constraint.

Since DBCC OPENTRAN() looks into the Transaction Log for these open transactions we can assume that there are certain optimizations built into the engine that minimize logging under the right conditions. So if you ever wonder why OPENTRAN isn't returning what you expect you should look to see if this may be the reason.