Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to the next level. Here is the article which discusses Author’s Perspective.
Beyond Relational has come up with a very interesting concept – they have converted a few of the questions from my book into the SQL Quiz. The quiz is indeed focused on my two goals. In addition, it’s going to put the learning of the book to a higher stage. Looking at this novel concept, Vinod Kumar (Co-author) and I have decided to help every participant and reader by giving a few hints and suggestions to solve the quiz.
ANSI ISOLATIONS levels are very important parts of any database. There are two database servers both have different isolations levels. Because of this, different results are returned to the users. Now, we want to help users by educating them with regards to the various isolations levels and their respective behaviors. Examples will be given accordingly.
What are the different ANSI ISOLATION Levels? What is the additional ISOLATION level with SQL Server and how can we explain its behavior?
It’s interesting to note that the ISOLATION Levels are lesser used and are often confusing. What is the fundamental difference of using Serializable Vs Repeatable Read Isolations especially when it comes to locking behavior? ISOLATION LEVELS Documentation
There are various effects of the ISOLATION Levels for the database. The result of the query follows the current isolation level of the database and session well enough. Isolation of the query can be changed at the session level. During the lifetime of their careers, quite a few DBAs or developers get some chances to change the isolation level; however, the understanding of the isolation level is a must not only for developers but also for all database professionals. Changing the isolation without a proper understanding of it can lead to dirty read or excessive locking. An appropriate balance between business requirements and database design is truly a very important aspect for optimal performance.
SQL Server Interview Questions and Answers ISBN: 1466405643 Page#20-21
Concurrency Problems and their Relationship with Isolation Level
Check the Isolation Level with DBCC useroptions
Applying NOLOCK Hint at Query Level – NOLOCK for whole Transaction
Simple Example of Snapshot Isolation – Reduce the Blocking Transactions
Note: SQL Quiz, winners and prizes are administrated by Beyond Relational. The goal of this blog post is to provide additional learning pointers only.
Reference: Pinal Dave (http://blog.sqlauthority.com)