Any RDBMS system follows a certain set of general principles that are geared towards ensuring the integrity of the data residing within the database. These fundamental principles are collectively known as ACID properties, which every database within an RDBMS system should confirm to.

• Atomicity:
  • At a transaction level, this is the all-or-nothing approach, i.e. either all changes are committed or none – one cannot have a partially committed transaction
  • Example: A parallel can be established with the banking industry – when transferring funds from one bank account to another, it is imperative that the amount of money deducted from the source account are credited to the destination account. It is not acceptable to have a deduction from one account, but no credit to another account (that would be a scam!). If something goes wrong during the transaction, the rollback needs to be triggered in both systems – source and destination
  • Similarly, all RDBMS systems need to ensure that if something goes wrong within any transaction, an “undo” or “rollback” happens for the non-committed transaction
• Consistency:
  • Whenever any transaction is “committed”, the effect of the transaction should leave the database in a logically consistent state
  • Example: When adding sales data into a database, it would be logically inconsistent to enter a sales order without having a corresponding customer record. Through the use of foreign keys, the RDBMS ensures that one cannot leave the database in a logically inconsistent state when committing a transaction
• Isolation
  • Each transaction should be isolated (or protected) from the effects of other concurrently running transactions
  • This means that although multiple transactions are running in parallel, the net effect of the execution of the execution should be identical to that of executing all transactions in series (i.e. one after another in some serial order)
  • Example: Taking our banking example further, let’s assume that John wants to transfer $100 to Jack’s account. Jack wants to transfer $75 to Steve’s account. Both are separate transactions, but the net effect of these transactions is that Jack will end up with $25 more than his base balance ($x). Here’s how: [($x + $100) – ($75)] = $x + $25. If this is not the end result, the system is no good
• Durability
  • Durability simply means that once a transaction has been completed, it’s effect must be persisted within the database irrespective of the state of the system (i.e. even in case of a system crash, the data must be permanently committed to disk upon recovery)
  • Example: Continuing our example of the banking system, if the system goes down in the middle of the banking transaction, Atomicity would ensure that uncommitted transactions are rolled back. However, if the system goes down after completion of the transaction, then changes made to all accounts involved must remain as-is when the system comes back online

The Question – Which SQL Server database does not follow one of the ACID properties?

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. The question today is:

Which SQL Server database does not follow one of the ACID properties?

Bonus Questions!!!

Here are two (2) bonus questions which you can answer – the bonus questions also contribute to the prize-winning questions!

1. Based on the answer to the basic question, name the ACID property which the database does not follow
2. Explain, in your own words, the reason why you believe the database does not follow the said ACID property

Rules

The rules are very simple:

1. You can answer these questions till Wednesday 22:00hrs (IST)/Wednesday 12:30hrs (US-EDT) – August 08, 2012
2. Answers will be shared and winners declared in my post on Thursday, August 09, 2012
3. You can answer multiple times – however, at the end of the day, only one entry would be considered
4. Follow the following on Twitter!
   • Pluralsight (Handle is: @pluralsight)
   • BeyondRelational.com (Handle: @beyondrel)
   • Me! (Handle is: @nakulv_sql )
5. Winners will be chosen randomly

Remember: Participation is always more important! Make sure you reply to the quiz even if it's just a guess! 

Until we meet next time,

Be courteous. Drive responsibly.