Ideal unit tests are easy to roll out, easy to maintain, run fast, and make a difference: without them making changes and troubleshooting are more difficult.
Some common examples of unit tests do not grade well against this criteria. For instance, writing unit tests to verify that a database or a table exists is surely easy, and is a nice learning excersize. However, such tests do not make troubleshooting any easier: if our code refers a non-existing table or database, the database engine will throw a clear to understand error message. If this error message does not get stored in the error log, we should improve error handling. To verify that the DDL in our version control is in sync with the real thing, we can use tools such as SQL Compare.
Explicitly testing database modifications is a very interesting challenge. We enjoyed solving it, and wrote an article describing the approach: Close These Loopholes - Testing Database Modifications. However, explicitly testing database modifications is rarely feasible: typically testing database modifications runs too slow, and the tests are quite brittle - we usually need to maintain them if the database schema changes. Suppose, for example, that we have added one more column to a table. If our select stored procedure does not use this new column, all existing unit tests invoking it can run as is. Unit tests verifying modifications of this table, however, usually need to change - they need to either ignore this new column, or to include this new column in their expected results.
Usually we just invoke stored procedures with database modifications to build test data, as follows:
EXEC Writers.InsertCustomer @ID=1, @LastName='Kirk', @FirstName='Burns';
EXEC Writers.InsertCustomer @ID=2, @LastName='Jason', @FirstName='Wu';
EXEC Writers.InsertCustomer @ID=3, @LastName='Jane', @FirstName='Larsen';
EXEC Writers.InsertCustomer @ID=4, @LastName='Dawn', @FirstName='Arrow';
EXEC Writers.UpdateCustomer @ID=1, @LastName='Kurt', @FirstName='Burns';
EXEC Writers.DeleteCustomer @ID=4;
The excpected results of such modifications are tested implicitly, when we run selects against this test data nd verify that we get the expected results.
If the data being selected includes identities, rowversion columns, or other columns that are not going to be the same the next time we run test, we can either exclude them from our checks, or make sure that they come out exactly the same every time we run test. Usually it is way simpler to make sure that the data is the same. Note that we cannot do that for rowversion columns - we have no cotrol over their values. Also if we insert multiple rows in one statement, we cannot guarantee which rows get which identities, unless we use IDENTITY_INSERT. The following script demonstrates it:
-- we need this because DBCC CHECKIDENT has some gotchas
INSERT INTO dbo.Customer DEFAULT VALUES
DBCC CHECKIDENT('Customer', RESEED, 1);
-- add your modifications here
UPDATE dbo.Customer SET ModifiedBy = 'TestUser', LastModifiedDate = '20100930' ;
Note that because DBCC CHECKIDENT has some gotchas that look and feel like documented bugs, we really need this try block in the script, otherwise we shall not be getting consistent results.
Alternatively, we can exclude the identity, ModifiedBy, and LastModifiedDate from testing, but that somewhat complicates the process of rolling out tests.
This post continues the series on unit testing, the previous post is How to Benefit from Unit Testing T-SQL: choosing what to test
The next post is How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests