Magic Tables in SQL Server

Magic Tables in SQL Server

Magic Table in SQL Server:
Magic tables are used to put all the deleted and updated rows. We can retrieve the
column values from the deleted rows using the keyword "deleted"

To project the deleted data into the deleted table we will use "output" clause
Below is the sample code to retrieve the deleted data.

Code Snippet

DECLARE @DeletedTable TABLE(DeletedTableID INT, DeletedData VARCHAR(20)) DELETE VENKATOutput OUTPUT Deleted.KeyID, Deleted.Name INTO @DeletedTable WHERE KeyID > 3 SELECT * FROM @DeletedTable

Similarly, we can retrieve the updated data and old data too using the keyword "Inserted"

Deadlocks in SQL Server

Deadlock occurs if multiple users tried to access the resources holding some resources with them to finish the task. Dead lock can be avoided by using Lock methods in sql server.Locks can be classified in various types like

1. Rowlevel locking,
2. Page level locking,
3. Table level locking.

Row level locking: A specific row in the table will be locked by the user and meantime no one can access those rows.
Page level locking: In this case, we used to lock a particular page which hold 8 KB of data.
Table level lock: Here, entire table will be locked.

Conceptually there are various types of locks like,

Shared lock : The lock is shared by the users. One person can update the data meantime other persons can select the data.
Exclusive lock: No one can access the data until the concerned person releases the lock.