Earlier this week, we discussed the following ACID properties that are geared towards ensuring the integrity of the data residing within the database.
- Atomicity
- Consistency
- Isolation
- Durability
Every user database within Microsoft SQL Server follows these ACID properties. However, there is one special case – one database which does not support one of the ACID properties. Therefore, I had asked 3 questions – one main, and the rest were bonus questions as mentioned below:
- Which SQL Server database does not follow one of the ACID properties?
- Name the ACID property which is not followed in the answer to question #1
- Explain, in your own words, the reason why you believe the database does not follow the said ACID property
Today, I will attempt to answer these questions myself.
Which SQL Server database does not follow one of the ACID properties? Why?
In my humble opinion, this has to be the tempdb database. The tempdb database does not follow the ACID property of Durability. Here’s why:
Whenever Microsoft SQL Server is restarted, the tempdb is recreated. Any data that was committed to the tempdb is lot and replaced by a fresh copy from the model database. To test this out on your own, or to read about my experiments with the re-creation of the tempdb, you can refer my previous posts referenced below:
- TempDB – Is it a copy of the Model database?
- TempDB v/s Model database – Minimum size considerations – CREATE DATABASE - Error Msg 1803
Who came up with the correct answers?
First of all, I would like to thank everyone who responded. But, this is a competition, and hence, below are the people whose responses were correct:
Unfortunately, I will not be considering the answer of the user "sk2000" because the original answer was "NoSQL", which generally do not have the concept of ACID properties.
And now, for the winners…
The following are the winners, randomly selected from those who participated in the quiz.
Expect my DM on your twitter accounts this week-end (or earlier) with more information on your prize – a Pluralsight training code!
Check out: http://www.pluralsight-training.net/microsoft for a detailed list of the courses available. Did you know that Pluralsight has courses spanning almost every Microsoft technology currently available in the Market? If you are a blogger, do write about how you used the Pluralsight trainings – which trainings you took, and what is your feedback.
For those who did not win…
Do not despair if your name did not appear in the list above. Another quiz is coming up on Monday, August 13, 2012. Be sure to participate in that one too!
Until we meet next time,
Be courteous. Drive responsibly.