ORDER BY clause with CASE Expressions(CASE STATEMENT): 

I was googling some code examples on how to use “ORDER BY clause with CASE Expressions (CASE STATEMENT)”, with some googling information I tried following t-sql code and that worked for me.

This experience I want to share in this post with example and code sample.

Searched CASE expression:

The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Example:

In this example searched CASE expression used to decide order, for male all records are in descending order and for female ascending order.

Code sample:

SELECT * FROM dbo.Employee
ORDER BY 
 CASE WHEN Gender='Male' THEN EmployeeName END Desc,
 CASE WHEN Gender='Female' THEN Country END ASC

Repeatable read Isolation in SQL Server

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

Example:

 
--Query1
 
SET TRANSACTION ISOLATION
LEVEL REPEATABLE
READ  
BEGIN TRAN  
SELECT * FROM Employee  
WAITFOR DELAY '00:00:05'  
SELECT * FROM Employee  
ROLLBACK
 
--Query2
Update Employee set UpdatedDate=getdate()

Result:

Run query 1 and then query 2, from query 1 notice that  both select statement returns same data even though you ran a query 2 to modify the data before the second select run. This is because the Update query was forced to wait for Query1 to finish due to the exclusive locks that were opened as you specified Repeatable Read.

After Transaction completes and update query executed, Select statement returns modified record:

If you rerun the above Queries but change Query1 to Read Committed you will notice the two selects return different data and that Query2 does not wait for Query1 to finish.

For above same use case Read returns:

One last thing to know about Repeatable Read is that the data can change between 2 queries if more records are added. Repeatable Read guarantees records queried by a previous select will not be changed or deleted; it does not stop new records being inserted so it is still very possible to get Phantom Reads at this isolation level.

Phantom read with repeatable read:

--Query1
SET TRANSACTION ISOLATION
LEVEL REPEATABLE
READ    
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)