Unit Testing - deleting test data

Problem:
Imagine a unit test in which you need to insert test data into database so that the test can run.

After running the test, you need to delete your inserted test data otherwise you will have so much junk in database.

Solutions:
One way, that we used for some time, is that in the the teardown method of the current test fixture, you run a stored procedure to delete all your test data from all the tables.

The problems with this way are:

• Cuncurrency issues when 2 test cases are run at the same time
• Performance issues:
• Unnecessary connection to database tables in order to delete the test data
• Unnecessary use of transaction logs which lead to have larger .ldf file (all the DML scripts affect transaction logs)
• Unnecessary change of indexes (all the DML scripts affect indexes)

The best way is to use transaction in each test case so that only those tables and data affected by the test are involved. In addition, because the changes will be rolled back at the end of the test case, so we won't have concurrency issue and database will remain in a consistent state.

I will write a sample code here later.