9

I am testing a financial web-based application having the back-end built on SQL Server. As far as DB testing is concerned, I test simply whether any value/input entered in the UI application is saved successfully in the DB. I sometimes test stored procedures and functions with dummy values and then validate the output.

I feel that I am not doing DB testing as good as I should do. What are some best-practices/methods which can be used by a test engineer to do better database testing. What other aspects/factors should one test so that quality of the application can be assessed more efficiently?

Improve this question
6

6 Answers 6

Reset to default
3

Reposting from my blog notes

Database Testing involves the following Activities for a OLTP System

  • Verify Table Schema, Column names as per Design Document
  • Verify Column Length and DataType
  • Verify Unicode Support (Storing Chinese/Japanese Characters) - NVARCHAR Datatype
  • Verify Indexes on Table (Clustered, Non-Clustered), Triggers for Auditing
  • Verify Primary Key and Foreign Key Constraints defined on the Tables as in Design Document
  • Verify Default Values of Columns, NOT NULLABLE columns, Constraints defined on Columns

Database Testing (Performance Tuning & Functional Testing)

  • This is more towards White-box testing, Verifying the SQL Statements, Queries in the Procedure from a performance perspective. The objective here is to ensure
  • Verify Access Methods (Seeks over SCANS)
  • Verify JOINs used and ensure its Optimal (Nested vs Merge Vs Hash)
  • Very Logging and Auditing Enabled for Error Handling
  • Verify Errors are logged and they contain Error Message, Supporting Details of Error
  • Verify Coding Guidelines (Set based Operation vs Cursors, Try-Catch Block).
  • Verify Isolation levels used. Recommend used of Read-Committed Snapshot isolation level to avoid potential blocking issues
  • Verify Rollback is handled in Transactions (Eg. Order Creation is failed due to PK Violation, Entire Transaction should be rolled back, All tables associated with the transaction need to be rolled back)
  • Use Profiler, Activity Monitors, DMV queries to find top IO, CPU consuming queries, You can find more details in Performance Tuning Section
Improve this answer
0
2

Look into:

Improve this answer
0

Database testing may include validating the Schema of the database, trigger testing, procedure testing, testing the ACID properties and a lot many things. You may include the security and performance aspect as well.

For details you can refer these websites:
http://www.softwaretestinghelp.com/database-testing-process/
http://www.guru99.com/data-testing.html

Improve this answer
0

You can do many type of testing for/with database , Some my suggestions are given below :

  1. Data Validation and Verification
  2. Load testing to check which query taking long time to get data and need to optimize
  3. Table structure and fields , In some companies they don't have DB specialist for database stuff so developers design database and in come cases they add unnecessary fields and tables which reduce data retrieval performance. So as tester you can suggest to remove things which can be manage from same table instead of creating new.
  4. Password encryption
  5. Active/Deactivate flags

After above list rest is depends on application.

Improve this answer
0

Database testing is about 3 key aspects: data flow, business logic and performance. The first two are covered by functional testing, and the latter - by performance testing.

Best way to ensure efficient functional testing is to use both white-box and black-box testing. Performing either of the two alone is not enough. While black-box testing provides a clear idea of the user experience, white-box testing helps ensure full test coverage and swiftly detect and fix some bugs related to business logic. You should also remember about regression testing, an important part of the functional testing effort. Even if your team follows agile approach, there are plenty of techniques to optimize regression testing in agile without delays and additional costs.

As for performance testing, the important point here is to make the two choices:

  1. Manual or automated testing. This fully depends on the project. For a CRM system for a 10 employee business, you may well use manual performance testing. This will certainly not work for a web application catering to thousands or millions of users.
  2. If you choose automation, you should be aware of its high costs. Even open-source tools offer extended (and often very necessary) functionality at a certain price.
Improve this answer
0

At this point I suppose you have already received the most of the info you need, anyhow I'll try to provide some input based on your answer to my comment: data integrity and data validation.

Testing data integrity: widely speaking it includes even the data validation testing but in a simplified check it consists of verifying that insert, update and delete operations preserve the data into your repository. Your check will verify that the data access layer of your application maintains the referential integrity preserving your data from corruption.

Testing data validation: your check will verify that the data will be saved and retrieved respecting the correct data type and the expected range.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.