DATABASE MIGRATION TESTING

Many often you are required to perform database migration testing. The task isn’t easy but if you follow some instructions one by one then the task will be easy for you. In my professional experience I’ve executed three types of database migration testing. These are MySQL to SQL Express, pgSQL to SQL Server and SQL Server to pgSQL.

When you will execute the task then you will have to face many db specific challenges, but here I’m presenting only the cases that are very to common to every database migration task.

Following cases should be considered in database migration task

  1. Unless specified, the migrated database should have equal number of tables, views, stored procedures, user defined data types,… and equal number of columns for each of the mentioned objects.
  2. Unless specified, the migrated database should have same name and same letter case for the names of tables, views, stored procedures, user defined data types and columns.
  3. Unless specified, the column/parameter order in tables, views, stored procedures and user defined data types should be same as original database.
  4. The type and size including precision of every column should be same as original database.
  5. If migrated database doesn’t have exact type as original database then it should use the type which is most similar to original database. Special consideration should be taken for numeric, bit, binary, image and date/time [with/without time zone] related data types.
  6. The constraints Primary Key, Foreign Key, Unique Key, Default, Check, NULL and NOT NULL on column(s) of table(s) in migrated database should be same as original database.
  7. Unless specified, the constraint names on every table should be same as original database.
  8. The actions of CASECADE DELETE and CASCADE UPDATE of foreign key(s) should be same as original database.
  9. Unless there has any limitation in migrated database, then the migrated data should be same as the data of original database.

How to Describe Table in MS SQL Server

Unlike Oracle or MySQL there is no DESCRIBE or DESC command to describe a table or object in MS SQL Server. But to describe a table or object MS SQL Server provides a very useful command or built-in stored procedure sp_help.  One can easily describe an object using this command.  You can use this command in the following ways:

> sp_help ‘your_object_name’ or
>
sp_help your_oject_name
The first method is more useful because you can specify db/schema name here. Here is the command along with output:
sp_help ‘dbo.Sales’
Output:
Output of sp_help command

More info  is available here

Usages of ROW_NUMBER() in MSSQL SERVER

A very important feature of SQL Server is the usages of ROW_NUMBER() function. It gives sequential row number[starts from 1] over duplicate touple (a set of column[s]) and the order you set. The sequence of using ROW_NUMBER() in your SELECT SQL is:

ROW_NUMBER ( ) OVER ( [ partition_by_clause ] order_by_clause )

The PARTITION BY clause determines the duplicate touples on which you
want the sequential row number and ORDER BY clause determines the
criteria to set row number.
Lets see how it works and how it helps us….

In a simple way consider you have a table SALES like

CustomerID ProductID PurchasedDate
C000001 P000001 2010-02-01
C000002 P000001 2010-02-01
C000002 P000001 2010-02-02
C000001 P000001 2010-02-02
C000001
P000002 2010-02-03
C000001 P000001 2010-02-03
C000002 P000002 2010-02-04
C000002 P000001 2010-02-04
C000001 P000001 2010-02-04

Now if you execute the following SQL:

SELECT CustomerID, ProductID, PurchasedDate, ROW_NUMBER() OVER (

PARTITION BY CustomerID, ProductID ORDER BY PurchasedDate DESC) AS RowNo

FROM SALES;

Then the output will be

CustomerID ProductID PurchasedDate RowNo
C000001 P000001 2010-02-04 1
C000001 P000001 2010-02-03 2
C000001 P000001 2010-02-02 3
C000001 P000001 2010-02-01 4
C000001 P000002 2010-02-03 1
C000002 P000001 2010-02-04 1
C000002 P000001 2010-02-02 2
C000002 P000001 2010-02-01 3
C000002 P000002 2010-02-04 1

Explanation of the output:
In the SALES table there has total four distinct CustomerID-ProductID combination. In the SELECT statement this distinct combination is gained by partitioning the column with CustomerID and ProductID columns. Then the partitioned columns are given row number based on PurchasedDate column. In this case the order is DESC.

So the combination C000001-P000001 has 4 rows with dates ‘2010-02-01’ to ‘2010-02-04’. So the ROW_NUMBER()[here the alias RowNo] of row C000001-P000001-2010-02-04 is 1 and then C000001-P000001-2010-02-01 is 4. Similarly the combination C000002-P000001 has three rows with dates ‘2010-02-01’, ‘2010-02-02’ and ‘2010-02-04’. So RowNo for the combination C000002-P000001-2010-02-04 is 1, C000002-P000001-2010-02-02 is 2 and C000002-P000001-2010-02-01 is 3. Here if you use the ASC as ORDER BY then the RowNo would be reverse than it is shown.

Now if you carefully see the second table then imagine how easy it is for you to select distinct CustomerID-ProductID based on most or least or Nth recent sales.

For example you now want to know the 3rd recent sales date of every CustomerID-ProductID combination. So your SQL will look like:

SELECT temp.CustomerID, temp.ProductID, temp.PurchasedDate
FROM (
SELECT CustomerID, ProductID, PurchasedDate, ROW_NUMBER() OVER(
PARTITION BY CustomerID, ProductID ORDER BY PurchasedDate ASC) AS RowNO

FROM SALES
) AS temp
WHERE temp.RowNo = 3;
The output is:

CustomerID ProductID PurchasedDate
C000001 P000001 2010-02-03
C000002 P000001 2010-02-04

Isn’t it very useful feature?