Survey: how do you unit test your T-SQL?

Integration tests.  I have used FitNesse in the past, as well as Silk test.  Silk is more comprehensive, but something that can look past the flakiness of the UI is better for my needs.

Full blown nightly integration tests are extremely important for any serious professional development.

I use my own (public) tool, available here:

http://www.codeplex.com/queryunit

We don't :(. I would love to be able to automatically test our SQL Server code but I'm not aware of any tools so we wrote procedures, run them and if we get the results expected they're deployed.

@Akex

I don't know how many poeple in total (in the world) are using my tool, but I can tell you that we're using it in Italy every day.

I try to write queries that returns aggregated values and match the expected results with all values returned (when possibile: if we have 100 values as a resultset with test a fraction of them picked at random)

For now yes basically only a Proof-Of-Concept, but I have plans to develop something much more usable and integrated with SSMS.

I have created a publically available database testing framework.

It can be used to test both expected db schema and values returned.

You can read more about it here - http://michaelbaylon.wordpress.com/database-testing-framework/

We use combination of integration tests, nunit tests and our own db comparers. For ad hoc testing I use simple unit tests written in T-SQL. I wrote about them on my blog.

http://sqlblogcasts.com/blogs/piotr_rodak/archive/2010/05/24/good-practices-database-programming-unit-testing.aspx

Alex, I carry out some very simple tests on a database  - expected total count for each type of SQL object and pass in a list of their names.

These tests ensure that all required objects are in the database.

If someone then adds or deletes a SQL object without updating the tests - they will fail. The same if they refactor/change an object name.

These sorts of tests can be really useful if you are working with an existing database and want to start building some automated tests.

They don't test what the database should do - there are other types of tests that can do that - but they do test what should be present in your db schema.

My understanding of compare tools is that they would not explicitly do the above (I have not looked at the API offered by such tools so I might be mistaken on this).

They can test that database A is the same/different to database B and show any differences.

Database A and B might be the same - but neither might contain a sproc that you expect to be present (for example a change script has not been applied yet).

I can see compare tools being useful in an overall continuous integration/build process - but more around deployment testing. Following a process similar to that below:

Carry out nightly build.

Run unit tests on 'build' - test both application and database

Auto create installers/build scripts

Deploy to a 'test' environment

Test deployment using a compare tool ie ensure 'build' and 'test' are the same.

Take a look at T.S.T.:

http://tst.codeplex.com