November 10, 2009 at 7:46 am
There are times when I feel I can stay in the "safety zone" without unit testing. For instance, when I'm developing an application for in-house use, where the cost of errors are relatively low, and the operation is fairly trivial and my time is better spent elsewhere. I'm not however discouraging others to use it.
My company has limited resources and relies on me choosing the best use of my time. The company also needs me to keep them relatively safe. When balancing the two, I turn to unit testing for cases where I'm working on something complicated or cost of errors are high. Mind you - I'm not in a shop that builds packaged software and my experience level with TSQL is high.
Other ways to stay in the safety zone that are more light-weight than developing unit tests within a framework include just keeping a .sql file that keeps queries that were used to build/change things and test the results of things. Also, I have an environment where I test first on a development system and all changes to production are first tested and change scripts are kept and versions of things are kept in source control.
I'm a big fan of unit testing. I use TSQLUnit as a framework which gives me set up and tear down. I create test rows with Redgate SQL Data Generator when I think it makes sense.
If a TSQL developer is not accustomed to unit testing, I think it's worth doing even on trivial projects for the sake of learning the skillset so it's there when it's most needed.
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
November 10, 2009 at 7:56 am
First of all Jeff's stuff made me laugh, he is right on track. Unit testing is absolutely critical, especially as the code gets more complex. I test the positive cases, the negative cases, and things that shouldn't ever happen because of other code in there but could.
It takes a little longer but then we don't have to use this quote:
"We aint got time to do it right, but we got time to do it twice" , or three times, or maybe even four..
An even better question is what we think about formal testing methods instead of ad-hoc testing. Its boring but it works and is repeatable..
CEWII
November 10, 2009 at 8:25 am
I think Steve's last sentence was the tickler here.....
<< I think most DBAs would prefer to spend it on making the SQL more efficient, not more tested.
Steve Jones>>
My question is....How do you make something more efficient WITHOUT testing? Tricky, Steve...very ,very tricky......
That being said, you have to test. Test the proc to make sure it runs. Test the processing within the proc to make sure you are manipulating the data you expect. Test the reports to make sure they meet the client department expectations. It's all part of delivering a finished product. Who wants to go back into a process at some time in the future to correct or improve something that you KNEW was not working to YOUR satisfaction when you released the system into production? Yes, I know about deadlines and budgets. You can only do so much. But that is why you are a salaried person...you get to work w/o overtime to solve these dilemmas...:w00t:
November 10, 2009 at 8:34 am
Yes, Unit Testing is important and necessary. Unfortunately, too many places I have been have not required developers to do unit testing, despite my recommendations to do so.
Fast, with the possibility of a rework, has often been the order of the day. This seems to have given rise to a developer outlook for procedures that the procedure meet spec (e.g. the table is populated with the specified data) and they have met the assigned deadline.
November 10, 2009 at 8:37 am
I was recruited to be a db developer full time after dabbling in SQL and stored procedures for several years in the BI world. I had always tested my work but not as a classical 'unit test'. The people I began working with were firm believers in the unit test concept, and I quickly saw the wisdom. I was put on a project for which unit tests didn't exist for half the stored procedures being used, and there were a lot. In spare moments we would generate the missing tests and, as you might have foreseen, found deficiencies in some stored procedures. Some were minor, some were critical. And someone, somewhere, was relying on inaccurate data, depending on the input parameters for the proc. You don't have to be brilliant in generating code, but you can't be arrogant.
November 10, 2009 at 9:30 am
Put simply, testing is part of development, so if you don’t unit test, the job is not done.
The ability to properly test is probably the most important skill for a developer to have.
Developers who do not unit test are incompetent and worthless.
November 10, 2009 at 9:31 am
Ah technical debt... my group has it... lots of it that we are only starting to pay back.
We do unit tests for most things and they are very useful. Except most of the issues that we find are due to load on the system and not any kind of logic errors...
November 10, 2009 at 9:36 am
My current contract is to resolve the Technical Debt. I guess thay can place a figure on it.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 10, 2009 at 9:43 am
I'd be willing to bet that the reason most people don't unit test is not because it's boring; it's because the boss wanted the solution yesterday. Boredom would be a luxury ... at least for the first half day or so! 😉
Robb
November 10, 2009 at 10:12 am
JustANumber (11/10/2009)
In spare moments we would generate the missing tests and, as you might have foreseen, found deficiencies in some stored procedures. Some were minor, some were critical. And someone, somewhere, was relying on inaccurate data, depending on the input parameters for the proc.
I have a few rules also.
Nothing is ever as simple as it appears.
Just because something appeared to work, doesn't mean it did work.
There is a vast amount of "working" code out there that returns invalid results at least some of the time, and very little of it is reconciled against other data that may be correct, or at least have different invalid results.
Just the other day, I saw an existing SQL stored procedure (which needed to return results in seconds) that never got tested on the case that one of the varchar parameters was fed in as blank. As it turns out, not only does it takes dozens of minutes to return results, but, presumably due to parameter sniffing, every subsequent call with even valid parameters takes dozens of minutes until the stored procedure gets recompiled.
This was a simple case of not testing boundary conditions, not believing that it would ever be called incorrectly, or both. Regrettably, it's common.
November 10, 2009 at 11:55 am
Michael Valentine Jones (11/10/2009)
Developers who do not unit test are incompetent...
I can get behind the idea that development isn't complete without unit testing because with unit testing, there is a spectrum between formal and informal. So there's room for pragmatism and common sense to fit the situation. As Wikipedia states (and I agree) "Its implementation can vary from being very manual (pencil and paper) to being formalized as part of build automation." In database development, it can include simply saving a .sql file containing test queries and other artifacts.
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
November 10, 2009 at 12:04 pm
One of the testing approaches I implemented in my last position was as follows:
1) all code development for SQL was fully scripted in independent files in version control.
2) a single deployment script was written that could run against development, testing or QA. This reproduced the entire database structure along with lookup table data such as province/state, country, etc.
3) a second script was maintained with a small set of test data that covered most data conditions (new ones were added as we uncovered each new or unique situation).
This allowed us to run a full unit test at the SQL level, the business unit level or the UI level with a known data set and provided regression testing as we made changes. Of course, we could also run the tests against a full copy of the production data to confirm functionality against larger data sets.
Regards,
Michael Lato
November 10, 2009 at 12:08 pm
You can't performance tune without some testing of how efficient things are. I guess I was wondering as well whether people actually think about testing beyond that. Jeff addressed some of that in his first comment, but most others haven't.
Do you look for edge cases? In other programming, you can send in zeros, too much data in a parameter, etc., but do you do that in SQL?
For example, do you look at aggregations with and without NULLs in there? Do you examine what happens for character comparisons if you have numerics or dates in the fields? Do you look for implicit conversions that could cause issues?
Those are more what I think of when I consider unit tests. My feeling is that most developers aren't overly concerned with more than getting back the correct result set with their test data.
November 10, 2009 at 12:41 pm
What to unit test itself is a long learning process, much of it determined by anticipating problems. They depend on existing state of the app, frequency of upgrades, hardware capacities, software fixes, expected loads, and so on.
Both correctness of SQL and efficiency of SQL in development code can suffer from inappropriate unit tests. Developing the right combination for a given app is the crux of the problem in my experience. Otherwise unit tests can give another false sense of comfort.
The easiest unit tests and the most common ones I see are for user defined functions or relational features, such as referential integrity or check constraints. Beyond that, for me, it was entirely case-by-case basis. In almost all cases, we added the most interesting unit tests only after the application was in production for some time. We still keep adding more tests as we learn more ways it could fail or discover new edge conditions.
November 10, 2009 at 1:36 pm
Yes. To me, unit testing is the number one important in software development life cycle. This process helps finding bugs before it goes out the box and to ensure all the requirements in the specification are met.
Reducing error rate means increasing customers confidence in use of our products.
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply