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.

Helping a team with an implementation has it’s own unique challenges, and the biggest challenge is – they ask questions! I get a kick out of this challenge because it gives me and the teams around me a lot to learn. Currently while helping a team to implement optimistic concurrency, somebody asked me a question:

What is the next value of ROWVERSION at any given instant of time?

The question makes absolute sense. If we can determine the active value of an identity column and determine which value would be used next, there is no reason why we cannot determine the active value of a ROWVERSION column. After all, ROWVERSION is an auto-incrementing number.

The answer comes in the form of a non-deterministic function – MIN_ACTIVE_ROWVERSION. This function returns the lowest active ROWVERSION value in the current database. A ROWVERSION value is active if it is being used in a transaction that has not yet been committed.

Explanation

The definition from Books-On-Line is a little too heavy to digest in one go. So, here’s a break-down of the sentence.

A new ROWVERSION value is typically generated when an INSERT or UPDATE is performed on a table that contains a column using the ROWVERSION data-type. A transaction may be performing multiple INSERTs and UPDATEs – causing multiple changes to the ROWVERSION value. However, the lowest of these values (effectively, the value that was first used when the transaction was started) is what is returned by the function – MIN_ACTIVE_ROWVERSION().

For all practical purposes, this is same as the value of @@DBTS + 1.

Demonstration

Here’s a demonstration of the function – MIN_ACTIVE_ROWVERSION. This demo requires two active sessions to the SQL Server instance.

Let’s begin by creating a test table, and inserting some test data into it. We will use the MIN_ACTIVE_ROWVERSION() to determine the value of the ROWVERSION column that will be used by SQL Server.

USE tempdb
GO

--Safety Check
IF OBJECT_ID('SourceData') IS NOT NULL
BEGIN
    DROP TABLE SourceData
END
GO

--Create the demo (source) table
CREATE TABLE SourceData (RowId INT IDENTITY (1,1),
                         RowVal VARCHAR(20),
                         DataVersion ROWVERSION
                        )
GO

--Fetch the currently active value of ROWVERSION
SELECT MIN_ACTIVE_ROWVERSION() AS MinActiveRowVersion

--Now, insert some test data
INSERT INTO SourceData (RowVal) VALUES ('Windows'),
                                       ('SQL Server'),
                                       ('BizTalk Server'),
                                       ('Exchange Server')
GO

SELECT RowId, RowVal, DataVersion
FROM SourceData

--Fetch the currently active value of ROWVERSION
SELECT MIN_ACTIVE_ROWVERSION() AS MinActiveRowVersion

Now, switch to another session connected to the same SQL Server instance, and execute the following query:

-- In a separate window, begin a transaction, insert some test data, but do not commit
-- Session 02
USE tempdb
GO
BEGIN TRANSACTION MinRowVersion
    --Fetch the currently active value of ROWVERSION
    SELECT MIN_ACTIVE_ROWVERSION() AS MinActiveRowVersion
    
    INSERT INTO SourceData (RowVal) VALUES ('System Center'),
                                           ('Office')
    
    SELECT RowId, RowVal, DataVersion
    FROM SourceData
    
    --Fetch the currently active value of ROWVERSION
    SELECT MIN_ACTIVE_ROWVERSION() AS MinActiveRowVersion
    
--Session 02 - We are NOT performing a COMMIT on the transaction
--COMMIT TRANSACTION MinRowVersion

Although the transaction inserts two (2) records, the MIN_ACTIVE_ROWVERSION() returns the same value as seen at the end of the statements executed in session #01. This is because the transaction in session #02 is not yet committed.

Committing the transaction in session #02 should now reflect the updated value of ROWVERSION column value.

--Session 02
COMMIT TRANSACTION MinRowVersion

--Return back to Session 01
--Session 01
--Fetch the currently active value of ROWVERSION
SELECT MIN_ACTIVE_ROWVERSION() AS MinActiveRowVersion

--Cleanup
IF OBJECT_ID('SourceData') IS NOT NULL
BEGIN
    DROP TABLE SourceData
END
GO

References:

• MIN_ACTIVE_ROWVERSION(): http://technet.microsoft.com/en-us/library/bb839514(v=sql.110).aspx

Until we meet next time,
Be courteous. Drive responsibly.

Recently, I was helping a team implement optimistic concurrency into their application. In my previous post (SQL Server – DDL – ROWVERSION v/s TIMESTAMP – a key difference), we reviewed a critical DDL difference with respect to table definition between the ROWVERSION and TIMESTAMP columns.

Many DBAs and developers believe that ROWVERSION is a unique value. This statement causes an interpretation that a table with a ROWVERSION column will always have unique values across the database.

The above statement is not accurate – the ROWVERSION column does get a new value during an insert, however, there is a possibility that ROWVERSION columns can hold duplicate values. This can happen during a bulk insert (SELECT…INTO).

Here’s a simple demonstration:

USE tempdb
GO

--Safety Check
IF OBJECT_ID('RowVersionDemo') IS NOT NULL
BEGIN
    DROP TABLE RowVersionDemo
END
GO

IF OBJECT_ID('SourceData') IS NOT NULL
BEGIN
    DROP TABLE SourceData
END
GO

--Create the demo (source) table
CREATE TABLE SourceData (RowId INT IDENTITY (1,1),
                         RowVal VARCHAR(20),
                         DataVersion ROWVERSION
                        )
GO

--Insert some test data
INSERT INTO SourceData (RowVal) VALUES ('Windows'),
                                       ('SQL Server'),
                                       ('BizTalk Server'),
                                       ('Exchange Server')

--Bulk Insert the source data into destination table
SELECT SourceData.RowId, SourceData.RowVal, SourceData.DataVersion
INTO RowVersionDemo FROM SourceData

--Select the data inserted (for demo purposes)
SELECT RowId, RowVal, DataVersion
FROM SourceData

SELECT RowId, RowVal, DataVersion
FROM RowVersionDemo
GO

--Check the data-types of the columns created as part of the BULK-INSERT:
SELECT isc.TABLE_NAME,
       isc.COLUMN_NAME,
       isc.DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS AS isc
WHERE isc.TABLE_NAME = 'RowVersionDemo'
GO

--Cleanup
IF OBJECT_ID('RowVersionDemo') IS NOT NULL
BEGIN
    DROP TABLE RowVersionDemo
END
GO

IF OBJECT_ID('SourceData') IS NOT NULL
BEGIN
    DROP TABLE SourceData
END
GO

We can see here that SQL Server inserted the column data “as-is” into the destination tables. A look at the data-types of the destination table shows us that it has been created as a “timestamp” column, which is a documented bug within Microsoft SQL Server.

In short, using ROWVERSION does not guarantee uniqueness – one can have duplicate values amongst the ROWVERSION columns within the same database.

References:

• Connect Cases related to ROWVERSION being displayed as “TIMESTAMP”
  • https://connect.microsoft.com/SQLServer/feedback/details/274333/deprecate-timestamp-the-keyword-not-rowversion-itself
  • https://connect.microsoft.com/SQLServer/feedback/details/530944/sql-2008-still-shows-timestamp-not-rowversion
• Optimistic Concurrency using ROWVERSION: http://beyondrelational.com/modules/2/blogs/77/posts/11268/optimistic-concurrency-a-conceptual-introduction.aspx

Until we meet next time,
Be courteous. Drive responsibly.

A couple of years ago when I started blogging, I wrote about an approach for implementing optimistic concurrency in an application (Link). The approach revolves around the storage, retrieval and comparison of the ROWVERSION value – which is an improvement upon the TIMESTAMP data-type, which has been marked as deprecated.

There are a handful of differences between the ROW VERSION and TIMESTAMPS columns. Yet, most accidental DBAs and novice database developers make the mistake of thinking that they are one and the same. While I had mentioned all the differences in my post on optimistic concurrency, here’s a recap:

1. The ROWVERSION data type is just an incrementing number and does not preserve a date or a time
2. A table can have only one ROWVERSION column
3. ROWVERSION should not be used as keys, especially primary keys
4. Using a SELECT INTO statement has the potential to generate duplicate ROWVERSION values
5. Finally for those who came from SQL 2005, the TIMESTAMP is deprecated. Please avoid using this in new DDL statements
6. Unlike TIMESTAMP, the ROWVERSION column needs a column name in the DDL statements

The last difference is what throws off most people – they simply replace all instances of “TIMESTAMP” in their DDL code and expect it to work – which will not be the case.

Here’s a simple demonstration.

A typical TIMESTAMP implementation would look like this:

USE tempdb
GO

--Safety Check
IF OBJECT_ID('RowVersionDemo') IS NOT NULL
BEGIN
    DROP TABLE RowVersionDemo
END
GO

--Create a test table
CREATE TABLE RowVersionDemo (Id INT IDENTITY(1,1),
                             Data VARCHAR(20),
                             TIMESTAMP
                            )
GO

--Insert some test data
INSERT INTO RowVersionDemo (Data) VALUES ('Windows'),
                                         ('SQL Server')
GO

--Fetch Data
SELECT RowVersionDemo.Id, RowVersionDemo.Data, RowVersionDemo.TIMESTAMP
FROM RowVersionDemo

As you can see, we just placed a TIMESTAMP column in the table definition – without really specifying the column name. Simply replacing all instances of TIMESTAMP with ROWVERSION will result in an error:

--ROWVERSION - ERROR
USE tempdb
GO

--Safety Check
IF OBJECT_ID('RowVersionDemo') IS NOT NULL
BEGIN
    DROP TABLE RowVersionDemo
END
GO

--Create a test table
CREATE TABLE RowVersionDemo (Id INT IDENTITY(1,1),
                             Data VARCHAR(20),
                             ROWVERSION
                            )
GO

Msg 173, Level 15, State 1, Line 5

The definition for column ‘ROWVERSION’ must include a data type.

ROWVERSION is a data-type and needs a column name to be associated with it. Hence, the following will work:

--ROWVERSION - FIXED
USE tempdb
GO

--Safety Check
IF OBJECT_ID('RowVersionDemo') IS NOT NULL
BEGIN
    DROP TABLE RowVersionDemo
END
GO

--Create a test table
CREATE TABLE RowVersionDemo (Id INT IDENTITY(1,1),
                             Data VARCHAR(20),
                             DataVersion ROWVERSION
                            )
GO

--Insert some test data
INSERT INTO RowVersionDemo (Data) VALUES ('Windows'),
                                         ('SQL Server')
GO

--Fetch Data
SELECT RowVersionDemo.Id, RowVersionDemo.Data, RowVersionDemo.DataVersion
FROM RowVersionDemo

The above simple demonstration proves that TIMESTAMP and ROWVERSION cannot be used in the same way when defining the database tables. Conversion from TIMESTAMP to ROWVERSION is not a direct find-replace operation.

There are more knowledge nuggets coming up related to the ROWVERSION data-type. Stay tuned to this blog for more!

Further Reading

• Implementing Optimistic Concurrency using ROWVERSION:

Until we meet next time,
Be courteous. Drive responsibly.

The SQL Server Profiler is a very powerful diagnostic tool that helps you take a look at what’s happening inside of SQL Server when you execute a query. I hope that you are finding the 10-part tutorial series on the SQL Server Profiler (http://beyondrelational.com/modules/12/tutorials/631/getting-started-with-sql-server-profiler.aspx?tab=tutorials&bs=57&ts=46) useful. The aim of the tutorial is to help you get started with the SQL Server profiler and debug any database issues within your application with ease.

However, the more you explore, the more queries and questions you come up with and attempting to answer them is perhaps the best way to learn.

One of the kind readers asked me a question the other day via the ASK Module (http://beyondrelational.com/ask/nakul/default.aspx) related to the SQL Server Profiler (http://beyondrelational.com/ask/nakul/questions/1557/sql-server-profiler-trace-query.aspx), the answer to which I will discussing in today’s post.

The Question

Assume that one of your applications is executing the following query to get a list of all salaried employees for AdventureWorks Cycles.

USE AdventureWorks2012
GO
SELECT * FROM HumanResources.Employee WHERE SalariedFlag = 1

However, when looked at from the SQL Server Profiler, you see that instead of the value “1”, you see a parameterized version of the query if the “SQL:StmtStarting” or “SQL:StmtCompleted” events are captured:

SELECT * FROM HumanResources.Employee WHERE SalariedFlag = @p1

The actual parameter value (“1”) is available when the “RPC:Completed” event is captured. The question therefore is:

Why do “SQL:StmtStarting” and “SQL:StmtCompleted” not show the parameter values? Is there a way by which they can show the parameter values, so that additional events don’t need to be traced?

The Answer

The question is very interesting, and the answer lies in the internals of how SQL Server prepares itself for multiple executions of the same query in the future. However, let’s answer the second part of the question first. The answer to the second part of the question is quite simply, no. To the best of my knowledge, there is no way that we can have both the parameterized version of the query and it’s values captured the same event – “SQL:StmtStarting” or “SQL:StmtCompleted”.

That being said, let’s look at “why” the parameterized version of the query is seen.

One of the first things that SQL Server’s database engine does is to create a query plan for execution. Preparing a query plan is an expensive operation and therefore, SQL Server caches this plan for future re-use. Subsequent executions of the query will re-use the already available query plan and not spend time in creating a new plan – the specific values used in the comparisons may differ from instance to instance, but the plan itself remains the same.

We are therefore looking at two different components to the execution of a query:

1. A generic, parameterized query plan – which is what is seen in the “SQL:StmtStarting” and “SQL:StmtCompleted” events
2. A set of instance-specific variables/parameter values – which is what is seen in the instance specific “RPC:Completed” event

The following is an easy set of queries for better understanding of the concept.

/* WARNING */
/*
This query is provided "as-is" and without warranty.
The query is provided for demonstration purposes only.

PLEASE RUN THIS ON A DEVELOPMENT ENVIRONMENT ONLY.
EXECUTING THIS ON A QA/PRODUCTION ENVIRONMENT MAY RESULT IN SERIOUS PERFORMANCE COMPLICATIONS.
*/
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

USE AdventureWorks2008R2
GO
DECLARE @sqlstring NVARCHAR(100)
DECLARE @paramDefinition NVARCHAR(50)
DECLARE @paramValue BIT

SET @sqlstring = N'SELECT * FROM HumanResources.Employee WHERE SalariedFlag = @p1'
SET @paramDefinition = N'@p1 BIT'
SET @paramValue = 1

EXEC sp_executesql @sqlstring, @paramDefinition, @p1 = @paramValue

--Check if the SQL Server is re-using the query plan
-- USECOUNTS value should be 1
SELECT usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE usecounts > 0 AND 
    	text like '%SELECT * FROM HumanResources.Employee%'
ORDER BY usecounts DESC;

/********* RESULTS ****************/
/* (Compressed for sake of brevity)
usecounts   cacheobjtype	objtype	     text
  1         Compiled Plan	Prepared     (@p1 BIT)SELECT * FROM HumanResources.Employee WHERE SalariedFlag = @p1
  1         Compiled Plan	Adhoc        DECLARE @sqlstring ...............
*/

We can see that the version SQL Server has cached is the one with the generalized parameters – not the one with the parameter values.

Let us execute the same query again – this time with a different parameter value.

--Execute the query again, but with a different value
SET @paramValue = 0
EXEC sp_executesql @sqlstring, @paramDefinition, @p1 = @paramValue

--Check if the SQL Server is re-using the query plan
-- USECOUNTS value should be 2
SELECT usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE usecounts > 0 AND 
    	text like '%SELECT * FROM HumanResources.Employee%'
ORDER BY usecounts DESC;

/********* RESULTS ****************/
/* (Compressed for sake of brevity)
usecounts   cacheobjtype	objtype	     text
  2         Compiled Plan	Prepared    (@p1 BIT)SELECT * FROM HumanResources.Employee WHERE SalariedFlag = @p1
  1         Compiled Plan	Adhoc       DECLARE @sqlstring ...............
*/
GO

The results show that the SQL Server was able to reuse the generic query plan.

Reference:

• Query Plan Re-use – http://beyondrelational.com/modules/2/blogs/77/posts/11346/query-plan-re-use-write-your-t-sql-queries-with-the-proper-case-for-plan-re-use-and-better-query-per.aspx

Until we meet next time,

Be courteous. Drive responsibly.