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.

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?