How do we test our SQL queries?

For most QA engineers, testing a SQL query involves writing a query and checking the output. Well this is definitely the first step but only a first step in checking the correctness of a query. Take an example:

Select * from customer where cust_num>10 and cust_name LIKE '%berdeen%' order by cust_num,cust_name;

Sounds like a simple query with a where clause.

This simple query involves several challenges which are also termed as phases to process which fetches data in fraction of seconds from tables having humongous data.

SQL processing actually involves several stages of parsing, optimization, row source generation, and execution of a SQL statement. There needs to be a check at all these stages and that’s how we do our SQL testing. Let’s look at each of these phases and what we test at each phase.

·        Parsing 

This state involve in syntax and semantic check for correctness of statement

Syntax validation must check each SQL statement for syntactic validity

Example :

               SELECT * FORM customer…;

              

ERROR at line 1:: FROM keyword not found where expected

Semantic validation checks and determines whether a statement is meaningful, for example, whether the objects and columns in the statement exist.

Example :

SELECT * FROM nonexistent_table;

SELECT * FROM nonexistent_table

              *

ERROR at line 1:: table or view does not exist

We need to have the tests to check the parser, not always the correct query. So the first step is done where we test the parser with accurate and inaccurate queries.

·        Optimization

The query optimizer (called simply the optimizer) is built-in database software that determines the most efficient method for a SQL statement to access requested data. The optimizer attempts to generate the best execution plan for a SQL statement. The best execution plan is defined as the plan with the lowest cost among all considered candidate plans. The cost computation accounts for factors of query execution such as I/O, CPU, and communication. Because the database has many internal statistics and tools at its disposal, the optimizer is usually in a better position than the user to determine the best method of statement execution. For this reason, all SQL statements use the optimizer and lot of algorithms are involved in this process.

Example:

Consider above sql statement where if we define index on the cust_num then optimizer will generate a query plan based on the index and the restrictions placed on the data fetching. Here in the above query we intend to sort the data on cust_num and cust_name .But optimizer with its intelligence takes the index column that is cust_num and fetch the data in the sorted order from the index column during fetching of data rather than sorting it again once the data is completely fetched .This will make query execute faster.

So always check how optimizer has optimized your queries. We are covering performance aspects of the query along with functionality by doing this, two birds for a shot.

·        Fetching the data

During execution, the SQL engine executes the query plan for fetching the data from the specified. In the above sql query sql execution consider the index column and get the data in sorted order based on cust_name which will indeed doesn’t need to sort the generated data as during the time of fetching the data it comes in sorted order.

Here we are finally testing for correctness of the results.

Any SQL query testing involves all these three functions and your testing is complete only when you test them all. If not, you are leaving large holes in your testing.