Database Isolation Levels in Sql Server and ADO.NET

Isolation level refers to I in the ACID properties for a Transaction. ACID stands for Atomicity, Consistency, Isolation, Durability. Isolation level refers to sensitivity of a database transaction to other changes in other transactions. You can set the isolation level for a transaction using the following command in T-SQL:

SET TRANSACTION ISOLATION LEVEL <level name>

<level name> can be one of the following:
  • READ COMMITTED
  • READ UNCOMMITTED
  • REPEATABLE READ
  • SERIALIZABLE
  • SNAPSHOT
Read Uncommited
  • No shared locks acquired
  • No exclusive locks are honored
  • Dirty Reads are possible
  • Good for Performace
Read Committed
  • Shared locks are held while data is read
  • Avoids dirty reads
  • Data can be changed after the shared lock is released but before the transaction completes. This can cause non-repeatable reads and phantom rows
  • Default Isolation level used by Sql Server
SnapShot
  • A copy of the data accessed by the transaction is maintained.
  • Reduces blocking since other transactions can still be modifying the original data.
  • Supported on SQL Server 2005 and beyond, but needs to be enabled manually.
Repeatable Read
  • Shared locks are placed on all data that is used in a query.
  • Prevents non-repeatable reads
  • Phantom rows can still occur
Serialize
  • A range lock is placed on the data used. This prevents other transactions from updating or inserting data in that range.
  • Removes phantom rows issue.
  • Extremely bad for concurrency.
Database Isolation Levels Compared:

Isolation Level Dirty Reads Repeatable Reads Phantom Rows Concurrency
Read Uncommitted Yes No Yes Best
Read Committed No No Yes Good
Snapshot No Yes No Good
Repeatable Read No Yes Yes Poor
Serializable No Yes No Very Poor

Database Normalization Basics

First Normal Form (1NF):
  • Identify related groups of data (ex: orders, customers, products) and create separate tables for each group.
  • All tables should have a Primary Key identifying an individual row in a table.
  • No two columns in a table should have the same information and each column can contain only one attribute.
In essence 1st normal form is about removing redundant data and creating tables for related groups of data and creating a primary key.

Second Normal Form (2NF):
  • The database is required to be in the 1NF form.
  • Identify data that applies to multiple rows in a table and extract it to a separate table(s)
  • Create Foreign Key relationships between the new tables and the original table.
Third Normal Form (3NF):
  • The database is required to be in the 2NF form.
  • A table should not contain columns that are not dependent on the Primary Key. For example, an Order table may contain a CustomerID column to denote the customer who placed the order, but may not contain the customers date of birth because its not related in any sense to the order.
Fourth Normal Form (4NF):
  • The database is required to be in the 3NF form.
  • A table should not contain 2 or more multi-valued facts about an entity.
For example, consider a table as shown below:
-------------------------------------
| EmployeeId  | Skill    | Language |
-------------------------------------
|  1          | Type     | German   |
-------------------------------------
|  1          | Cook     | Greek    |
-------------------------------------
|  1          |          | Spanish  |
-------------------------------------

This table violates the 4NF form since it has 2 multi-valued facts (Skill and Language) in the same table. Instead this table should be split into two tables that individually satisfy the 4NF.
-----------------------          -----------------------------
| EmployeeId | Skill  |          | EmployeeId   | Langugage  |
-----------------------          -----------------------------
|  1         | Type   |          |    1         | German     |
-----------------------          -----------------------------
|  1         | Cook   |          |    1         | Greek      |
----------------------           -----------------------------
                                 |    1         | Spanish    |
                                 -----------------------------
References: http://www.bkent.net/Doc/simple5.htm

SQL Server Index Fragmentation and Resolution

What is Index Fragmentation and how do you resolve it in SQL Server?
Answer: I came across and excellent article on index fragmentation and 4 ways to resolve it on Sql-Server-Performace.com which describes it in great detail.

Find an Item in a Sorted Array with Shifted Elements

Problem: You are given a sorted array with shifted elements. Elements can be shifted to the left or right by 'i' number of places. The sign of 'i' denotes the direction of the shift. For positive 'i' direction of shift is right and left for negative 'i'.

For example, consider the sorted array 2, 3, 4, 8, 10, 11. A shift of 3 places to the right would be denoted by i=2 and the shifted array would look like this: 10, 11, 2, 3, 4, 8,

For i=-2, the shifted array would look like: 4, 8, 10, 11, 2, 3.



Write code to find if a given number is present in this array.

Solution: The brute force method to search all elements in the array would yield an O(n) solution, so obviously that's not the best approach. We are not leveraging the sorted nature of the array in this case.

Now, how can we leverage the sorted nature of the array? Let assume that 'i' was 0. In that case the array would be sorted and not shifted at all (0 shift). Whats the fastest way to search in a sorted array? Binary Search! We can split the array in 2 halves and do a recursive search in one of the halves until we find the number we are looking for ( or not, if its not in the array ). This approach has a running time of O(log n), which is obviously better than n.

But, the fact that the array is shifted by 'i' number of elements complicates things a little bit. Now, instead of splitting the array in equal halves, we split the array at the shift index and do a recursive binary search. There are issues we need to tackle when the shift is greater than the length of the array or if the shift is negative. I guess the code below will make much more sense than my description of the solution.

Code: We will assume that we are provided with a method below that does binary search for us and won't bother implementing it here.
// myArray is the input array
// startIndex and endIndex are the indexes in the 
// array where the binary search starts and ends
// The method returns the index of the searchVal 
// if found in the array, else it returns -1

int BinarySearch(int[] myArray, int startIndex, int endIndex, int searchVal);


// this method will return the index of the searchVal 
// if found, else it return -1
int SearchElement(int[] myArray, int shift, int searchVal)
{
   // to take care of scenarios where the shift is more 
   // than the length of the array
   shift = shift % myArray.Length; 
   
   // -ve shift can be seen as positive shift equal to 
   // the length of the array - ( -ve shift) 
   if (shift < 0)
       shift = myArray.Length + shift;

   if(myArray[shift] <= searchVal &&  
      myArray[myArray.Length - 1] >= searchVal)
   {
      return BinarySearch(myArray, shift, myArray.Length - 1, searchVal);
   }
   else if(myArray[0] <= searchVal && 
           myArray[shift - 1] >= searchVal)
   {
      return BinarySearch(myArray, 0, shift-1, searchVal);
   }
   return -1;
}

Database SQL Interview Questions

  1. Write a SQL Query to find first day of month?
  2. Why there is a performance difference between two similar queries that uses UNION and UNION ALL?
  3. How to choose between a Clustered Index and a Non-Clustered Index?
  4. How you can minimize deadlock situations in a database server?
  5. When you should use low fill factor?
  6. Explain First, Second, and Third database normalization form with examples?
  7. What are the benefits of using stored procedures?
  8. What is the difference between an explicit and an implicit lock
  9. Discuss about lock granularity
  10. Discuss the ACID properties for a transaction
  11. How do TRUNCATE and DELETE work?
  12. Contrast UNIQUE and PRIMARY KEY constraints
  13. Do you know what log shipping is?
  14. Talk about different kinds of joins and how does each work?
  15. What does a NOLOCK hint do?
  16. What is Index Fragmentation and how do you resolve it in SQL Server?
  17. What is lock escalation in SQL Server and how does it affect blocking?
  18. How can you resolve blocking problems caused by lock escalation in SQL Server? http://support.microsoft.com/kb/323630
  19. What are the pros and cons of creating an index on a table?
  20. What is the max length of an index key?