Magic Tables: Magic tables are the virtual tables provided by SQL Server to store the recently inserted, deleted and updated values. Basically there are two types of magic tables in SQL Server namely ‘INSERTED‘ and ‘DELETED‘.
These are much useful while working with Triggers. We can’t query or see these tables are created when we do DML operation on the data and will be deleted once the DML operations completes.
Inserted: When we do Insert operation, this table is created automatically and holds the recently inserted data temporarily which can be accessed by triggers.
Deleted: When we do Delete operation, this table is created automatically and holds the recently deleted data temporarily which can be accessed by triggers.
In SQL Server, we do not have a magic table called UPDATED to hold the recently updated data. But there is way to access updated data by using INSERTED and DELETED. In SQL Server, if we update data by default all the old values will be stored in DELETED and all the new values will be stored in INSERTED magic table.
Note: Some interviewers might try to confuse you by asking how many magic tables in SQL Server. This is very common question interviews and don’t get confused when you face this question. Just answer confidently as only ‘2’. Still they try to confuse you by asking what about update operation if we have only 2 one for insert and other for delete. Just answer the above confidently. That’s it… You will be in.