From XpFaq

'''How do I test database tables?'''

1. Have each test set up its own state and return the DB to
clean-and-pristine when it's done. The effort of doing this will be
substantial at first, but over time is much less than the effort of finding
defects that were masked by test sequencing, or false defects that were
created by changes in test sequencing. That's one of the lessons of
xUnit-test isolation is wonderful.

2. Don't assume one unit test per stored procedure.

--KentBeck

There is no substantial effort to do this for most DBMS. Any DBMS that has transaction processing requires just two statements to return the data to it's pretest state... Begin Transaction and Rollback. 

A more significant issue is unit testing large data sets (when it's a requirement, or when it's impractical to create test data). If it takes to long to run the unit tests due to this, they won't be run.

--JeffWinchell

------
Some information about using in-memory databases to speed up unit tests can be found in EjbUnitTest.

----
see also: RefactoringWithRelationalDatabases, UnitTestsAndDatabases, MockObject
----
CategoryFaq CategoryExtremeProgramming