On aspect of SQL Server testing, it is totally different when compared to other type of testing, we can have following as major portions in SQL Server testing:
a) Code compliance testing.
b) Performance Testing.
c) Integration Testing.
Under code compliance testing, the code will be checked in compliance with requirements; Developer can develop the code in different ways for a requirement
eg: A typical developer will divide the code into different functions/procedure of reuse and some redundant developer will put the functionality in the same code, as a tester you cannot say that, this is wrong but you can recommend which is good.
When a piece of code arrives to a tester, he/ she have to look into the following areas in SQL Server Code Compliance Testing:
a) Parameter passage.
b) Code blocking (Begin / End).
c) Conditions / loops.
d) Exception handling.
e) Code commenting / alignment.
f) Return values.
Parameter passage: These plays very important, with data types and length, parameter movement (Input /output etc).
Code Blocking: Fast coders will leave the begin and end for single statements and when they change code/modify later they will forget to put the begin and end statement and piece of code will execute even though condition fails, as a tester you need to make sure that which should be in block and which should be out of block.
Conditions /loops: simple condition is easy to understand, but long condition will put confuse to developer, as an example “Not of Not” and the meaning of loop will change with “do while “and “while” usage, As a tester you need to make sure that they are properly conditioned / looped. We can put this under “Branch Testing”
Exception handling: This is very important area which comes under “Range Testing”, everything has to be reported back to the caller, either it is requested result or there is no result. Out bound and inbound value, failing of transaction etc are highlight points under exception handling.
Code commenting / alignment: This is low priority task compared to others but plays very important role in future versions and maintenance of the code, there should be proper comment for each items which is confusing or developer thinks that “this should have proper explanation”, alignment makes code readable.
Return values: For some of the code, return values are mandatory and the return values should be in the context of requirement, when there is result and when there is no result, here boundary value checking plays major role, and this will come under boundary and range value checking.
Performance Testing:
This is validating DBA’s (Database Administrator) role, performance of piece of code plays major role in network based projects, after code is in-compliance with the requirement.
Please do remember that if the code is not in-compliance with the requirements, then there is no scope of performance.
Code performance has to be checked in following area(s):
a) SQL Queries.
b) Indexing.
c) Disabling / enabling of items – bulk insertions.
d) Clustering.
e) Database designing
f) Data types.
g) Locking / unlocking.
SQL Queries: This is the closure area where you will be looking for optimization of code; Queries are source of interacting data on SQL Server, SQL Queries can be written in different forms and some of the queries will be differentiated only with “Execution Plan” to identify the optimized one. Most of the time tester has to look into “Join” conditions and another well known area “IN” condition, where developer can use “Exist” to make code perform better.
Indexing: This is area where fastness /performance could be increased for the requested queries. Database designer will identify where we may require indexing (clustered / non-clustered) and as a tester you need to identify whether all the indexes are properly configured or working with the query list.
Bulk Transactions (Disabling / Enabling of items): when there is bulk data transaction we must make sure that certain elements / objects are disabled for example, Triggers - when millions of records have to be transacted, it will take hell of time if trigger are enabled, make sure that required objects are enabled / disabled.
Clustering: SQL Server clustering can provide fault tolerance for many aspects of a SQL Server, such as hardware, network, operating system, and application failure, make sure that it will not affect the performance and database is clustered on required portions.
Database designing: This is the basic area to look for optimization; too much normalization will affect the performance. As a tester you have to identify which areas (reports, query results etc), are required for normalization and other areas of data manipulation (insertion/ update etc) are done with de-normalization.
Data Types: The lazy designers/developers always use the maximum data type’s usage, Ex: when it requires of true / false, they will keep constants as “VALID” or “INVALID”, and use varchar(8), these datatype will consume more memory, which is not used by the code but still occupying, which will intern increasing the paging.
Locking / unlocking: Some of the code will lock certain portions and until it is released other operations are not performed, as a tester you need to make sure that the correct portion is locked instead of global locking, eg: when row level locking is there you may not require table level locking.
Integration Testing: In order to get certain portions of data, we may require integration of different servers and following are major areas to look on aspect of integration:
a) Calling of External Stored procedure.
b) Usage of multiple Servers.
External Stored procedure is the complex item to test, which can call any windows components, discussion on External Stored procedure is vast area and out of scope of this article. Usage of multiple servers (Linked server) validating should be done on exception throwing cases like when one of the server is down etc.
Other focused areas: As a tester for SQL Server you have to know the general items like
a) Writing test plan.
b) Writing test cases.
c) Usage of bug track system.
d) Differentiating bugs with triage items.
e) Documenting required items.
