Updated: Dec 2, 2020
I prepared this awesome checklist that you can use when testing any software that contains a database, just take what you need and add your ideas if necessary.
Why using a checklist for testing?
In my opinion, there are a few major benefits that you achieve when using checklists:
Checklists can ensure that all the client requirements will be covered in the testing process.
Checklists can ensure that the software is tested with the needed coverage.
Checklists can reduce the tester “Forgetting” errors.
Checklists can help to ensure testing accuracy.
Checklists can help to see the testing coverage.
Checklists can help to see the testing progress.
Checklists can increase the coordination between different teams involved in the testing process.
Checklist for Database testing
Database testing is used to test the main aspects of the integration between our tested software and the chosen database platform (SQL, Oracle, etc.). The main aspects that we want to validate are:
Synchronization between the database and the values displayed in our client/web.
Query results, views, stored procedures, indexers Etc.
Data manipulation (Update, Delete, insert Etc.).
I selected a few criteria for tests, every criteria contain few examples that may help you to perform better database testing.
Database system-level tests
Validate the DB behavior of any case of service failures (recovery, error handling, etc.).
Validate that all indexes are created when it can increase the system performance.
Validate those appropriate events that are created ad sent to the EventVwr/trace log.
Validate that DB tables are created with informative and reasonable names.
Try to work when the storage is ‘0’ and the database is running.
Perform your tests on different versions (SQL 2012, 2016, etc.).
Validate the software security model (User roles, permissions, etc.).
Validate the connection strings against SQL/Win authentications.
Validate data migrations (Different databases, clusters, etc.).
Validate the behavior of the system against SQL injections.
Validate data to DB when the server is loaded.
Try to work when the database server is down.
Try to work with a different instance.
Validate restore and backup plans.
Data integrity tests
Check that all columns are set with the relevant data type (bigint, int, string, etc.)
Check that all data is logically organized in the relevant DB tables.
Check that each data item is located under the relevant column.
Is there any irrelevant data in the software dedicated tables?
Check that each table contains the relevant data.
Try to insert invalid database values.
Verify the data encryption (if any).
Data field tests
Validate that the “Allow Null” condition is not allowed in a place that results in a software failure.
Validate that all tables are created with logical structure (Primary, foreign keys.)
Validate that the “Allow Null” condition is set when you need to allow it.
Validate that mandatory fields are created, this issue is very important when you work with multiple tables that depend on each other.
Validate that the data affected by the procedure is changed as expected.
Validate that all procedures are triggered when they are supposed to run.
Validate that all the conditions receive appropriate date inputs.
Validate that all procedures are created with the relevant code.
Is there an appropriate error handling for a failed procedure?
Validate that all the loops receive appropriate date inputs.
Validate the procedure's parameters (types, names, etc.).
Test the SP while executing the code manually.
Validate important code with SQL profiler.
Validate that all procedures names
Run tests with missing parameters.
Database and software integration (Client, web Etc.)
Validate that the user data is saved when the user “Apply” or “Submit” the changes.
Try to insert “NULL” values on fields that don’t suppose to receive it.
Validate that the user receives the current result when pulling data.
Validate that transaction the data type boundaries (Min and Max).
Validate that empty spaces are not committed to the database.
Validate that the values are displayed based on the database data.
Try to insert UNICODE on Unicode character strings.
Try to insert values that exceed the field boundaries.
Validate transactions with negative data values.
Insert invalid date format on Date and time fields.
Validate that the data integrity is not affected when the “Apply” or “Submit transactions are failing during the process.
Validate that the “Roll Back” option is available when the DB transaction is failed in the middle.