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.
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".
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 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 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.