Testing FAQ
Q: Why test my databases?
A: How about . . .
And that's just three of the many benefits testing has to offer.
Finding bugs (aka "faults") is the most obvious reason you test a database. Fewer bugs means increased reliability--more intuitive handling, quicker performance, better security and so on.
Testing also reduces support costs. Robust designs and well-written SQL scripts mean fewer headaches--for you and your customer.
Less obvious, perhaps, bug counts are key tracking metrics that can help you meet a tight release schedule.
Q: Is database testing different?
A: Yes and no.
With database testing, your test unit might be an SQL query, a stored procedure, a data table or a security setting. Compare that with a Windows form in the case of UI testing or a Javascript in the case of web testing.
Another unique facet of testing databases is the frequent use of SQL to write test drivers and automate tests. You don't have to use SQL but if you like working "close to the metal," as they say, then SQL will likely be your language of choice.
Besides these differences, database testing follows the same steps and uses most of the same techniques as other forms of software testing:
You develop test plans.
You design test cases--black box, white-box, and gray-box.
You write test scripts (for manual tests) and create test drivers (for automated tests).
You run tests.
You document your test results.
You log and track the bugs you find.
Q: What kinds of tests work best for databases?
A: As in all software testing, there are many different kinds of database tests.
In general, the test you run depends upon the kinds of "bugs" (aka "faults") you're trying to find.
| Test type | Focus | Bug examples |
| Unit tests | Individual code modules (like triggers, stored procedures, and SQL jobs) or schema objects like data tables. | Missing columns, security holes like weak passwords, and performance bugs like poorly designed sql queries. |
| Integration tests | Interfaces connecting two or more objects--two procedures, a table and a stored procedure, etc. | Missing (or extra) input or output parameters, incorrect column names or data types--e.g., char as opposed to int. |
| System tests | Major subsystems of a database like reporting or database quality targets like security and performance. | Missing reports, system crashes, sluggish performance, security violations. |
Q: Who should be doing our testing?
A: That depends on several factors.
1. The size of your organization
Big organizations can hire QA specialsts who spend all their time testing. But if you work for yourself or a small organization, testing is probably a part-time job for you.
2. The kind of testing you do
Unit tests require access to the database object you're testing. Many testers do not have direct access to the data tables and other schema objects they test. (In some organizations, even the developers are prohibited direct access.) In those cases, only someone with the required access will be able to unit-test individual objects.
3. Your skills and those of your teammates
Some tests can be run by anyone with the required access. In fact, QA engineers frequently write manual test scripts so even inexperienced technicians can run a test correctly.
But other testing activities require highly specialized skills. Designing tests is a good example. While almost anyone can be shown how to run a simple test, designing the same test typically requires far more technical training and experience.
Likewise, certain types of tests require more skill than others. Almost anyone can functionally test whether a report date renders correctly. By contrast, a good security tester must thoroughly understand the technical architecture of many different DBMS systems.
Q: How do I test a database's "functionality"?
A: By knowing the business requirements it must meet.
Before running a test, sit back and think about why you've chosen this particular test. Does it correctly cover a business requirement? (Tests too can be "buggy"!) What kinds of bugs do you hope to find? How should you implement the test? Manually or through automation? Who will be running the test? Have you laid out the steps they should take and clearly defined the expected results?
The purpose of professional testing is to detect bugs. So the first step in all good testing is designing tests with a good chance of finding problems in your database design or SQL code. Sounds perverse, doesn't it? At least till you get the hang of it. From there, the specifics of testing databases depend upon the type of test you're running.
Functional tests are the most familiar. They test the "reliability" of your database by looking for design flaws and implementation mistakes that violate some business requirement. Common examples are tables with missing columns, queries with incorrect table joins, and procedures with the wrong parameters.
Programmers know these kinds of bugs as "logic errors" because you can't tell whether a database is behaving "logically" without first checking its requirements.