Few facts about INSERTED & DELETED tables of SQL Server

The logical tables INSERTED and DELETED of SQL Server are accessible only inside triggers. The schema of the logical tables is similar to the schema of table to which triggers are attached. These tables hold data of new and old states of rows modified using DML statements like UPDATE, DELETE and INSERT. For example if a record is updated using an UPDATE statement, the INSERTED table holds the newly updated row data and DELETED tables contains previous version of updated data rows. The following are few interesting facts about these tables

1. In SQL Server 2000, these logical tables internally refer to database transaction log to provide data when user queries them.
2. In SQL Server 2005, these logical tables are maintained in tempdb and they are maintained using the new technology Row versioning.
3. Accessing of logical tables is much faster in SQL Server 2005 when compared to SQL Server 2000 as the load is removed from transaction log and moved to tempdb.
4. Logical tables are never indexed. So, if you are going to loop through each and every record available in these tables, then consider copying data of logical tables to temporary tables and index them before looping through.