How to Unit Test SQL Changes
-
Hi,
I'm wondering if there is any special tool(s) which some of you guys are using to unit test SQL Server changes such as a change to stored procedure, UDF etc...
I know some folks are using NUnit for this purpose but apart from this tool is there any tool that you guys are using or recommend me to use?
Thanks,
Ignas Chilewa
MCP, MCDBA
Columbus, OH
-
Does it mean that people don't do unit testing or rather they decide to keep quite as they are not comfortable with this topic..
Someone come please, give me your experience on this.
Ignas
MCP, MCDBA
Columbus, OH
-
Big topic this one. The methods used may depend on what the objective of the testing is. Are you testing to check if the results are accurate and the query returns the results you expect or are you testing to see if the query is efficient under different senarios. Are you are looking to automate this so you can regression test and test your queries regularly or just a once off way manually test the query.
In order to do either, a good set of test data is important. A copy of your production db is best but if you cant have that then you will need a test db containing all the senarios you want to test. Before you run a test you need to be able to predict what the results should be so you either need the results from a previous query or you need to run a few manual queries so you know that your unit test are accurate.
We have web apps and use selenium scripts to test the front end which in turn tests the back end so we can check the results of the queries are accurate and return everything the front end needs. This is very useful as we can script out pretty much every senario and throw multiple requests at a query testing it for sorting and a variety of parameters. It takes a bit of time to get used to this and build the scripts but it's great when you can regression test your entire application in a couple of hours. Something that takes 2 weeks if you do it manually and you don't get half the coverage that the well build selenium scripts can cover. Selenium is open source.
If I'm working on an existing query, I will make a copy of it. When I'm ready to test, I will create a script to execute the old query several times outputting the results to a test file. I will do the same on the new query and then compare the two text files to ensure my new query returns the same results as the old query. I can test various senarios with various usernames using EXECUTE AS LOGIN which allows you to run a query as if you were a different user.
Beyond that you get into running queries and watching the execution plans and sql profiler to ensure they are efficient and don't use more resources that they need to.
-
Everyone's favorite answer....it depends. Roger has given you some very useful info here, but much of what you are after depends on what you are trying to accomplish. You say unit test, but that could mean 10 different things to 10 different people. Can you give us an examle of what kinds of scenarios you want to test?
-
No... I'm just shocked that someone who boasts the MCDBA in their signature line is asking such a question.
There is no magic to this... you need to know what the original code was supposed to do, test it before you change it with some basline tests that you write and verify, then, do the changes and see if the meet the tests, which you have also written, for the new requirements.
--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".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff maybe we should link this question to the certification debate
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Thank you Roger your detailed response.
It is true that I wanted to see how people automate their unit testing strategies.
I've been testing my changes be in a stored procedure, trigger, UDF etc using Scott's way.
But I've read some articles and know that there is so many ways to automate testing using NUnit, TSQLUNit, DBUnit, Visual Studio 2005 Team Edition for Database Professional, Selenum etc etc...
So the main objectives of my question was to hear from your experience which one is your favourite and if possible to know when it is very useful.
Remember the changes which am talking about relate to enhancement, defect fix etc... I want to test for accuracy of results as Load/performance testing is under control so far. And being able to do regression testing whenever possible.
Thanks,
Ignas
MCP, MCDBA
Columbus, OH
PS: I here to learn and open to learn as much as I can..
-
PS: I here to learn and open to learn as much as I can..
No doubt and good attitude! I'm sorry if my comment sounded as if it were directed at you, Ignas. It kinda was but not for the reason it may have sounded... I'm just a bit ticked at Microsoft for the, in my opinion, poor training they or their constituents do...
--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".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Heh... doesn't matter, Rudy... folks can always buy hardware to make up for the lack of knowledge
--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".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
... ahh yes ... the old 'tune it up with money' trick ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply