Migrating from Oracle to SQL Server

Step IV Taking Care of the Collation Problem

A default SQL Server installation is case insensitive, which means that SQL Server will not differentiate between UPPER (“MICROSOFT”) and lower (“MicroSoft”) case characters.

SQL Server 2000 supports number of collations. fn_helpcollations() returns a list of all the collations supported by Microsoft SQL Server 2000. You must change the collation of the server while installing, if you want case sensitiveness. You can specify collation at the database level as well as at column level. That means, you can have a default SQL Server installation with case insensitive collation, but a specific database or a set of columns can have case sensitive collation.

In earlier versions of SQL Server (6.5 and 7.0) you can select a 'sort order', while installing SQL Server. Sort order defines how the characters supported by the code page are compared and evaluated. The default sort order is 'case insensitive'. If you want SQL Server to perform case sensitive searches, you need to select binary sort order while installing SQL Server (6.5 or 7.0). In these versions (SQL Server 6.5 and 7.0), sort order is defined at the server level, and cannot be manipulated at the database level. If you want to change the sort order of an existing SQL Server 6.5 or 7.0 instance, you must rebuild the master database using rebuildm utility.

Now, a sort order is called 'collation' in SQL Server 2000.

I think column level collation makes sense than database level collation. I am using ‘SQL_Latin1_General_CP1_CS_AS’ collation in this article.

Step V Handling the Oracle Sequences

It is very easy to migrate oracle sequences to SQL Server, just by setting the identity property of the columns. But I have created a permanent table “sequence_tab” with sequence name and sequence value as columns, and a procedure to return next value of the sequence.

Step VI Transfer Data from Oracle Using a Linked Server

There are number of methods/tools to transfer the data from Oracle to SQL Server. However, you can use Microsoft’s in built DTS (Data transformation service) to transfer data. I have used linked server to transfer the data from Oracle to SQL Server.

Step VII Creating the Constraints

Migrating primary, unique and foreign key constraints is very straightforward. Both Oracle and Microsoft SQL Server enforce uniqueness by automatically creating unique indexes whenever a PRIMARY KEY or UNIQUE constraint is defined. Additionally, primary key columns are automatically defined as NOT NULL. Only one primary key is allowed per table.

A SQL Server clustered index is created by default for a primary key, though a nonclustered index can be requested. The alternate keys can be defined with a UNIQUE constraint. Multiple UNIQUE constraints can be defined on any table. UNIQUE constraint columns are nullable. In SQL Server, a nonclustered index is created by default, unless otherwise specified.

When migrating your application, it is important to note that SQL Server allows only one row to contain the value NULL for the complete unique key (single or multiple column index), and Oracle allows any number of rows to contain the value NULL for the complete unique key.

Oracle allows creating a cascading foreign key relationship with more than one path to a table.

IF try to create the same FK constraint in SQL Server, an error 1785 with error message “Introducing FOREIGN KEY constraint '%.*ls' on table '%.*ls' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints”, is generated and the constraint will not be created.

In order to avoid this error, do not try to create a cascading foreign key relationship with more than one path to a table.

I hope this article helps for those are who are in the process of migration of Oracle database to SQL Server.