How do you unit test your T-SQL [closed]

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

Have a look at this TSQLUnit and this tSQLt.

I have experimented with using a few different frameworks in many languages to test T-SQL, such a junit or nunit. The reason I went this route was to take advantage of the rich testing environments in these other languages. If for example you use nunit it has a nice command line and gui viewer to look at the status of your tests and because your using .net to write your tests it hooks into sql server very easily. JUnit has some nice integration with a lot of commercial and open source IDE environments such as NetBeans and IDEA as well making T-SQL testing more like Java code testing, which is very rich. The negative is you are not only writing T-SQL you are also writing java or .net to test your T-SQL.

I have also used Ruby with Rake(it's like make or ant) and made system calls to sqlcmd to executed sql scripts which in turned contained tests. The scripts return values and strings back to ruby to pass or fail a test. I liked this approach the best as it was very lean and easy for others to pick up on. The other routes mentioned above required developers to use .net or java which if you have all DBA types might be hard to overcome where the ruby with sql scripts approach is easier to sell from my experience. DBA types tend to be open and able to pick up scripting like languages easy and Ruby has a low learning curve and the scripts are easy to run relative to a .net or java class.

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

Probably only 20% only because most databases systems have historically not had tests created for them as they were created so I'm in the process of retro actively adding tests and adding tests as new defects and enhancements come up.

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

The database is the bread and butter of most businesses. It always pays off in my opinion. If your business tolerates junk and high failure rates for the customer then no testing is probably not worth it as it's not free to do.

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

I will love to see if anyone comes up with an answer for this that isn't absurd. Kinda like why don't you put your kid in a car seat when driving down the road..."They cost too much"..."Takes too much time to put the child in it".."He's safe without it".."What could go wrong to justify it".."There's just not enough time" All of these are bs.

Yeah I know a little extreme maybe, but really if you have a system and it brings the company value it should be united tested to help catch some issues before they become production issues. Unit testing isn't a panacea, but it's one tool we have to do the best we can.

I would say overall somehow most people give the database a free pass when it comes to structured testing. I have no idea why this is, but I have seen it at company after company. I would love to see that change.

For a unit-testing tool, I've had most success with DBFit.

I've never found a tool to measure TSQL test coverage.

Having accepted that there's a level of pain involved in setting up tests, I've found that I can improve the quality of my code by unit testing, once a project reaches a more than trivial level of complexity.

Like regular code, some things in T-SQL have to be tested differently than others. If you code-generate a lot of static things (like triggers or views), typically the testing load is lighter.

Our particular system is almost completely coded in T-SQL.

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

We have known good results that we compare against. On our monthly analysis runs we compare month on month and run on run to understand the impact of code and data changes. Our main tool is a stored proc which will compare two tables/views/subsets and identify differences (with threshold options).

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

We validate the data produced in most processes against historical behavior. When making code changes due to business requirements, an impact analysis is performed comparing outputs from both runs. We also rely heavily on exception reporting to determine in advance when data is not conforming to expectations/configuration (constraints in the schema where possible).

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

Yes

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

Our unit testing is at each of our "process" levels. Each process usually corresponds to a single stored procedure - so that's the unit we test. Comparing the final outputs would correspond to system testing.

We've recently switched from T-SQL Unit to the Visual Studio 2010 Unit testing framework. The biggest complaint is that we need to manage our own transaction scope within the tests in order to leave the database "clean" for the next test(s). This is something that T-SQL unit provides right at the start.

Our goal is to have 100% coverage for our stored procedures. It enables us to change the underlying table structure and relationships as needed without having to be absolutely in lock step with development (we have independent data and development teams). We haven't started measuring the test coverage yet. We're in the process of developing our first production deployment with this technology stack. For us the interface between the applications and data is stored procedures. First we build the stored procedures enough to meet the needs of the application team and then once the signature has become stable enough to get to continuous integration, then we'll build our unit tests. We wanted to do TDD, but we have a deadline that is making that just not a practical option.

We haven't measured ROI on the testing harness, but experience has shown us that changes to the production database have been very costly.

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

tSQLt 100%

What percentage of your code is covered by unit tests and how do you measure it? How do you decide which modules to unit test first?

Some projects 100%, most less :)

I wrote a T-SQL code coverage tool that was sponsored by redgate and is included in their sql test product:

https://github.com/GoEddie/SQLCover

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

Yes 100%, everytime I have had tests it has helped us move faster. Everytime there were not tests it has slowed us down.

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

I find it inexplicable that people wouldn't unit test their code :)