October 19, 2008 at 5:06 am
Comments posted to this topic are about the item Test-Driven Development of T-SQL Code
October 20, 2008 at 2:25 am
[p]This has had me scratching my head a bit, as I can't see how this is going to help tackle the insidious dynamic errors, such as deadlocking, that happen in a working database. Unit testing of TSQL code is something we do already- nothing radical here: in fact it is difficult, almost impossible, to write good TSQL without unit-testing everything as you go. It could be that the title of the article led me to expect a more general exposition on Unit Testing of TSQL, rather than a description of one particular test harness.[/p]
[p]I liked the approach of doing everything in TSQL rather than using a C# test-harness, though I'm not yet sure how far one can get with this technique.[/p]
[p]The most telling sentence in this article for me is 'In my opinion, it is much more difficult to refactor SQL code than it is to refactor .NET or Java code because of the lack of tooling (i.e. ReSharper for Visual Studio, etc) and the lack of object-oriented design of code modules within T-SQL code.'. For an experienced database developer, this is a strange sentiment. It is as odd as saying that C# is hard to refactor because it isn't relational![/p]
[p]For a good grounding in the techniques of Unit testing of databases, including the testing of their dynamic behaviour, I would recommend Alex Kuznetsov's series on Simple-Talk.[/p]
Close These Loopholes in Your Database Testing[/url]
Close These Loopholes: Testing Stored Procedures[/url].
Close These Loopholes: Testing Database Modifications[/url]
Close These Loopholes: Stress-Test those Stored Procedures[/url]
Close these Loopholes - Reproduce Database Errors[/url]
Best wishes,
Phil Factor
October 20, 2008 at 3:43 am
This method seems a bit obvious to me. Of course large units should be divided into smaller parts that can be tested and of course you should test ervery component. About 15 years ago I attended a course: 'Software Engineering' where I learned to do that, so nothing really new here.
The danger of this method, or this particular (re)introduction of it, lies in the fact that the bigger scope is neglected. Most units are part of one or more bigger parts. If you work the way that is presented here, you easily loose the big picture and the focus on the final goals with it.
Those who do keep the bigger picture in mind will recognize that reusability and maintainability is not served by this method, because the focus is too narrow: on specific results for specific situations. In my opinion that can lead to systems with large amounts of small, specific components. A new function? A new component. After time nobody knows what component serves what purpose, or _if_ it still is used.
I'm not saying that the method guarantees the above, but a good method should guarantee that it will not happen.
Kind regards,
Hans van Dam
October 20, 2008 at 5:46 am
I agree with the problems that the folks in the posts above have cited. Further, there is an absolutely glaring problem with this method (no fault of the author)...
"refactor the code if necessary to remove code duplication and improve the overall design of the code while preserving its functionality"
The whole reason, it appears, for Agile and TDD is time to market... no one will ever go back and refactor the code and no one will ever test it properly until it actually hits the streets. Testing properly should include performance tests for about 1 to 2 times the expected scale. The concepts behind Agile and TDD are absolutely wonderful if they're not tainted by the urgency of a poorly planned schedule.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2008 at 6:15 am
One more thing you can do for unit tests is create a Database or Sql Server Project in Visual Studio for Database Architects. These projects have built-in tests as well which can facilitate most of what is being done here. Of course the other suites probably offer more, this could get you going with the basics until you need something more.
I also agree with the other posts, I test as I go with Procs, but I guess the point of Unit Tests is that you often forget at a later time how you actually tested your Procs with all the myriad of Inputs / Outputs. Unit Tests just ensure that as time goes on or your Proc needs to handle new Inputs / Outputs, you can run all the previous tests for regression testing.
October 20, 2008 at 6:45 am
I've been working on the whole concept of unit testing TSQL for quite a while. At this point, I'm a bit more in Phil's camp. We do "unit testing" as a matter of course, but getting into this formalized, structuralized methodology just hasn't paid off a lot in terms of benefits. One thing you can do to make your life easier though, is to automate, as much as you can, the generation of the unit tests. I wrote up on TSQL Unit a while back here on SQL Server Central[/url].
We've actually discovered that the number of unit tests we need has dropped radically because of a few things. First, we moved most of our projects into Microsoft's Visual Studio Team System Database Edition. The instant syntax checking and the ability to test our deployments ahead of time eliminates most of the simple database errors that unit testing is designed to discover. Then, we've found that most of the problem isn't that the database is not returning data as defined by a test, but that it isn't returning data as defined by the program. The fact is, if I take our requirements and write a proc to return them appropriately, that doesn't mean that's what the app actually needs. We still have to go through a round of testing with integration with the app and the unit tests don't help. Finally, we're getting a push towards using Object Relational Mapping software, in our case nHibernate. This means that there won't be a proc at all, therefore no need for unit testing on the database.
Also, the tests you outlined don't test result sets. You're still checking for a single value, not the entire set. We worked out a method of using XML & storing it locally to get a full record set evaluation. Unfortunately, it's a ton of work to maintain. The benefits have yet to outweigh the costs.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 20, 2008 at 6:53 am
I've used Nunit and found it useful, but so far haven't applied it to TSQL (mental block?). I'd like to think unit tests have nothing to do with your development strategy, and in many ways don't have anything to do with QA and acceptance testing. Unit tests serve two purposes; verify that the code works as designed, and to provide a safety net if the code needs to be refactored. Refactoring does happen, often months later, and it's no easier in TSQL than anywhere else, it's easy to miss one thing that causes pain, so re-running those tests, while no guarantee of perfection, often quickly identifies the little snafu's that come along.
I've found developers fall into two camps, they love unit tests or they hate them. They are a bit of a leap of faith, and it's been made harder to show them why to love them because many of the tests are too trivial (can I modify trivial with too?). I like the idea of testing for constraints, more than once I've done tuning where someone moved a table without the constraints and all sorts of bad things happened.
I do like Phil's comment on code. I think the reason I haven't spent time on TSQL unit tests is I think they are properly part of the application unit tests, app developer should be testing the various invocation scenarios. The only time TSQL testing seems to make sense if for jobs/processes that are only TSQL.
I'll also speak up in favor of agile techniques. This is another area where there's been a lot of noise and confusion, and it doesn't work in all scenarios without modification (or perhaps at all), but for lots of common business problems it's a very good solution. Unlike diets and get rich quick schemes, agile works when you stick to "a" formula, not necessarily "the" formula.
October 20, 2008 at 11:55 pm
Very interesting discussion. People can learn more than the article. This is why this forum in very special.
October 28, 2008 at 8:16 am
I spent most of the day researching TSQL Unit for the company I work for.
We are moving towards test-driven development and we are looking for a way to run data validation scripts as part of the development process.
Here are some of the pros and cons I found with this product.
Pros:
1. Easy to implement tests
2. Can run a test from the command line or from an application
Cons:
1. Have to create their tables and stored procedures on every database you want to test.
2. Their stored procedures are using CURSORS!!
3. Have to store your test stored procedures in the database.
4. Not recommended for use on a Production Environment.
So basically, I have recommended against T-SQL Unit.
I feel that there are better ways to unit test stored procedures, many of which were mentioned above.
This product is definitely not useful for running data validation scripts.
(BTW, if anyone knows of a good way to do that please pass it along :).)
October 28, 2008 at 8:23 am
ggraber (10/28/2008)
I spent most of the day researching TSQL Unit for the company I work for.We are moving towards test-driven development and we are looking for a way to run data validation scripts as part of the development process.
Here are some of the pros and cons I found with this product.
Pros:
1. Easy to implement tests
2. Can run a test from the command line or from an application
Cons:
1. Have to create their tables and stored procedures on every database you want to test.
2. Their stored procedures are using CURSORS!!
3. Have to store your test stored procedures in the database.
4. Not recommended for use on a Production Environment.
So basically, I have recommended against T-SQL Unit.
I feel that there are better ways to unit test stored procedures, many of which were mentioned above.
This product is definitely not useful for running data validation scripts.
(BTW, if anyone knows of a good way to do that please pass it along :).)
While I do agree, we use Visual Studio Team System Database Edition for unit testing now, a few of your negatives probably shouldn't be on the list. #4 especially. You shouldn't be running unit tests against production machines, ever. Unit testing is a development only process that should be isolated from other systems, especially a production system.
Also, when we were using TSQLUnit, we only ever created a test database by deploying code there first. You can keep it seperate from your development, integration & QA databases. It's an extra step in development though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 28, 2008 at 8:30 am
Grant Fritchey (10/28/2008)
While I do agree, we use Visual Studio Team System Database Edition for unit testing now, a few of your negatives probably shouldn't be on the list. #4 especially. You shouldn't be running unit tests against production machines, ever. Unit testing is a development only process that should be isolated from other systems, especially a production system.
I guess I wasn't clear about what I meant for #4.
We wanted to use TSQLUnit for data validation as well as unit testing.
For example: let's say we run some data export for a client.
We are creating a file (xml, csv, flat file) to send to them.
After we create the file, we load it into the database.
We then want to run SQL scripts against the file comparing the file to the original data in order to validate the file.
We would have to do this in our Production environment.
Also, when we were using TSQLUnit, we only ever created a test database by deploying code there first. You can keep it seperate from your development, integration & QA databases. It's an extra step in development though.
That sounds like it would take a lot of extra time, and when does development ever have extra time?!? :hehe:
July 7, 2010 at 2:54 pm
Those who are failing to see the value of this may perhaps be failing to see the value of TDD in any context. To quote Robert Martin: "Now most programmers, when they first hear about this technique, think: "This is stupid!" "It's going to slow me down, it's a waste of time and effort, It will keep me from thinking, it will keep me from designing, it will just break my flow." However, think about what would happen if you walked in a room full of people working this way. Pick any random person at any random time. A minute ago, all their code worked."
The point here is *not* to see the big picture. The big picture is painfully obvious: our code is fragile and tightly coupled. And we have no way of being confident that the next developer who touches it won't break it. Quality begins with the first line of code.
This technique is more profound than perhaps you're seeing.http://www.charleshammell.com
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply