Inserted and Deleted tables

Hi:

Can any of the experts please confirm the fact that Inserted and deleted tables in SQL Server 2005 are stored in tempdb?. If so, how can I query them in tempdb ( A code snippet would be useful).

Thanks

AK

Hi Ankith,

Inserted and deleted table are created in Trigger execution time and can't possible query them, only in trigger execution time.

Regards,

|||

Thanks for the reply. I still would like to know if they are stored in tempdb though in SQL Server 2005 Vs getting stored in memory in SQL Server 2000.

Any pointers?

Thanks

|||inserted/deleted are memory-resident tables. You cannot access them outside of the execution context.|||Thanks OJ. So what I might have read is probably talking of row versioning that uses tempdb. Thanks again for the clarification.|||You shouldn't be allowed to read internal worktable even if it resides in tempdb. If you could, this would be a major security hole. ;-)|||

Hi OJ:

<You shouldn't be allowed to read internal worktable even if it resides in tempdb. If you could, this would be a major security hole. ;-)>

Right I agree with you. However what does the following paragraph mean?

URL is :http://www.sqlmag.com/Article/ArticleID/93465/sql_server_93465.html

The first impression i get when i read the paragraph is the tables are stored in tempdb in 2005. This is where I am confused. Can you please elaborate further?.

Thanks

AK

Triggers have long been a part of SQL Server and were the only feature prior to SQL Server 2005 that provided any type of historical (or versioned) data. Triggers can access two pseudo-tables called deleted and inserted. Inside the trigger, you can access these two tables as if they were real tables, but accessing them while not in a trigger results in an unknown object error. If the trigger is a DELETE trigger, the deleted table contains copies of all the rows deleted by the operation that caused the trigger to fire. If the trigger is an INSERT trigger, the inserted table contains copies of all the rows inserted by the operation that caused the trigger to fire. And if the trigger is an UPDATE trigger, the deleted table contains copies of the old versions of the rows, and the inserted table contains all the new versions. Before SQL Server 2005, SQL Server would determine which rows were included in these pseudo-tables by scanning the transaction log for all the log records belonging to the current transaction. Any log records containing data inserted in or deleted from the table to which the trigger was tied were included in the inserted or deleted tables.

In SQL Server 2005, these pseudo-tables are created by using RLV technology. When data-modification operations are performed on a table that has a relevant trigger defined, SQL Server creates versions of the old and new data in the version store in tempdb.This occurs whether or not either of the snapshot-based isolation levels has been enabled.When a SQL Server 2005 trigger accesses the deleted table, it retrieves the data from the version store.When a trigger needs to determine which rows in the table are new rows and accesses the inserted table, SQL Server again gets the inserted table rows from the version store.

|||The article describes how sqlserver physically create/maintain the inserted/deleted table. For a very long time now, tempdb has always been used as the workspace for sqlserver. It uses tempdb to hold the paged data that can't fit in the allowable memory - @.table variable is the best example of this. So, in the new sql2k5, instead of scanning the log to materialize the inserted/deleted table, it goes ahead and store a copy of updated data in tempdb. This will make the materialization faster because it does not have to scan the entire log.

Long story short, inserted and deleted table are very special table. Regardless of how they're materialized, they can only be accessed within the execution (trigger) context.|||

I am curious why you would want to do this in the first place. Are you simply trying to access the data before and after the record is created. In a trigger you can access the date using inserted and deleted as a table name.

select * from inserted

Also, it is interesting to point out that an update consists of both an insert and a delete.

|||Thanks OJ for your explanation.sql