Recently I had a need to create some unit tests for a change to the database that was under development. I needed to change the database so that the NOT FOR REPLICATION option was set on all keys, constraints, indexes and triggers. Following an agile engineering best practice I created a schema unit test for each of these object types (more on this later) and ran the test. Naturally the test, as I expected, failed. I was now ready to make my change.
I decided to use my sandbox database and generated some sql to create the sql to complete this task. Below is the script I used to generate the change to the check constraints:
/*
Set the results pane to Text
This Script Generates the sql to drop all the constraints from a database and then adds them back with NOT FOR REPLICATION set
*/
SET NOCOUNT ON
SELECT 'SET XACT_ABORT ON'
-- ,''
-- ,''
-- ,''
-- ,''
UNION ALL
SELECT 'BEGIN TRANSACTION'
-- ,''
-- ,''
-- ,''
-- ,''
SELECT 'Alter Table '+QUOTENAME(SCHEMA_NAME(ta.[SCHEMA_ID]))+'.'+QUOTENAME(ta.NAME)+' DROP CONSTRAINT '+QUOTENAME(cc.NAME)
-- ,SCHEMA_NAME(ta.[SCHEMA_ID]) TableSchemaName
-- ,ta.NAME TableName
-- ,cc.NAME CheckConstraintName
-- ,cc.definition CheckConstraintDefinition
FROM sys.check_constraints cc
JOIN sys.tables ta
ON cc.parent_object_id = ta.[OBJECT_ID]
WHERE is_not_for_replication = 'false'
UNION ALL
SELECT 'Alter Table '+QUOTENAME(SCHEMA_NAME(ta.[SCHEMA_ID]))+'.'+QUOTENAME(ta.NAME)+' ADD CONSTRAINT '+QUOTENAME(cc.NAME)+' CHECK NOT FOR REPLICATION '+cc.Definition
-- ,SCHEMA_NAME(ta.[SCHEMA_ID]) TableSchemaName
-- ,ta.NAME TableName
-- ,cc.NAME CheckConstraintName
-- ,cc.definition CheckConstraintDefinition
FROM sys.check_constraints cc
JOIN sys.tables ta
ON cc.parent_object_id = ta.[OBJECT_ID]
WHERE is_not_for_replication = 'false'
UNION ALL
SELECT 'COMMIT TRANSACTION'
-- ,''
-- ,''
-- ,''
-- ,''
The abridged result set looks like this (ran this on adventureworks)
-----------------
SET XACT_ABORT ON
BEGIN TRANSACTION
Alter Table [Production].[ProductListPriceHistory] DROP CONSTRAINT [CK_ProductListPriceHistory_EndDate]
Alter Table [Production].[ProductListPriceHistory] DROP CONSTRAINT [CK_ProductListPriceHistory_ListPrice]
Alter Table [Production].[ProductListPriceHistory] ADD CONSTRAINT [CK_ProductListPriceHistory_EndDate] CHECK NOT FOR REPLICATION ([EndDate]>=[StartDate] OR [EndDate] IS NULL)
Alter Table [Production].[ProductListPriceHistory] ADD CONSTRAINT [CK_ProductListPriceHistory_ListPrice] CHECK NOT FOR REPLICATION ([ListPrice]>(0.00))
COMMIT TRANSACTION
This resulting script did what I was after so I ran it against my sandbox. I was then able to re-run my tests. The tests ran through cleanly ... which was nice. I could then script out the affected objects which is much easier now SP2 has re-introduced the one file per object scripting functionality and check in the change.
The Test
declare @rc int
,@er int
select object_id,name,is_not_for_replication -- The test
from sys.check_constraints where is_not_for_replication <> 1
select @rc = @@Rowcount
,@er = @@Error
If @rc >0 or @er <> 0
Begin
RAISERROR('NOT FOR REPLICATION CHECK CONSTRAINT TEST FAILED',16,1) -- SQL Assertion
End
The above test introduces two concepts
-
The database unit test
-
The Verification of the test
Verification in unit testing is a process of test followed by assertion. In this instance I have used a SQL assertion. I.e. I have validated the results of the test with a sql statement. This could have been done by running the sql test against the database in a c# application and testing the resulting ADO.NET dataset. This would be known as a c# or code based assertion but I don't have the skills (yet) to do this myself.
I have also seen people create rigs/harnesses/extensions to nunit to call sql statements or used more database friendly unit testing frameworks. My colleague Howard van Rooijen has mentioned mbunit as an alternative.
However, one recent development in this area has been Microsofts Team Edition for database professionals product. This plugs into the Team Test framework of Team System and offers a Code Assertion option. It's actually very friendly, gui/designer based, and I believe it is extensible whilst also hooking into the code coverage and unit test generation functionality. However, I think I'll cover this in another post.
Cheers, James