Serializable Isolation in SQL Server

    ·     Statements cannot read data that has been modified but not yet committed by other transactions.
·     No other transactions can modify data that has been read by the current transaction until the current transaction completes.
·      Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

 This isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads. It does this by placing range locks on the queried data. This causes any other transactions trying to modify or insert data touched on by this transaction to wait until it has finished.

Example:

--Query1
SET TRANSACTION ISOLATION
LEVEL SERIALIZABLE    
BEGIN TRAN  
SELECT * FROM Employee  
WAITFOR DELAY '00:00:10'  
SELECT * FROM Employee  
ROLLBACK
--Query2 
INSERT INTO Employee(EmployeeName,Country,PrimaryLanguage,CompanyPlant,ManagerId)
  
VALUES ('Anushka', 'England','English','London',5)  

Result:

Execute query1 and query 2 query 1 both Select statement reterns same data ,reason Serialaizable transaction wont allow to insert new records until the current transaction completes.

Difference between UNION and UNION ALL:

  1. UNION returns only distinct records removes duplicate records.

  2. UNION ALL returns all records including duplicate records.

  3. UNION has to perform distinct sort to remove duplicates, which makes it less fast than UNION ALL.

UNION (Merge Join) cost 46%, Union All

Union All:

Note:

·        While using UNION and UNION ALL operators, the number of columns must be same, and data columns have to be same and they have to in same order.

·        ORDER BY clause should be used in last SELECT statement in the UNION query.

·        Both statements should have some order, and equal number of columns.

INTERSECT Clause:

INTERSECT Clause the SQL INTERSECT clause/operator is used to combine two SELECT statements, INTERSECT returns only common rows returned by the two SELECT statements.

Syntax:

SELECT column1 [, column2 ] ..

FROM table1 [, table2 ]

[WHERE condition]

INTERSECT

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

Example:

SELECT * FROM [UserData]

INTERSECT

SELECT * FROM [Employee]

EXCEPT Clause:

EXCEPT Clause the SQL EXCEPT clause/operator is used to combine two SELECT statements EXCEPT returns only rows, which are not available in second SELECT statement.

Syntax:

SELECT column1 [, column2 ] ..

FROM table1 [, table2 ]

[WHERE condition]

EXCEPT

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

Example:

SELECT * FROM [UserData]

Except

SELECT * FROM [Employee]

SNAPSHOT Isolation in SQL Server

Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data.

 During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back. The SNAPSHOT transaction is blocked until that transaction has been rolled back. The lock is released immediately after it has been granted.

 In SNAPSHOT row versioning is used so when data is changed the old version is kept in tempdb so existing transactions will see the version without the change. When all transactions that started before the changes are complete the previous row version is removed from tempdb. This means that even if another transaction has made changes you will always get the same results as you did the first time in that transaction.

So the plus side you’re not blocking anyone else from modifying the data whilst you run your transaction but…. You’re using extra resources on the SQL Server to hold multiple versions of your changes.

To use the snapshot isolation level you need to enable it on the database by running the following command

Syntax:

ALTER DATABASE DatabseName

SET ALLOW_SNAPSHOT_ISOLATION ON

Example:

ALTER DATABASE LocalTest 

SET ALLOW_SNAPSHOT_ISOLATION ON

--Query1  

BEGIN TRAN 

SELECT * FROM Employee 

WAITFOR DELAY '00:00:10' 

SELECT * FROM Employee 

ROLLBACK

--Query2

INSERT INTO Employee(EmployeeName,Country,PrimaryLanguage,CompanyPlant,ManagerId)

VALUES ('Anushka', 'England','English','London',5) 

Result: