How to use DTM Data Scrubber as functional and unit testing software for your database solution.

For a long time our team using the verification tool for database testing. In the article we'll share our experience. All examples deals with Transact SQL (SQL dialect for MS SQL Server). You should modify most of statements for your system or environment.

Database Object Existence Check

The first test will identify that required object like table, index, trigger or procedure is exist in the database. We'll use this SQL statement with data verification rule for 'Phones' database schema object:

select 
 case when object_ID('Phones') is NULL 
  then 0 
  else 1
 end

Actually the type of object is not important, it will work with view or procedure as well. The important is to provide correct name qualifiers like schema name and owner object name if necessary.

To create complete rule at the 'condition' page we just compare results with '1'. It is true if the object exists.

Test 1: check for database object existence: query

Test 1: check for database object existence: compare

Unit Test for Stored Procedure

The next rule is unit test for some stored procedure. For example we want to be sure that it returns required value for some input. We'll create one verification rule per test case. For example, we expecting number between 8 and 11 for input 10. I.e. we'll use "in range" check together with following statement: "exec OrderChecks 10".

Test 2: check for stored procedure result set: query

Test 2: check for stored procedure result set: compare

Check User Defined Function

The third example likes the second but we'll operate with function and check 3 results in one rule. Also, we expect that function results are in dictionary, i.e. in the set of table ('PrCodes' in example) column values. We'll use following query:

select dbo.fnOrderByQty(1)
union
select dbo.fnOrderByQty(10)
union
select dbo.fnOrderByQty(25)

The check is "in query result set" with the SQL statement "select distinct(ID) from PrCode" that retrieves dictionary.

Test 3: check for function call: compare

Test for Trigger

The next case dwells with trigger. Our trigger must prevent wrong value insertion. In other words it checks inserted data for business logic and rollbacks the transaction if necessary. We'll try some kind of wrong data and test that the database rejects new row. For easy example, our 'PrCode' table should not accept negative value but we'll try to execute "insert into PrCode values(-1, 'some code')". Our trigger must rollback it with message like " The transaction ended in the trigger". But how to check that? We'll use special check "is SQL statement executed successfully" for this purpose. This check returns 'false' for incorrect statements or canceled transactions that helps us to achieve the goal. Our trigger must cancel that transaction i.e. we should add "not" modifier to the condition.

Test 4: check for trigger functionality: query

Test 4: check for trigger functionality: compare

Test for Data Unicity

The 'pCode' column of 'PrCode' table must contain unique values. How to check that with DTM Data Scrubber. It is easy if we'll compare results of the following query execution with 0:

select count(distinct pCode)-count(*) from dbo.PrCode

This statement returns 0 when number or rows in the table and number of unique values in 'pCode' column are equal only.