Claypole's World has some good postings around unit testing with SQL.
Verification in unit testing is a process of test followed by assertion. In this instance I have used a SQL assertion. I.e. I have validated the results of the test with a sql statement. This could have been done by running the sql test against the database in a c# application and testing the resulting ADO.NET dataset. This would be known as a c# or code based assertion but I don't have the skills (yet) to do this myself.
I have also seen people create rigs/harnesses/extensions to nunit to call sql statements or used more database friendly unit testing frameworks. My colleague Howard van Rooijen has mentioned mbunit as an alternative.
However, one recent development in this area has been Microsofts Team Edition for database professionals product. This plugs into the Team Test framework of Team System and offers a Code Assertion option. It's actually very friendly, gui/designer based, and I believe it is extensible whilst also hooking into the code coverage and unit test generation functionality. However, I think I'll cover this in another post.