The magic table in sql server

 Magic tables in SQL Server are special types of tables that are automatically created and managed by SQL Server during data modification operations such as INSERT, UPDATE, DELETE, and MERGE. These are not physical tables but temporary logical tables.

There are two types of magic tables in SQL Server:

1. INSERTED: This table holds the recently inserted or updated data. During an INSERT operation, this table holds the data being inserted. For an UPDATE operation, it holds the new values.
2. DELETED: This table holds the recently deleted or updated data. During a DELETE operation, this table holds the data being deleted. For an UPDATE operation, it holds the old values.

These magic tables can’t be retrieved directly. They are typically used within triggers to access the old and new values of the modified rows. This allows developers to efficiently manipulate data without writing additional code.

Here’s an example of how magic tables work:

CREATE TRIGGER trgAfterInsert ON Employees
AFTER INSERT
AS
BEGIN
   SELECT * FROM INSERTED
END

In this example, after an INSERT operation on the Employees table, the trigger trgAfterInsert fires and selects all rows from the INSERTED magic table, which holds the newly inserted rows.

Magic tables are stored in the tempdb. So, whenever you use magic tables in SQL Server with a query statement, tempdb will come into the picture.

Please note that there are some limitations to magic tables:

• Users cannot create any index or apply any constraint on the magic tables in SQL Server.
• They cannot be altered because the purpose of the magic tables is to audit the information in the system.