How triggers are implemented in SQL 2005/2008?

Triggers operate on DELETED and INSERTED rows as part of DML operations on a table.

Before SQL Server 2005, the trigger logic constructed these rows by traversing the UNDO/REDO logs. This may cause the disk head to move back and forth as the SQL Server will need to traverse the older records thereby compromising the IO throughput of log disk. Log disk is typically expected to have sequential writes given that transactional rollbacks are not that common. Trigger implementation disrupts that an application with heavy usage of triggers can potentially cause IO bottleneck on the log disk.

Starting with SQL Server 2005, the trigger implementation was changed to use row versions. So now, the DELETED and INSERTED rows are created as a row versions and a handle to these rows is attached to the transaction.
Row versions take the pressure off log disk but at the expense of the TempDB.


How to identify the list of compression-enabled tables in SQL 2008

SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]
,[index_id] as [IndexID_on_Table]
FROM sys.partitions
INNER JOIN sys.objects
ON sys.partitions.object_id = sys.objects.object_id
WHERE data_compression > 0
AND SCHEMA_NAME(sys.objects.schema_id) <>’SYS’
ORDER BY SchemaName, ObjectName