April 7, 2008 at 8:47 am
Hello, all. Recently I've had reason to contemplate the whole concept of unit testing and question whether I have a good grasp of it. I will run tests against code I have updated or developed looking for errors, appropriate results, etc. I'll be satisfied with the results and send it along to our QA group. They will run tests and encounter errors using tests very similar to the ones I ran, yet they find bugs I didn't. :sick:
How do you unit test? What tricks to you use to make sure you are not testing only those situations you know will work? I obviously have an understanding gap of some sort. I've asked our QA group to help me out by showing me how they approach it but I wanted to get the perspective of other SQL Developers.
Any insights you can provide will be appreciated.
Don
April 7, 2008 at 9:22 am
Most people just insert a row of data into the database, run a select query or a delete query and declare it successful if it finishes without error. For myself, that's very inadequate. First, for database testing, you need to get a realistic set of data. It doesn't need to mirror the 300gb production system, but it should be a reasonable set of rows distributed in a way that matches production. Once you've got that in place, I recommend having a positive and negative test for each proc. Meaning, one that works as expected and one that produces an error. Finally, automate this stuff as much as you can. I use Visual Studio Team Edition for Database Professionals which has data load and unit test mechanism built in. However you can go to freeware or inexpensive alternatives. Red Gate just released a great data load utility. TSQL Unit is a freeware unit testing automation tool available over on SourceForge[/url]. There are other alternatives as well.
"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
April 7, 2008 at 10:37 am
Thanks, Grant, good points. I use copies of existing production databases to perform my tests but I obviously don't cover all the bases. I have Data Dude and I'm learning how to do unit tests with it.
April 7, 2008 at 10:46 am
I did write a couple of articles on unit testing here at SQL Server Central. Two of them cover DBPro and one covers TSQLUnit. Do a search. There might be something helpful there.
"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
April 7, 2008 at 10:59 am
Cool, thanks!
April 7, 2008 at 11:26 am
The key to unit testing is to run some tests that should work, and then run some that probably shouldn't work.
For example, insert duplicate key values into a table by running an insert proc twice, if you can. Use Raiserror to see what happens if a proc fails partway through. Try selecting a state abbreviation that doesn't exist. Try SQL injection on your string-type input parameters. Run the proc from multiple connections to see if you can get it to deadlock or time out. Insert into multiple tables with foreign keys, delete some of it, then try your update/delete procs and make sure they can handle missing data. Heck, if it really matters, try unplugging a test box while a query is running, and see if the database can recover from that or if you end up with committed transactions that really shouldn't be. (Don't try that on a server, of course. If you toast the hardware or have to re-install the OS, or whatever, just understand that these things are important tests.)
The point is, try to break it.
Unit test software is good, but it also (in my opinion) takes a little streak of viciousness in the testing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply