Today’s post was prompted by a recent code review experience. I was called upon to code review a data cleanup script. Coming directly to the interesting part, I noticed that the developer had put the WITH (NOLOCK) table hint when consuming the tables in an UPDATE statement. The resulting conversation is transcribed below:

Me: We will need to remove the WITH (NOLOCK) table hint from the UPDATE statements in this script.

Developer: Why? Isn’t the WITH (NOLOCK) supposed to acquire no locks on the database?

Me: That’s not true. For the SQL Server to create a query plan and execute it, SQL Server will have to request at least a Schema stability (Sch-S) lock. You can read about this from Pinal Dave, a.k.a. “SQLAuthority” (B|T)’s post on “What Kind of Lock WITH (NOLOCK) Hint Takes on Object?”

Developer: Okay, I get it now. So, we can use to reduce the locking in our database with the WITH (NOLOCK) statement. Wouldn’t we want to reduce locking as much as possible always?

Me: Yes, we may want to keep locks to a minimum, but that’s only during SELECT statements. This is because systems are typically 80% read, 20% write systems. An update would require an exclusive lock, and hence the WITH (NOLOCK) would be ignored by SQL Server. Let me show you an example:

Below are two UPDATE queries – one which uses a NOLOCK statement, and the other which does not. To allow us to track the nature of the locks acquired, I have wrapped them inside of a transaction.

USE AdventureWorks2012;

GO

–Check the locks acquired during a normal UPDATE

BEGIN TRANSACTION NormalUpdate

    UPDATE hredh

    SET hredh.ShiftID = 2

    FROM HumanResources.EmployeeDepartmentHistory AS hredh

    WHERE hredh.BusinessEntityID = 215;

    –Check the nature of the locks applied

    SELECT sdtl.request_type,

           sdtl.request_status,

           sdtl.request_mode,

           sdtl.resource_type,

           DB_NAME(sdtl.resource_database_id) AS DatabaseName

    FROM sys.dm_tran_locks AS sdtl;

ROLLBACK TRANSACTION NormalUpdate

GO
USE AdventureWorks2012;

GO

–Check the locks acquired during an UPDATE WITH (NOLOCK)

BEGIN TRANSACTION UpdateWithNolock

    UPDATE hredh

    SET hredh.ShiftID = 2

    FROM HumanResources.EmployeeDepartmentHistory AS hredh WITH (NOLOCK)

    WHERE hredh.BusinessEntityID = 215;
    –Check the nature of the locks applied

    SELECT sdtl.request_type,

           sdtl.request_status,

           sdtl.request_mode,

           sdtl.resource_type,

           DB_NAME(sdtl.resource_database_id) AS DatabaseName

    FROM sys.dm_tran_locks AS sdtl;

ROLLBACK TRANSACTION UpdateWithNolock

GO

Comparing the result sets of both these queries shows us that the same number and nature of locks were acquired by SQL Server for both queries during the UPDATE.

request_type	request_status	Qry1 request_mode	Qry2 request_mode	Qry1 resource_type	Qry2 resource_type	DatabaseName
LOCK	GRANT	S	S	DATABASE	DATABASE	AdventureWorks2012
LOCK	GRANT	S	S	DATABASE	DATABASE	AdventureWorks2012
LOCK	GRANT	X	X	KEY	KEY	AdventureWorks2012
LOCK	GRANT	IX	IX	PAGE	PAGE	AdventureWorks2012
LOCK	GRANT	IX	IX	PAGE	PAGE	AdventureWorks2012
LOCK	GRANT	X	X	KEY	KEY	AdventureWorks2012
LOCK	GRANT	X	X	KEY	KEY	AdventureWorks2012
LOCK	GRANT	X	X	KEY	KEY	AdventureWorks2012
LOCK	GRANT	X	X	KEY	KEY	AdventureWorks2012
LOCK	GRANT	IX	IX	PAGE	PAGE	AdventureWorks2012
LOCK	GRANT	X	X	KEY	KEY	AdventureWorks2012
LOCK	GRANT	IX	IX	OBJECT	OBJECT	AdventureWorks2012

In fact, the official MSDN/Books-On-Line page for the UPDATE statement [Link] clearly states:

“NOLOCK and READUNCOMMITTED are not allowed”

If you do specify the WITH (NOLOCK) table hint, it is conveniently ignored by the database engine.

Developer: I guess the understanding of the entire team was incorrect. Thank-you, Nakul for explaining the detailed reason behind the code review comment.

Me: You are most welcome! Please note that Microsoft has already marked the use of NOLOCK in INSERT, UPDATE and DELETE statements as a deprecated feature. Hence, while today the database engine ignores the hint, it will produce an error in one of the future versions.

Further Reading:

• Pinal Dave, a.k.a. “SQLAuthority” (B|T): What Kind of Lock WITH (NOLOCK) Hint Takes on Object? [Link]
  
• Books-On-Line/MSDN: Table Hints in SQL Server [Link]
  
• Books-On-Line/MSDN: Update Statement [Link]

Important Note

[Added: 10/01/2013, 10:15AM IST]

Please note that using WITH (NOLOCK) on the target table in a complex UPDATE statement may cause corruption in the non-clustered indexes in SQL Server 2008. Please refer https://support.microsoft.com/kb/2878968/en-us for details. Thank-you, Chintak for drawing attention to this KB article.

Until we meet next time,

Be courteous. Drive responsibly.

[Edit: 09/30/2013, 10:55AM IST – A schema-stability lock is abbreviated as Sch-S, not Sch-M.]

We were recently working on an implementation and had to introduce a couple of CHECK constraints to maintain logical integrity of the database with respect to the business rules. As we were going through the table design, one of my colleagues asked a very interesting question:

Most of our constraints are quite simple, mostly range checks which involve a maximum of two columns. Is it possible to have a complex CHECK constraint which involves more than two columns?

The answer, quite simply is Yes! CHECK constraints can be complex, provided they continue to be an expression that evaluates to a Boolean value.

Demo

I will take a sample table derived from my post earlier in the week:

USE tempdb ;
GO
--Safety Check
IF OBJECT_ID('dbo.ComplexCheckConstraint', 'U') IS NOT NULL 
    DROP TABLE dbo.ComplexCheckConstraint ;
GO

--Create the temporary table
CREATE TABLE dbo.ComplexCheckConstraint
    (
      StartDate DATE,
      StartTime TIME,
      EndDate DATE,
      EndTime TIME,
    ) ;
GO

Now, let us create a complex CHECK constraint on the table:

USE tempdb;
GO
--Add the complex constraint
ALTER TABLE dbo.ComplexCheckConstraint
    ADD CONSTRAINT chk_EndDate CHECK ( (EndDate > StartDate) 
                                       OR (EndDate = StartDate AND 
                                           EndTime > StartTime) 
                                     );
GO

Note that we have five operators (3 comparison operators and 2 logical operators) and four columns (EndDate, StartDate, EndTime and StartTime). To check if the constraint works or not, let us attempt to insert some invalid data into the test table:

USE tempdb;
GO
--Attempt to insert some invalid data
INSERT  INTO dbo.ComplexCheckConstraint
        (
          StartDate,
          StartTime,
          EndDate,
          EndTime
        )
VALUES  (
          '2013-09-19',
          '08:00',
          '2013-09-19',
          '07:00'
        ) ;
GO

We get the following error, proving that the complex table constraint was created and is in effect.

Msg 547, Level 16, State 0, Line 2

The INSERT statement conflicted with the CHECK constraint "chk_EndDate". The conflict occurred in database "tempdb", table "dbo.ComplexCheckConstraint".

The statement has been terminated.

Attempting to insert valid data works fine and does not produce a violation.

Summary

CHECK constraints help to maintain logical consistencies in the database. They can help validate data with respect to a given pattern and also help in ensuring that the data in a column is well within the acceptable limits as defined by the business – irrespective of the complexity of the requirement.

Further Reading:

• Using Regular Expressions with CHECK constraints [Link]
• Defining CHECK constraints on computed columns [Link]

Until we meet next time,

Be courteous. Drive responsibly.

Most enterprise applications have something a module similar to a calendar or a planner which tracks information like the start and end date/times of a particular task and/or activity. Over the years, I have seen logical data inconsistencies (especially if the application has been around for a while) wherein the end date/time is sometime earlier than the start end/time. For example, a task may start on September 23, 2013 10AM and finish on September 23, 2013 9AM – which clearly, is an issue.

If these systems were re-engineered after the advent of SQL Server 2008, the date/time value is actually stored in separate fields – a DATE column and a TIME column and a computed column is available that contains the date/time representation. Here’s an example:

USE tempdb ;
GO
--Safety Check
IF OBJECT_ID('dbo.ComputedColumnConstraintCheck', 'U') IS NOT NULL 
    DROP TABLE dbo.ComputedColumnConstraintCheck ;
GO

--Create the temporary table
CREATE TABLE dbo.ComputedColumnConstraintCheck
    (
      StartDate DATE,
      StartTime TIME,
      StartDateTime AS ( CAST(StartDate AS DATETIME)
                         + CAST(StartTime AS DATETIME) ),
      EndDate DATE,
      EndTime TIME,
      EndDateTime AS ( CAST(EndDate AS DATETIME) 
                       + CAST(EndTime AS DATETIME) )
    ) ;
GO

Normally, the solution to prevent logical data inconsistencies is to have a CHECK constraint on the column. But, can a CHECK constraint be defined on a computed column?

Let us attempt to create the required CHECK constraint for our example:

USE tempdb ;
GO
--Attempt to add the Check Constraint    
ALTER TABLE dbo.ComputedColumnConstraintCheck
ADD CONSTRAINT chk_EndDateTime CHECK ( EndDateTime > StartDateTime ) ;
GO

The following error message is encountered and the constraint is not created when we attempt to execute the SQL statement provided above:

Msg 1764, Level 16, State 1, Line 2

Computed Column ‘StartDateTime’ in table ‘ComputedColumnConstraintCheck’ is invalid for use in ‘CHECK CONSTRAINT’ because it is not persisted.

Msg 1750, Level 16, State 0, Line 2

Could not create constraint. See previous errors.

The first error message is when the error actually occurred and hence that is the message of interest. The message quite clearly tells us that the CHECK constraint definition was invalid because the underlying column was not persisted. Because constraints are in-turn implemented as indexes, it makes sense to have the column as computed.

So, let us convert the column to a computed column. We have at least two methods to convert a non-persisted computed column to a persisted one:

• Create an index using the computed column
• Drop the column and re-create it as persisted

For the purposes of this demo, we will be using the 2nd method (drop-and-recreate the column). Once the persisted columns are created, we will attempt to create the CHECK constraint again:

USE tempdb;
GO
--Now, alter the computed column to make it persisted
ALTER TABLE dbo.ComputedColumnConstraintCheck
    DROP COLUMN StartDateTime;
ALTER TABLE dbo.ComputedColumnConstraintCheck
    ADD StartDateTime AS ( CAST(StartDate AS DATETIME) 
                           + CAST(StartTime AS DATETIME) ) PERSISTED;
GO

ALTER TABLE dbo.ComputedColumnConstraintCheck
    DROP COLUMN EndDateTime;
ALTER TABLE dbo.ComputedColumnConstraintCheck
    ADD EndDateTime AS ( CAST(EndDate AS DATETIME) 
                         + CAST(EndTime AS DATETIME) ) PERSISTED;
GO

--Attempt to add the Check Constraint    
ALTER TABLE dbo.ComputedColumnConstraintCheck
ADD CONSTRAINT chk_EndDateTime CHECK ( EndDateTime > StartDateTime ) ;
GO

Now that the constraint has been created, let us attempt to insert some data which violates the constraints.

USE tempdb;
GO
--Attempt to insert some invalid data
INSERT  INTO dbo.ComputedColumnConstraintCheck
        (
          StartDate,
          StartTime,
          EndDate,
          EndTime
        )
VALUES  (
          '2013-09-19',
          '08:00',
          '2013-09-19',
          '07:00'
        ) ;
GO

We immediately encounter the following error and the INSERT fails:

Msg 547, Level 16, State 0, Line 2

The INSERT statement conflicted with the CHECK constraint "chk_EndDateTime". The conflict occurred in database "tempdb", table "dbo.ComputedColumnConstraintCheck".

The statement has been terminated.

Summary

CHECK constraints help to maintain logical consistencies in the database. They can help validate data with respect to a given pattern and also help in ensuring that the data in a column is well within the acceptable limits as defined by the business. Having the ability to enforce business validations via the use of CHECK constraints on computed columns is an added advantage in making the database design robust.

Further Reading:

• Can a Computed Column be used as a Primary Key? [Link]
• Using Regular Expressions with CHECK constraints [Link]

Until we meet next time,

Be courteous. Drive responsibly.

Whenever a database with Service Broker enabled is restored, we notice that:

• The Service Broker is disabled in the restored copy
• The Service Broker GUID is NOT re-generated by default
• We need to manually re-enable Service Broker after the restore

The reason we need to manually re-enable Service Broker after the database restore is to prevent misdirection of broker messages to an incorrect copy of the database. I looked at this reason in my post here [Link].

After the post, I received a couple of questions on various channels. The most common question was:

We use Service Broker, and we need to have two identical copies of the same database for testing purposes. How can we ensure that Service Broker continues to work on both databases without any conflict?

The answer is actually quite simple. As mentioned in my previous post, we need to regenerate the Service Broker GUID. The scripts below show the various statements that one can use to enable Service Broker.

New Database AND/OR Restoring a copy of the database (for purposes other than recovery)

We need to generate a new Service Broker GUID in the following cases:

• Service Broker is being enabled in the database for the first time
• A copy of the database is being restored for purposes other than database recovery (testing, troubleshooting or other purposes)

--Enable the Broker and re-generate the Service Broker GUID
ALTER DATABASE AdventureWorks2012 SET NEW_BROKER;
GO

Please NOTE: Using NEW_BROKER will clear out any conversations in the database, however no error messages will be sent to the peer endpoint.

Restoring to a point-in-time

When restoring to a database to a point-in-time, it may happen that the existing conversations in the database may be invalid. In such cases, we need to reject/error these messages and send the appropriate message to the peer end-point.

Point-in-time restores are generally required during a database recovery which means that we cannot change the Service Broker GUID (else all messaging routes to the broker identifier will become invalid). To achieve this, we have the ERROR_BROKER_CONVERSATIONS option:

--Error out all conversations in the database
--Preserve the Broker GUID
--Enable the Broker message delivery
ALTER DATABASE AdventureWorks2012 SET ERROR_BROKER_CONVERSATIONS;
GO

Enabling Service Broker on an Existing Database

Enabling Service Broker on an existing database is quite simple – all one needs to do is use the ENABLE_BROKER option.

--Enable the Broker message delivery
--Preserve the Service Broker GUID
ALTER DATABASE AdventureWorks2012 SET ENABLE_BROKER;
GO

Disabling Service Broker on an Existing Database

The process of disabling the Service Broker on an existing database is similar to the enable process – one needs to do is use the DISABLE_BROKER option.

--Disable the Broker message delivery
--Preserve the Service Broker GUID
ALTER DATABASE AdventureWorks2012 SET DISABLE_BROKER;
GO

IMPORTANT NOTE:

Please note that when we enable the Service Broker, database lock is requested. Hence, please close all open connections to the database before attempting to enable the broker. If on the MSDB database, please also stop SQL Server Agent.

Further Reading

• Database Restore – manually enabling Service Broker – Msg 9772 [Link]
• Managing Service Broker Identities [Link]

Until we meet next time,

Be courteous. Drive responsibly.

In most development and QA environments, one would find that a baseline copy of the database is restored and upgraded again to “begin from a clean state”. If your database architecture uses service broker to carry out background processes, you would have observed that whenever the database is restored on the same instance or on a different instance, we need to re-enable the broker manually:

ALTER DATABASE AdventureWorks2012 SET ENABLE_BROKER;
GO

The Question

Our database architecture uses Service Broker. Recently, we encountered this situation in a test lab where a production database was brought in-house for some testing, and that got us thinking:

Why do we need to manually re-enable Service Broker on a restored database?

Answer

The answer is available (as always) in the MSDN documentation, the extracts from which are shown below.

• Service Broker routing relies on a unique identifier in each database to correctly deliver messages
• Each database contains a Service Broker identifier that distinguishes it from all other databases in the network. The service_broker_guid column of the sys.databases catalog view shows the Service Broker identifier for each database in the instance
• Service Broker routing uses the Service Broker identifier to ensure that all messages for a conversation are delivered to the same database
• To correctly support message delivery, each Service Broker identifier should be unique across all instances of the Database Engine on the same network. Otherwise, messages could be misdirected. When a new database is created, it is assigned a new Service Broker identifier that should be unique in the network. The identifier is restored when the database is either restored or attached

If the restore is intended to replace the original database (also applicable if the database is being moved to a different instance), then we would not want the Service Broker identifier to change. However, if the purpose of restoring the backup is to create a copy of the database, then the identifier should be changed.

To provide a clear explanation, I have come up with a small demo below:

Base State

To begin with, let us create a test database and enable service broker on the same.

USE master;
GO

--Create a test database
CREATE DATABASE ServiceBrokerFlagTesting;
GO

--Enable Service Broker on the database
ALTER DATABASE ServiceBrokerFlagTesting SET ENABLE_BROKER;
GO

--Check the database properties
SELECT 'Before Backup' AS Stage,
       sd.is_broker_enabled AS IsServiceBrokerEnabled,
       sd.service_broker_guid AS ServiceBrokerGUID,
       sd.name AS DatabaseName, 
       sd.database_id AS DatabaseId
FROM sys.databases AS sd WHERE sd.name = 'ServiceBrokerFlagTesting';
GO

Let us now create a backup of this database and drop the database so that we can restore it again from the backup. (Simple scripts to back-up and restore databases from a UNC path and/or disk are available here. You may customize them for your requirements.)

--Backup the database
USE master;
GO
EXEC SP_ADDUMPDEVICE 'disk',
                     'networkdrive',
                     'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLBackupServiceBrokerFlagTesting.bak'

BACKUP DATABASE ServiceBrokerFlagTesting TO networkdrive;

EXEC SP_DROPDEVICE 'networkdrive';
GO

--Drop the database
ALTER DATABASE ServiceBrokerFlagTesting SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE ServiceBrokerFlagTesting;
GO

Restore 2 copies from the same backup and query Service Broker Information

Now, let us restore two copies of database from the same backup and query the Service Broker information.

USE master;
GO
--Now, restore the backup to two databases
EXEC SP_ADDUMPDEVICE 'disk',
                     'networkdrive',
                     'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLBackupServiceBrokerFlagTesting.bak';

RESTORE DATABASE ServiceBrokerFlagTesting FROM networkdrive
WITH
MOVE 'ServiceBrokerFlagTesting' 
  TO 'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLDATAServiceBrokerFlagTesting.mdf',
MOVE 'ServiceBrokerFlagTesting_log' 
  TO 'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLDATAServiceBrokerFlagTesting_Log.ldf';

RESTORE DATABASE ServiceBrokerDummy FROM networkdrive
WITH
MOVE 'ServiceBrokerFlagTesting' 
  TO 'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLDATAServiceBrokerDummy.mdf',
MOVE 'ServiceBrokerFlagTesting_log' 
  TO 'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLDATAServiceBrokerDummy_log.ldf';

EXEC SP_DROPDEVICE 'networkdrive';
GO

--Check the Service Broker status - notice the Broker GUID
SELECT 'After Restore' AS Stage,
       sd.is_broker_enabled AS IsServiceBrokerEnabled,
       sd.service_broker_guid AS ServiceBrokerGUID,
       sd.name AS DatabaseName, 
       sd.database_id AS DatabaseId
FROM sys.databases AS sd 
WHERE sd.name = 'ServiceBrokerFlagTesting' 
   OR sd.name = 'ServiceBrokerDummy';
GO

As can be seen from the screenshot above, both the databases have the same value for the service_broker_guid. Also, notice that Service Broker is not enabled in either of the databases.

Enabling Service Broker and Error Msg 9772

Now, assume that both databases can be enabled to process the broker messages. If this is allowed, then we would see misdirected messages, i.e. a message that may be bound for database id 9 is being processed by database id 8 which would not be good. It is just like having two roads on opposite ends of the city leading to confusion amongst the motorists.

It is for this reason that SQL Server by default always disables message delivery when a database is restored or is taking part in mirroring and/or is the destination in a log shipping scenario.

Attempting to enable the service broker for both databases results in the following error message when the 2nd database is being processed:

--Enable the Broker
ALTER DATABASE ServiceBrokerFlagTesting SET ENABLE_BROKER;
GO

/*
Msg 9772, Level 16, State 1, Line 1
The Service Broker in database "ServiceBrokerDummy" cannot be enabled because there is already an enabled Service Broker with the same ID.
*/
ALTER DATABASE ServiceBrokerDummy SET ENABLE_BROKER;
GO

Msg 9772, Level 16, State 1, Line 1

The Service Broker in database "ServiceBrokerDummy" cannot be enabled because there is already an enabled Service Broker with the same ID.

Summary

When working with Service Broker, one needs to be careful about message delivery. If a service broker enabled database needs to be restored for troubleshooting and/or data recovery purposes, then:

• When a database is restored/attached, the message delivery to the database would always be disabled
• If Service Broker needs to be enabled for a copy of the same database on the same network, then the service broker GUID must be changed via the use of the NEW_BROKER option. This option activates the broker and also generates a new service broker identifier for the database
• Care must be taken to ensure that only one database with a given Service Broker identifier has message delivery active. Otherwise messages would be misdirected to a wrong copy of the database

Further Reading

• Managing Service Broker Identities [Link]
• Service Broker Routing [Link]

Until we meet next time,

Be courteous. Drive responsibly.