Defining Database Objects

Oracle database objects (tables, views, and indexes) can be migrated to Microsoft SQL Server easily because each RDBMS closely follows the SQL-92 standard that regards object definitions. Converting Oracle SQL table, index, and view definitions to SQL Server table, index, and view definitions requires relatively simple syntax changes. The table highlights some differences in database objects between Oracle and Microsoft SQL Server.

Category Microsoft SQL Server Oracle
Number of columns 1024 254
Row size 8060 bytes, plus 16 bytes to point to each text or image column Unlimited (only one long or long raw allowed per row)
Maximum number of rows Unlimited Unlimited
BLOB type storage 16-byte pointer stored with row. Data stored on other data pages. One long or long raw per table. Must be at end of row. Data stored on same block(s) with row.
Clustered table indexes One per table One per table (index-organized tables)
Nonclustered table indexes 249 per table Unlimited
Maximum number of indexed columns in single index 16 16
Maximum length of column values within of an index 900 bytes ˝ block
Table naming convention [[[server.]database.]owner.]
table_name		 [schema.]table_name
View naming convention [[[server.]database.]owner.]
table_name		 [schema.]table_name
Index naming convention [[[server.]database.]owner.]
table_name		 [schema.]table_name

It is assumed that you are starting with an Oracle SQL script or program that is used to create your database objects. Simply copy this script or program and make the following modifications. Each change is discussed throughout the rest of this section. The examples have been taken from the sample application scripts Oratable.sql and Sstable.sql:

  1. Ensure database object identifiers comply to Microsoft SQL Server naming conventions. You may need to change only the names of indexes.
  2. Modify the data storage parameters to work with SQL Server. If you are using RAID, no storage parameters are required.
  3. Modify Oracle constraint definitions to work in SQL Server. Create triggers to support the foreign key DELETE CASCADE statement if necessary. If tables cross databases, use triggers to enforce foreign key relationships.
  4. Modify the CREATE INDEX statements to take advantage of clustered indexes.
  5. Use Data Transformation Services to create new CREATE TABLE statements. Review the statements, taking note of how Oracle data types are mapped to SQL Server data types.
  6. Remove any CREATE SEQUENCE statements. Replace the use of sequences with identity columns in CREATE TABLE or ALTER TABLE statements.
  7. Modify CREATE VIEW statements if necessary.
  8. Remove any reference to synonyms.
  9. Evaluate the use of Microsoft SQL Server temporary tables and their usefulness in your application.
  10. Change any Oracle CREATE TABLE…AS SELECT commands to SQL Server SELECT…INTO statements.
  11. Evaluate the potential use of user-defined rules, data types, and defaults.