THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

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

How do you unit test your T-SQL? Which libraries/tools do you use?

What percentage of your code is covered by unit tests and how do you measure it?

Do you think the time and effort which you invested in your unit testing harness has paid off or not?

If you do not use unit testing, can you explain why not?

Published Monday, May 03, 2010 5:26 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Erik said:

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.

May 3, 2010 6:47 PM
 

Davide Mauri said:

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

http://www.codeplex.com/queryunit

May 4, 2010 3:45 AM
 

Alexander Kuznetsov said:

Erik,

How much time do your integration tests take to run? Can you elaborate on "the flakiness of the UI"?

Davide,

How many people are using your tool? Do you match the whole output or do you verify only some values?

May 4, 2010 11:34 AM
 

Zack Jones said:

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.

May 4, 2010 12:17 PM
 

Alexander Kuznetsov said:

Zack,

If you are familiar with C#, you might be interested in this series of articles. We have been using it for almost three years:

http://www.simple-talk.com/sql/t-sql-programming/close-these-loopholes---reproduce-database-errors/

May 4, 2010 1:02 PM
 

Davide Mauri said:

@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.

May 5, 2010 2:10 AM
 

Michael Baylon said:

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/

May 18, 2010 7:45 AM
 

Piotr Rodak said:

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

May 24, 2010 3:26 PM
 

Alexander Kuznetsov said:

Michael,

Can you elaborate why are you verifying if database objects exist? Maybe we could just use a tool like SQL Compare instead of writing such test?

Piotr,

This was an interesting read. I agree that NUnit is better for unit testing than T-SQL.

May 26, 2010 4:30 PM
 

Michael Baylon said:

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.

June 7, 2010 3:43 AM
 

Ladi said:

Take a look at T.S.T.:

http://tst.codeplex.com

September 25, 2010 4:45 AM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement