October 19, 2008 at 3:25 pm
For example if I have inserted and deleted triggers, for how long does the data stays in the inserted and deleted tables which have been created by the triggers.
Thanks!
October 19, 2008 at 7:45 pm
as long as the trigger is running.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 20, 2008 at 2:17 am
Inserted and deleted aren't real tables. They're virtual tables that are materialised from the transaction log in SQL 2000 and from the row version store in 2005 and higher. They are only visible within a trigger and they're only there for the duration of the trigger.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2008 at 6:54 am
GilaMonster (10/20/2008)
They're virtual tables that are materialised ... from the row version store in 2005 and higher.
Gail: Is this true even if none of the "Snapshot" features are turned on? (Not doubting you, just curious for my own sake 🙂 )
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 20, 2008 at 8:02 am
Yup. Row versioning is used by the DB engine internally, even if Snapshot isolation isn't enabled in any DB. Other things that use row versions:
Online index rebuilds
MARS
http://www.sqlmag.com/Articles/ArticleID/93465/93465.html
You'll need a SQLMag subscription to read the full article, I'll just reproduce a excerpt here
SQL Server Magazine
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 Row-level versioning 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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2008 at 8:51 am
Thanks, Gail.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply