|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Are 'inserted' and 'deleted' tables or views?How are 'inserted' and 'deleted' implemented by SQL Server?
When I look at the execution plan of a trigger that selects from 'inserted', I can see that the user table is queried instead and aliased as 'inserted': >>> Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted]))Execution Tree -------------- BOL referers to inserted as "temporary, memory-resident tables to test the effects of certain data modifications". Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted' is instead a view on top of MY_TABLE that accesses the new version of a record, and deleted accesses the old version? I have been told recently, by a reliable source (Itzik Ben-Gan), that in
SQL 2000 the insert & deleted virtual tables accessible in triggers are implemented in memory from the transaction log of the DB (ie. it builds a table in RAM from reading the tlog of the database). At a guess I'd say that the execution plan tells you that MY_TABLE is being accessed because there's no better way to say, in an execution plan, that it's actually the transaction log that's being accessed. I guess you could interpret the execution plan step as "scan the change history for the MY_TABLE object", or something along those lines. In SQL 2005 the inserted & deleted tables are created in tempdb using the new row versioning technology that has been added to SQL 2005. HTH -- Show quoteHide quote*mike hodgson* |/ database administrator/ | mallesons stephen jaques *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907 *E* mailto:mike.hodgson@mallesons.nospam.com |* W* http://www.mallesons.com BGL wrote: >How are 'inserted' and 'deleted' implemented by SQL Server? > >When I look at the execution plan of a trigger that selects from 'inserted', >I can see that the user table is queried instead and aliased as 'inserted': > > > >Execution Tree >-------------- >Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted])) > >BOL referers to inserted as "temporary, memory-resident tables to test the >effects of certain data modifications". > >Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted' >is instead a view on top of MY_TABLE that accesses the new version of a >record, and deleted accesses the old version? > > > > > > > That's close, but there actually is not really any building going on. The rows are already in memory because the inserts or deletes have been logged. You can think of inserted and deleted as views of the transaction log containing the changed rows for the particular transaction.
Since the log records refer to the base table they are based on, the query plan uses that for the table name. "Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message news:eghs3%239ZFHA.796@TK2MSFTNGP09.phx.gbl... I have been told recently, by a reliable source (Itzik Ben-Gan), that in SQL 2000 the insert & deleted virtual tables accessible in triggers are implemented in memory from the transaction log of the DB (ie. it builds a table in RAM from reading the tlog of the database).At a guess I'd say that the execution plan tells you that MY_TABLE is being accessed because there's no better way to say, in an execution plan, that it's actually the transaction log that's being accessed. I guess you could interpret the execution plan step as "scan the change history for the MY_TABLE object", or something along those lines. In SQL 2005 the inserted & deleted tables are created in tempdb using the new row versioning technology that has been added to SQL 2005. HTH -- mike hodgson | database administrator | mallesons stephen jaques T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907 E mailto:mike.hodgson@mallesons.nospam.com | W http://www.mallesons.com BGL wrote: How are 'inserted' and 'deleted' implemented by SQL Server? When I look at the execution plan of a trigger that selects from 'inserted', I can see that the user table is queried instead and aliased as 'inserted': Execution Tree -------------- Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted])) BOL referers to inserted as "temporary, memory-resident tables to test the effects of certain data modifications". Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted' is instead a view on top of MY_TABLE that accesses the new version of a record, and deleted accesses the old version? Hi Mike,
I actually like the term views rather than virtual tables. What I said was that inserted and deleted are views on top of the section in the log that contains the change that fired the trigger. And since these log records are also buffered, chances are that if the section in the log is not really big, it's in cache. The reason it's important to realize this is that referring to inserted and deleted means reading from the log, postponing its other activities. Cheers, "Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message news:eghs3%239ZFHA.796@TK2MSFTNGP09.phx.gbl... I have been told recently, by a reliable source (Itzik Ben-Gan), that in SQL 2000 the insert & deleted virtual tables accessible in triggers are implemented in memory from the transaction log of the DB (ie. it builds a table in RAM from reading the tlog of the database).At a guess I'd say that the execution plan tells you that MY_TABLE is being accessed because there's no better way to say, in an execution plan, that it's actually the transaction log that's being accessed. I guess you could interpret the execution plan step as "scan the change history for the MY_TABLE object", or something along those lines. In SQL 2005 the inserted & deleted tables are created in tempdb using the new row versioning technology that has been added to SQL 2005. HTH -- mike hodgson | database administrator | mallesons stephen jaques T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907 E mailto:mike.hodgson@mallesons.nospam.com | W http://www.mallesons.com BGL wrote: How are 'inserted' and 'deleted' implemented by SQL Server? When I look at the execution plan of a trigger that selects from 'inserted', I can see that the user table is queried instead and aliased as 'inserted': Execution Tree -------------- Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted])) BOL referers to inserted as "temporary, memory-resident tables to test the effects of certain data modifications". Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted' is instead a view on top of MY_TABLE that accesses the new version of a record, and deleted accesses the old version? Thanks for the clarification (I was close - it was a long day for my
brain on Wednesday ;-) ). <OffTopic> Wow - posts from both Kalen & Itzik in the same thread. I've just given myself a big headache by listening to a Podcast on .NET Rocks (that went for about 100min - wmplayer sucked up 980MB of virtual RAM!) of Kim Tripp talking about snapshot & read committed snapshot isolations levels (among other topics) in SQL 2005. It took me about 3 hours because I kept having to go back and replay bits over and over again to understand what she was talking about. And that was immediately after listening to a 1 hour Podcast of Kalen talking about MSSQL history. (Kalen when are you going to do a course or seminar in Sydney (much better city than Melbourne)?) I downloaded another of Kim's Podcasts (about the same size) for the train trip home but I'm scared to start it for fear of exasperating my headache. :-\ </OffTopic> -- Show quoteHide quote*mike hodgson* |/ database administrator/ | mallesons stephen jaques *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907 *E* mailto:mike.hodgson@mallesons.nospam.com |* W* http://www.mallesons.com Itzik Ben-Gan wrote: > Hi Mike, > > I actually like the term views rather than virtual tables. What I said > was that inserted and deleted are views on top of the section in the > log that contains the change that fired the trigger. > And since these log records are also buffered, chances are that if the > section in the log is not really big, it's in cache. > The reason it's important to realize this is that referring to > inserted and deleted means reading from the log, postponing its other > activities. > > Cheers, > -- > BG, SQL Server MVP > www.SolidQualityLearning.com <http://www.SolidQualityLearning.com> > > > > "Mike Hodgson" <mike.hodgson@mallesons.nospam.com > <mailto:mike.hodgson@mallesons.nospam.com>> wrote in message > news:eghs3%239ZFHA.796@TK2MSFTNGP09.phx.gbl... > I have been told recently, by a reliable source (Itzik Ben-Gan), > that in SQL 2000 the insert & deleted virtual tables accessible in > triggers are implemented in memory from the transaction log of the > DB (ie. it builds a table in RAM from reading the tlog of the > database). > > At a guess I'd say that the execution plan tells you that MY_TABLE > is being accessed because there's no better way to say, in an > execution plan, that it's actually the transaction log that's > being accessed. I guess you could interpret the execution plan > step as "scan the change history for the MY_TABLE object", or > something along those lines. > > In SQL 2005 the inserted & deleted tables are created in tempdb > using the new row versioning technology that has been added to SQL > 2005. > > HTH > > -- > *mike hodgson* |/ database administrator/ | mallesons stephen jaques > *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907 > *E* mailto:mike.hodgson@mallesons.nospam.com |* W* > http://www.mallesons.com > > > > BGL wrote: > >>How are 'inserted' and 'deleted' implemented by SQL Server? >> >>When I look at the execution plan of a trigger that selects from 'inserted', >>I can see that the user table is queried instead and aliased as 'inserted': >> >> >> >>Execution Tree >>-------------- >>Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted])) >> >>BOL referers to inserted as "temporary, memory-resident tables to test the >>effects of certain data modifications". >> >>Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted' >>is instead a view on top of MY_TABLE that accesses the new version of a >>record, and deleted accesses the old version? >> >> >> >> >> >> >> Come to think of it, that's not a bad topic for a whitepaper: Internals
of triggers, inserted & deleted "views" and best practices regarding triggers (pointers like log activity is frozen while accessing inserted & deleted). I'd read that. Sounds like it'd be right up Kalen's alley. -- Show quoteHide quote*mike hodgson* |/ database administrator/ | mallesons stephen jaques *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907 *E* mailto:mike.hodgson@mallesons.nospam.com |* W* http://www.mallesons.com Mike Hodgson wrote: > Thanks for the clarification (I was close - it was a long day for my > brain on Wednesday ;-) ). > > <OffTopic> > > Wow - posts from both Kalen & Itzik in the same thread. I've just > given myself a big headache by listening to a Podcast on .NET Rocks > (that went for about 100min - wmplayer sucked up 980MB of virtual > RAM!) of Kim Tripp talking about snapshot & read committed snapshot > isolations levels (among other topics) in SQL 2005. It took me about > 3 hours because I kept having to go back and replay bits over and over > again to understand what she was talking about. And that was > immediately after listening to a 1 hour Podcast of Kalen talking about > MSSQL history. (Kalen when are you going to do a course or seminar in > Sydney (much better city than Melbourne)?) > > I downloaded another of Kim's Podcasts (about the same size) for the > train trip home but I'm scared to start it for fear of exasperating my > headache. :-\ > > </OffTopic> > > -- > *mike hodgson* |/ database administrator/ | mallesons stephen jaques > *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907 > *E* mailto:mike.hodgson@mallesons.nospam.com |* W* > http://www.mallesons.com > > > > Itzik Ben-Gan wrote: > >> Hi Mike, >> >> I actually like the term views rather than virtual tables. What I >> said was that inserted and deleted are views on top of the section in >> the log that contains the change that fired the trigger. >> And since these log records are also buffered, chances are that if >> the section in the log is not really big, it's in cache. >> The reason it's important to realize this is that referring to >> inserted and deleted means reading from the log, postponing its other >> activities. >> >> Cheers, >> -- >> BG, SQL Server MVP >> www.SolidQualityLearning.com <http://www.SolidQualityLearning.com> >> >> >> >> "Mike Hodgson" <mike.hodgson@mallesons.nospam.com >> <mailto:mike.hodgson@mallesons.nospam.com>> wrote in message >> news:eghs3%239ZFHA.796@TK2MSFTNGP09.phx.gbl... >> I have been told recently, by a reliable source (Itzik Ben-Gan), >> that in SQL 2000 the insert & deleted virtual tables accessible >> in triggers are implemented in memory from the transaction log of >> the DB (ie. it builds a table in RAM from reading the tlog of the >> database). >> >> At a guess I'd say that the execution plan tells you that >> MY_TABLE is being accessed because there's no better way to say, >> in an execution plan, that it's actually the transaction log >> that's being accessed. I guess you could interpret the execution >> plan step as "scan the change history for the MY_TABLE object", >> or something along those lines. >> >> In SQL 2005 the inserted & deleted tables are created in tempdb >> using the new row versioning technology that has been added to >> SQL 2005. >> >> HTH >> >> -- >> *mike hodgson* |/ database administrator/ | mallesons stephen jaques >> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907 >> *E* mailto:mike.hodgson@mallesons.nospam.com |* W* >> http://www.mallesons.com >> >> >> >> BGL wrote: >> >>>How are 'inserted' and 'deleted' implemented by SQL Server? >>> >>>When I look at the execution plan of a trigger that selects from 'inserted', >>>I can see that the user table is queried instead and aliased as 'inserted': >>> >>> >>> >>>Execution Tree >>>-------------- >>>Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted])) >>> >>>BOL referers to inserted as "temporary, memory-resident tables to test the >>>effects of certain data modifications". >>> >>>Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted' >>>is instead a view on top of MY_TABLE that accesses the new version of a >>>record, and deleted accesses the old version? >>> >>> >>> >>> >>> >>> >>> Did you see Kim's whitepaper on Snapshot Isolation? That might have much of the same info, and if you had read that first, you might not have to keep rewinding the podcast!
We were planning on doing my course in Sydney, but not enough people signed up. So we're doing the second week in Canberra. Hope you can make it! "Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message news:%23mp%23IsAaFHA.720@TK2MSFTNGP15.phx.gbl... Thanks for the clarification (I was close - it was a long day for my brain on Wednesday ;-) ).<OffTopic> Wow - posts from both Kalen & Itzik in the same thread. I've just given myself a big headache by listening to a Podcast on .NET Rocks (that went for about 100min - wmplayer sucked up 980MB of virtual RAM!) of Kim Tripp talking about snapshot & read committed snapshot isolations levels (among other topics) in SQL 2005. It took me about 3 hours because I kept having to go back and replay bits over and over again to understand what she was talking about. And that was immediately after listening to a 1 hour Podcast of Kalen talking about MSSQL history. (Kalen when are you going to do a course or seminar in Sydney (much better city than Melbourne)?) I downloaded another of Kim's Podcasts (about the same size) for the train trip home but I'm scared to start it for fear of exasperating my headache. :-\ </OffTopic> -- mike hodgson | database administrator | mallesons stephen jaques T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907 E mailto:mike.hodgson@mallesons.nospam.com | W http://www.mallesons.com Itzik Ben-Gan wrote: Hi Mike, I actually like the term views rather than virtual tables. What I said was that inserted and deleted are views on top of the section in the log that contains the change that fired the trigger. And since these log records are also buffered, chances are that if the section in the log is not really big, it's in cache. The reason it's important to realize this is that referring to inserted and deleted means reading from the log, postponing its other activities. Cheers, -- BG, SQL Server MVP www.SolidQualityLearning.com "Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message news:eghs3%239ZFHA.796@TK2MSFTNGP09.phx.gbl... I have been told recently, by a reliable source (Itzik Ben-Gan), that in SQL 2000 the insert & deleted virtual tables accessible in triggers are implemented in memory from the transaction log of the DB (ie. it builds a table in RAM from reading the tlog of the database).At a guess I'd say that the execution plan tells you that MY_TABLE is being accessed because there's no better way to say, in an execution plan, that it's actually the transaction log that's being accessed. I guess you could interpret the execution plan step as "scan the change history for the MY_TABLE object", or something along those lines. In SQL 2005 the inserted & deleted tables are created in tempdb using the new row versioning technology that has been added to SQL 2005. HTH -- mike hodgson | database administrator | mallesons stephen jaques T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907 E mailto:mike.hodgson@mallesons.nospam.com | W http://www.mallesons.com BGL wrote: How are 'inserted' and 'deleted' implemented by SQL Server? When I look at the execution plan of a trigger that selects from 'inserted', I can see that the user table is queried instead and aliased as 'inserted': Execution Tree -------------- Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted])) BOL referers to inserted as "temporary, memory-resident tables to test the effects of certain data modifications". Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted' is instead a view on top of MY_TABLE that accesses the new version of a record, and deleted accesses the old version? I actually understood it all because Itzik covered it with a good demo
in the T-SQL course in Sydney last Wednesday. I think half the problem was that I was trying to do some logic puzzles (at http://laser.narr.as) at the same time as trying to concentrate on what Kim was saying (and the fact that it was late Friday arvo). I'll be sure to check out her whitepaper though. Thanks for the tip. I'd love to come to your Canberra session but the missus & I are expecting our 3rd baby at the beginning of July so I'm afraid I'll be a tad busy but I hope your time down under will be great (I'm sure your SQL sessions will be fantastic). -- Show quoteHide quote*mike hodgson* |/ database administrator/ | mallesons stephen jaques *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907 *E* mailto:mike.hodgson@mallesons.nospam.com |* W* http://www.mallesons.com Kalen Delaney wrote: > Did you see Kim's whitepaper on Snapshot Isolation? That might have > much of the same info, and if you had read that first, you might not > have to keep rewinding the podcast! > > We were planning on doing my course in Sydney, but not enough people > signed up. So we're doing the second week in Canberra. Hope you can > make it! > > -- > HTH > ---------------- > Kalen Delaney > SQL Server MVP > www.SolidQualityLearning.com <http://www.SolidQualityLearning.com> > > > > "Mike Hodgson" <mike.hodgson@mallesons.nospam.com > <mailto:mike.hodgson@mallesons.nospam.com>> wrote in message > news:%23mp%23IsAaFHA.720@TK2MSFTNGP15.phx.gbl... > Thanks for the clarification (I was close - it was a long day for > my brain on Wednesday ;-) ). > > <OffTopic> > > Wow - posts from both Kalen & Itzik in the same thread. I've just > given myself a big headache by listening to a Podcast on .NET > Rocks (that went for about 100min - wmplayer sucked up 980MB of > virtual RAM!) of Kim Tripp talking about snapshot & read committed > snapshot isolations levels (among other topics) in SQL 2005. It > took me about 3 hours because I kept having to go back and replay > bits over and over again to understand what she was talking > about. And that was immediately after listening to a 1 hour > Podcast of Kalen talking about MSSQL history. (Kalen when are you > going to do a course or seminar in Sydney (much better city than > Melbourne)?) > > I downloaded another of Kim's Podcasts (about the same size) for > the train trip home but I'm scared to start it for fear of > exasperating my headache. :-\ > > </OffTopic> > > -- > *mike hodgson* |/ database administrator/ | mallesons stephen jaques > *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907 > *E* mailto:mike.hodgson@mallesons.nospam.com |* W* > http://www.mallesons.com > > > > Itzik Ben-Gan wrote: > >> Hi Mike, >> >> I actually like the term views rather than virtual tables. What I >> said was that inserted and deleted are views on top of the >> section in the log that contains the change that fired the trigger. >> And since these log records are also buffered, chances are that >> if the section in the log is not really big, it's in cache. >> The reason it's important to realize this is that referring to >> inserted and deleted means reading from the log, postponing its >> other activities. >> >> Cheers, >> -- >> BG, SQL Server MVP >> www.SolidQualityLearning.com <http://www.SolidQualityLearning.com> >> >> >> >> "Mike Hodgson" <mike.hodgson@mallesons.nospam.com >> <mailto:mike.hodgson@mallesons.nospam.com>> wrote in message >> news:eghs3%239ZFHA.796@TK2MSFTNGP09.phx.gbl... >> I have been told recently, by a reliable source (Itzik >> Ben-Gan), that in SQL 2000 the insert & deleted virtual >> tables accessible in triggers are implemented in memory from >> the transaction log of the DB (ie. it builds a table in RAM >> from reading the tlog of the database). >> >> At a guess I'd say that the execution plan tells you that >> MY_TABLE is being accessed because there's no better way to >> say, in an execution plan, that it's actually the transaction >> log that's being accessed. I guess you could interpret the >> execution plan step as "scan the change history for the >> MY_TABLE object", or something along those lines. >> >> In SQL 2005 the inserted & deleted tables are created in >> tempdb using the new row versioning technology that has been >> added to SQL 2005. >> >> HTH >> >> -- >> *mike hodgson* |/ database administrator/ | mallesons stephen >> jaques >> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) >> 675 907 >> *E* mailto:mike.hodgson@mallesons.nospam.com |* W* >> http://www.mallesons.com >> >> >> >> BGL wrote: >> >>>How are 'inserted' and 'deleted' implemented by SQL Server? >>> >>>When I look at the execution plan of a trigger that selects from 'inserted', >>>I can see that the user table is queried instead and aliased as 'inserted': >>> >>> >>> >>>Execution Tree >>>-------------- >>>Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted])) >>> >>>BOL referers to inserted as "temporary, memory-resident tables to test the >>>effects of certain data modifications". >>> >>>Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted' >>>is instead a view on top of MY_TABLE that accesses the new version of a >>>record, and deleted accesses the old version? >>> >>> >>> >>> >>> >>> >>> re: > In SQL 2005 the inserted & deleted tables are created in tempdb using
the new row versioning technology that has been added to SQL 2005 The overhead of the writes to #temp should slow down trigger performance vs the current implementation of viewing the log records in memory? Thanks for the posts, all, I've enjoyed the feedback. -- BGL Show quoteHide quote "Itzik Ben-Gan" wrote: > Hi Mike, > > I actually like the term views rather than virtual tables. What I said was that inserted and deleted are views on top of the section in the log that contains the change that fired the trigger. > And since these log records are also buffered, chances are that if the section in the log is not really big, it's in cache. > The reason it's important to realize this is that referring to inserted and deleted means reading from the log, postponing its other activities. > > Cheers, > -- > BG, SQL Server MVP > www.SolidQualityLearning.com > > > "Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message news:eghs3%239ZFHA.796@TK2MSFTNGP09.phx.gbl... > I have been told recently, by a reliable source (Itzik Ben-Gan), that in SQL 2000 the insert & deleted virtual tables accessible in triggers are implemented in memory from the transaction log of the DB (ie. it builds a table in RAM from reading the tlog of the database). > > At a guess I'd say that the execution plan tells you that MY_TABLE is being accessed because there's no better way to say, in an execution plan, that it's actually the transaction log that's being accessed. I guess you could interpret the execution plan step as "scan the change history for the MY_TABLE object", or something along those lines. > > In SQL 2005 the inserted & deleted tables are created in tempdb using the new row versioning technology that has been added to SQL 2005. > > HTH > > -- > mike hodgson | database administrator | mallesons stephen jaques > T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907 > E mailto:mike.hodgson@mallesons.nospam.com | W http://www.mallesons.com > > > > BGL wrote: > How are 'inserted' and 'deleted' implemented by SQL Server? > > When I look at the execution plan of a trigger that selects from 'inserted', > I can see that the user table is queried instead and aliased as 'inserted': > > Execution Tree > -------------- > Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted])) > > BOL referers to inserted as "temporary, memory-resident tables to test the > effects of certain data modifications". > > Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted' > is instead a view on top of MY_TABLE that accesses the new version of a > record, and deleted accesses the old version? > > > > > > You might also find that if you need constant, repeated access to those tables from within your trigger, consider a table variable or a temp table. With the aforementioned, you get primary keys, constraints, and indexes at your disposal. Repeated access of INSERTED & DELETED means repeated access to the LOG file (as Mike said).
hth Eric "Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message news:eghs3%239ZFHA.796@TK2MSFTNGP09.phx.gbl... I have been told recently, by a reliable source (Itzik Ben-Gan), that in SQL 2000 the insert & deleted virtual tables accessible in triggers are implemented in memory from the transaction log of the DB (ie. it builds a table in RAM from reading the tlog of the database).At a guess I'd say that the execution plan tells you that MY_TABLE is being accessed because there's no better way to say, in an execution plan, that it's actually the transaction log that's being accessed. I guess you could interpret the execution plan step as "scan the change history for the MY_TABLE object", or something along those lines. In SQL 2005 the inserted & deleted tables are created in tempdb using the new row versioning technology that has been added to SQL 2005. HTH -- mike hodgson | database administrator | mallesons stephen jaques T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907 E mailto:mike.hodgson@mallesons.nospam.com | W http://www.mallesons.com BGL wrote: How are 'inserted' and 'deleted' implemented by SQL Server? When I look at the execution plan of a trigger that selects from 'inserted', I can see that the user table is queried instead and aliased as 'inserted': Execution Tree -------------- Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted])) BOL referers to inserted as "temporary, memory-resident tables to test the effects of certain data modifications". Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted' is instead a view on top of MY_TABLE that accesses the new version of a record, and deleted accesses the old version?  
Other interesting topics
|
|||||||||||||||||||||||
