Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Test-Driven Development of T-SQL Code Expand / Collapse
Author
Message
Posted Sunday, October 19, 2008 5:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 30, 2013 7:28 AM
Points: 4, Visits: 41
Comments posted to this topic are about the item Test-Driven Development of T-SQL Code
Post #588237
Posted Monday, October 20, 2008 2:25 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 587, Visits: 2,538

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
Post #588412
Posted Monday, October 20, 2008 3:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, September 27, 2014 7:54 AM
Points: 22, Visits: 117
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
Post #588438
Posted Monday, October 20, 2008 5:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #588486
Posted Monday, October 20, 2008 6:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 2013 8:21 AM
Points: 283, Visits: 268
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.



Post #588491
Posted Monday, October 20, 2008 6:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:27 PM
Points: 13,776, Visits: 28,178
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #588501
Posted Monday, October 20, 2008 6:53 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:30 PM
Points: 6,784, Visits: 1,899
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
Post #588506
Posted Monday, October 20, 2008 11:55 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,375, Visits: 1,391
Very interesting discussion. People can learn more than the article. This is why this forum in very special.


Post #588951
Posted Tuesday, October 28, 2008 8:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 1:09 PM
Points: 478, Visits: 1,417
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 :).)

Post #592922
Posted Tuesday, October 28, 2008 8:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:27 PM
Points: 13,776, Visits: 28,178
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #592927
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse