Inserted and Deleted are One Table?

Posted on by John H5 Comments on Inserted and Deleted are One Table?

Inserted and deleted tables are 2 pseudo tables preseting the before and after images of modified rows. They can only be accessed from within triggers. Those two have been around for quite long time but it’s hard to find any articles to tell how they are populated. Some rumors said they are retrieved from transaction logs in SQL Server 2000. Recently, I was asked to evaluate the performance impact of using trigger for my customer’s project. I did some investigation and got some interesting results.

Continue reading “Inserted and Deleted are One Table?” →

Truncate

Posted on by John H1 Comment on Truncate

What are the differences between TRUNCATE and DELETE? You may immediately point out that:

  1. Truncates reset the identity value
  2. Truncates can’t have where clause
  3. Truncates can’t be run on a table which is referenced by other tables
  4. Truncates do not fire triggers.
  5. Truncates can’t be run on a table which has depending objects such as indexed views.
  6. Truncates can’t be run on a table which has published by merge or transactional replication publication
  7. Truncates need db_owner and db_ddladmin permission.
  8. Truncates need locks on the table and schema but do not need locks on rows of the tables

Continue reading “Truncate” →