Software database testing stored procedures

Software database testing stored procedures

I am a testing engineer. I need to know about database testing i.e., how to test functions, stored procedures and how to test the performance of stored procedures.

    Requires Free Membership to View

    Login

    When you register, you'll receive targeted emails designed to keep you informed of the most relevant information on Agile development, application security, testing & QA, software requirements, and more.

    Hannah Smalltree, Editorial Director

    By submitting your registration information to SearchSoftwareQuality.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSoftwareQuality.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Database testing seems to be a loose term used to refer to any backend or data-related testing when in fact there are many different types of backend data testing-- such as data migrations and data integrity.

The two practices I have participated in and have seen used for testing stored procedures (SP) are:

  1. Conduct code reviews of the stored procedures-- SPs are in fact as powerful and manipulative as code, so both code reviews and version control are practices to consider implementing.
  2. Build data sets to both test the stored procedures as well as test the performance of a stored procedure. Let me explain more specifically:

To test a stored procedure functionally, you can build one or more sets of data that test both a positive as well as negative aspect of the procedure. A positive test to prove the SP works against the specific data set and handles the data as expected as well as a negative set of data-- it might be data that falls outside of the SP to ensure that the SP does not impact data it is not expected to. Also you can challenge the SP-- creating data sets that push boundary condition or fall on the edge of a business rule to see how gracefully the SP handles those types of challenges.

To test the performance of a SP, you could follow a similar process building larger data sets. You could also isolate sections of the SPs and run those separately to see how specific SQL statements execute in terms of performance.

This was first published in November 2010

Back to top