Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Test-Driven Development of T-SQL Code


Test-Driven Development of T-SQL Code

Author
Message
Louis Roy
Louis Roy
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 41
Comments posted to this topic are about the item Test-Driven Development of T-SQL Code
Phil Factor
Phil Factor
SSC Eights!
SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)

Group: General Forum Members
Points: 951 Visits: 2953

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.


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.


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!



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.



Close These Loopholes in Your Database Testing
Close These Loopholes: Testing Stored Procedures.
Close These Loopholes: Testing Database Modifications
Close These Loopholes: Stress-Test those Stored Procedures
Close these Loopholes - Reproduce Database Errors


Best wishes,

Phil Factor
Simple Talk
Hans van Dam
Hans van Dam
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 173
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52662 Visits: 40358
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
tymberwyld
tymberwyld
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 275
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.



Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19851 Visits: 32362
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.

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: Moderators
Points: 7962 Visits: 2708
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.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6505 Visits: 1407
Very interesting discussion. People can learn more than the article. This is why this forum in very special.



Goldie Lesser
Goldie Lesser
Mr or Mrs. 500
Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)

Group: General Forum Members
Points: 552 Visits: 1491
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 Smile.)
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19851 Visits: 32362
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 Smile.)



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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search