CODEWITHSUNDEEP
sqlsql-server-2008
Splunk
Splunk

Reputation: 499

What are the mechanics of the inserted table in SQL Server?

I have been having a discussion with my manager about the behaviour of the inserted table in Sql Server. He explained to me briefly his understanding of which I have concluded it sounds thread specific. He is a busy man and does not always have time to go into massive detail. I would like to understand more about how the inserted table(s) work.

When there are more than one inserted tables, what makes them unique, are they aliased?

Do they then get discarded at the close of each thread?

Are they even thread specific so to speak?

I appreciate this may not be an ideal question for the site, but there is a wealth of knowledge on here between you helpful lot.

Cheers

Upvotes: 3

Views: 1005

Answers (2)

HLGEM
HLGEM

Reputation: 96648

They are not just thread specific, they are specific to a particular Insert, Update or Delete action. So if, in the same stored proc, you inserted to the same table in two different steps, each one would have its own instance of inserted and deleted that was specific to only that action. If others were performing actions at the same time, they would have their own versions of the tables to pull from. But the inserted and deleted tables are batch-oriented. If you do an update of 10,000,000 records that is how many records will be in the inserted and deleted tables. If you do a cursor that inserts 1 record and loops through a 1,000,0000 times you would get one record in 1,000,0000 different versions of inserted and deleted (Which of course would be the worst way you could perform the task). It is important to understand that they contain a batch of records becasue that means ALL triggers must be written with the assumption that more than one record might be involved.

Upvotes: 5

TcKs
TcKs

Reputation: 26642

The virtual tables "inserted" and "deleted" are only once in one time and context. It can be used in triggers or OUTPUT clausule.

Both "inserted" and "deleted" tables has same schema (in the same place), but can have different count of records.

The "inserted" table contains the new records (inserted rows, updated rows with new values) and the "deleted" table contains the old records (deleted rows, updater rows with old values).

The scope of these tables is for the trigger or OUTPUT clausule. So, if you execute two/three/... commands, the "inserted"/"deleted" tables will be different.

Using the inserted and deleted Tables

Upvotes: 1

Related Questions