In our last blog we spoke about the advantages of data integration at an enterprise level. As mentioned data integration can be tricky, we faced many issues on a data integration projects for few of our large clients. Sometimes issues which might look minor becomes a major challenge. In this blog we are going to share with you how we faced an issue in migration of diacritical (á) Character and how we resolved the same.
Pic 1 : Representation of Diacritical Character in different Character set.
Most of us have faced this issue several times in any data base migration. Below are some excerpt on how we faced this issue and what we did to resolve the same.
- The Issue was with datatype varchar used in MS-SQL which is limited to 8 bit code. This should be replaced with nvarchar which can accept any uni-code characters.
- For all the tables having varchar as data type we need to change to nvarchar but before that we need to drop the index (which is present for all the tables) then change the datatype and create the index again.
The steps which should be followed are:
- Rename the Existing table
- Creating the original table with NVARCHAR in place of VARCHAR
- Create the COLUMN STORE INDEX
- Copy the Data from the COPY table to the Original Table.
- Then we will have to do the testing for all the tables involved
Note: Column store index of affected tables should be dropped before altering and restored after alteration.
Pic 2: Movement of Diacritical character between Oracle and SQL Server
Stay tuned as we will bring forth many topics on issues we have faced relating to data integration in medium to large enterprise and how we resolved them.