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 1234»»»

Is Unit Testing Important? Expand / Collapse
Author
Message
Posted Monday, November 09, 2009 8:21 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 2:54 PM
Points: 32,819, Visits: 14,964
Comments posted to this topic are about the item Is Unit Testing Important?






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #816280
Posted Monday, November 09, 2009 8:52 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 36,002, Visits: 30,294
"Make it work, make it fast, make it pretty... and it ain't done 'til it's pretty." --Jeff Moden

I not only test each stored procedure, but I test each query in a stored procedure. I test for happy path, not-so happy path, unhappy path, and places that most people don't know exist never mind that there's a path to those places. At the same time, I'm testing for performance and making the code extremely readable along with some substantial comments for the next poor slob that may have to modify my code.

As a result of all my unit and sub-unit testing, I almost never have code come back for a bug or performance problem.

One of the more difficult things to do when testing like that is to convince the managers that it's worth the wait especially if you're the new guy in the company even though they hired you for your years of experience. But it doesn't take long... there's always someone who's willing to risk it all by not testing at all. Those are the people whose time you keep track of along with your own. Then you tell the manager "Remember that project that was finished a month ago? The code was delivered on time for a very aggressive schedule... Ummmm... when are you folks going to be done debugging all the problems you found after release?" They very quickly learn that another "Modenism" rings true...

"If you want it real bad, that's the way you'll usually get it." --Jeff Moden

There's also an inmeasurable side benefit to such testing... it's called "innovation". Sometimes developers will come up with some awesome (sometimes totally new) methods for doing things while they're searching for a "fix" to something they found during their own unit testing.

Let me summarize my thoughts... every minute you spend testing during development will save an equal number of hours in post implementation debugging... maybe more. Developers who don't test their own work should be fired. Managers who don't allow enough time for developers to test their own work should be fired. C* upper level management who don't give managers enough time to allow enough time for developers to test their own work should be fired. The Board of Directors and their customers who don't give C* upper level management enough time to... etc, etc.

When it comes to unit testing, s**t flows uphill.


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

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #816282
Posted Monday, November 09, 2009 9:43 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 2:54 PM
Points: 32,819, Visits: 14,964
Nicely put (he said with a smile on his face)






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #816292
Posted Monday, November 09, 2009 9:53 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 12:58 AM
Points: 1,904, Visits: 2,771
Let me start of with saying, it's not important, it's critical.

Managing db's, with odd proc's running against them, is not a task I want to spend my full day on.
I encourage our developers to write "smarter" code, in their proc's and ad hoc queries.

Blocking, locking, multiple sessions, these are the kind of stuff being implemented into our live environment....
because there was no proper testing done. No performance measures, no execution plan reviews.
All of this adds up to performance issues, more time spent on fixing code, if and only when you can pin point the problem afterwards.

Time is always a factor, to push the developers, to code faster, more, with less information - so I do excuse them, but just a bit.

Final word: Test, test, test and once you feel your code is 100%, test again!!


----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #816294
Posted Tuesday, November 10, 2009 1:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 04, 2013 11:44 AM
Points: 2, Visits: 39
3 years ago I wrote my first fully unit tested stored procedures that synchronizes (in very twisted way) few tables between two servers. One is in Europe second one in Asia and of course whole synchronization should be done in transaction (distributed of course).
It was my experiment, which took me 3 months to write it, and everyone was blaming me for doing it so long.
But when I installed it on the servers there was only ONE not so critical bug that showed up after 1,5 year or so.
Other than that this stored procedure is working without any problem for 3 years.

That convinced me completely.
Post #816331
Posted Tuesday, November 10, 2009 1:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 10:18 AM
Points: 294, Visits: 1,008
Well, where I work we do write unit tests to make sure everything is working as expected. When I first started doing it it was slow but one becomes fast at this after very little time. It's a good way to ensure code quality and that no one makes a change with unexpected results.
Post #816337
Posted Tuesday, November 10, 2009 1:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 04, 2013 11:44 AM
Points: 2, Visits: 39
If someone is intrested how I'm testing stored procedures I want to share.

I have Visual Studio with Resharper.
I create test dll project in c# that is using NUnit as a test framework.

Then I write a tests that
1. Delete test database
2. Create new one
3. Run setup scripts twice - to be sure that it will work correctly also on already installed DB
4. Run each test in a way that all changes done by it are removed after test

The longest part is to write support classes/methods that allow you rollback all your changes from DB after each test (point 4.).
I'm not using transactions for it, because I have many places where I'm testing code that cannot be run under transaction (like db user creation).

In each test I prepare data in database then I run some SQL code and I'm checking results.
Post #816349
Posted Tuesday, November 10, 2009 4:27 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 4,880, Visits: 2,269
As a developer, of course my focus will be on testing when compared to performance. However, I would back this up by the numerous times when, given the option, the customer would rather go live with something that is slow but working. Optimisation is pointless until you have functionally correct results i.e. you cannot necessarily speed something up then make it correct as the correction may invalidate the optimisations.

In my opinion, never humble chaps and chapesses, this is the DBAs equivalent to developers who believe that the customer would rather an additional feature. Neither additional features not performant code are any good without the code being correct.

Having had a near-flame debate yesterday, I certainly don't want to do the same today!!!


Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Post #816398
Posted Tuesday, November 10, 2009 6:31 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 07, 2012 9:23 AM
Points: 304, Visits: 716
My career in database goes back to the late 1970's when there was no SQL Server. In those days, you did 90% of your data work in loops. You could watch loops and even build in stops to double-check data as it processed. In essence, you "saw" and tested your work as it processed.

Then along comes SQL. A "Black Box". You stick in a query, and you get data back - but you don't see much in-between.

Test? Unit Test? ARE YOU KIDDING??? To this day I think I am still suffering from some of the early mistrust and panic brought on by SQL many years ago. I don't see how anybody can NOT test. How do you trust SQL, let alone trust yourself???

When you consider (as I have seen many times), that you can mistakenly and yet very simply use say, INNER JOIN when you should have used LEFT OUTER JOIN and this will happily return the WRONG data without a hiccup - YOU'D BETTER TEST!!!

My Rule is simple and absolute - TEST, and do so presuming that you HAVE made a mistake. When you're done with that, pass it up to QA and let them test too. Nothing goes into production until at least two sets of eyes have beaten the tar out of it.

Think about it - in those early days, it was simple to blow things up. But at least when you did that, you knew, ooops, I made a mistake. Now you have SQL where you can screw up big time with a simple valid command, missed punctuation, or other very minor glitch - and SQL will merrily hand you back data - completely wrong data. TEST TEST TEST, and when your done, TEST some more.


There's no such thing as dumb questions, only poorly thought-out answers...
Post #816439
Posted Tuesday, November 10, 2009 6:40 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:06 AM
Points: 482, Visits: 138
Of course Unit Testing is important! I'm guessing this question was asked as a means of 'stirring the pot.' Some of my best work has come out of fine-tuning stored procedures after unit testing.

I use all of my professional ability and experience to build the best procedure possible. Then, I go into unit testing with a complete lack of confidence in the product, tearing it apart - finding every possible way to break what I have done. That's where you find out what the data is really about. It allows you to anticipate problems that you never would have thought of. It builds your confidence in the product, and puts you in a better position to answer any questions that may arise after the product goes live.
Post #816445
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse