As I stated yesterday, using techniques designed for an Agile SDLC for your database, enables you to work within an Agile process. However, Automated Testing provides value for any SDLC. Automated Testing is most easily performed at the object level, and consists of different implementations.
Much like automated testing of application code, you can create automated tests for scalar user defined functions. A scalar UDF takes scalar parameters as input, and returns a scalar output of a SQL Server data type. The input or output may be user defined data types. The UDF may be created using CLR code, or standard TSQL. The test(s) simply calls the function with input parameters and evaluate the output. Automated tests may be written using any automated testing tool able to call a SQL Server UDF. I have used NUnit, MS Test, and basic C# to run these tests. I found NUnit to be the easiest, and MS Test a close second. All you have to do is call the TSQL Code using standard Ado.Net, get the response and evaluate if it returns your expected value. Test for positive and negative results.
Stored procedures need to be tested as well. These tests are harder, especially if they modify data in tables. For a stored procedure that modifies tables, created a test with the following steps:
1) Begin a transaction
2) Execute the stored procedure
3) Evaluate that the data is modified as you would expect
4) Rollback the transaction
NUnit enables this with some extensions that automatically do the transaction and rollback. The advantage of this implementation is that you can execute the test over and over, because the data is not changed permanently. In order for this to work, the evaluation has to be performed in the same database connection, or your evaluation has to be allowed to read uncommitted data.
A stored procedure returning data can be tested much like one that modifies data. The difference is that you call the stored procedure, and evaluate the data returned.
Stored procedure that do work, similar to what you can do in a function, can be tested in the same way as a function.
You can also test Views or Table Valued Functions in the same way you can test a stored procedure that returns data. Provided specific input from the data those object access, you can call a view or table valued function, and evaluate the structure of the data returned, and the contents of the data.
Finally, I also test my reference data, or lookup tables as they are sometimes called. Often those tables contain data that must synchronize with ENUMs in application code. So, they can’t change without code being changed to correspond. For that reason, I will write a unit to cover the data of a lookup table. If I know there is a corresponding ENUM, then I am more likely to write that unit test.
Once you have this suite of tests in place, you can modify your database schema, data, and procedures with confidence. You don’t have to worry that a change you made to a certain object to fix a problem in area B will have an unknown impact on something in area A, that you didn’t remember. Having changed the test(s) for area B, and the underlying code to fulfill the new or modified tests, you now run all the tests, and the tests for area A fail, letting you know there is broader impact immediately. I even use tests as an estimation tool. Someone asks me what it will take to change area A. I make the change, and then run all the tests. Now I see all the tests that have failed. Based on the failures, I can now gather what changes are necessary to the test code and the database code to get it done completely.
In closing, Automated Testing provides you with the necessary confidence to make code changes in a database, resulting in a more stable application. If you do it as you go along, it doesn’t take long, and pays big. If you try to introduce it at the end, it is like cleaning your house once a year; it will feel like it will never end. You’d rather burn the house down and start over.
Cheers,
Ben