Chapter 6 - Developing: Databases - Migrating Schemas
On This Page
.gif){kind=icon}
Introduction and Goals
Scripting Migrated Schema Objects
Migrate the Schema
Migrate the Schema Objects
Introduction and Goals
The next task is to create additional databases to house the application schema objects and the objects that are migrated.
Apart from the architectural differences mentioned in Chapter 5, there are also differences in data models between OLTP and DSS systems. OLTP systems have more complex relations, with constraints used to enforce business rules. DSS systems have simpler and far fewer relationships between tables. Despite the differences between systems and schema modeling techniques (such as normalized, star, and snowflake), the implementation of the physical schema uses the same set of objects, such as tables, indexes, and views. As a result, migration of the schemas and data is the same for all these types. As in Chapter 5, the examples used in this chapter focus on OLTP systems, but the guidance is applicable to DSS systems, as well.
A Microsoft® SQL Server™ database has the characteristics of an Oracle schema because objects can be created inside the database. In Oracle, the schema and the storage (tablespaces) have independent identities — objects of a schema can be created in different tablespaces, and a single tablespace can accommodate objects from multiple schemas. In this context, SQL Server databases are similar to Oracle tablespaces — an owner can create objects in different databases, and a database can contain objects from different owners.
In spite of this fact, the database provides a higher degree of separation of the application data and security than is provided by schemas. SQL Server has been designed for the isolation of administrative duties at the database level. The system (catalog and roles) has been divided, with centralized functions that have instance-wide authority under the master database and database-specific functions under the individual databases.
Creating a SQL Server database for each Oracle schema is the ideal choice for the logical separation of objects based on the business function (application) that is provided by the Oracle schemas. Subsequently, the schema's objects can be created under the database.
Tools are available from Microsoft that can assist in this step of the database migration. The Microsoft SQL Server Migration Assistant (SSMA) provides the Schema and Data Migrator, which specializes in the migration of the Oracle schema to SQL Server. The SSMA also provides the SQL Converter tool, which converts SQL code found in Oracle
objects (such as stored procedures) to their T-SQL equivalents. The SSMA tool suite can be downloaded from https://www.microsoft.com/sql/migration. The beta version of SSMA is available as of the date of publishing this solution. Version 1.0 of SSMA is slated to be available in June 2005.
Most tools that are employed in the administration of Oracle databases are capable of extracting object definitions. Toad and SQL Navigator from Quest, PL/SQL Developer from Allround Automations, Unicenter SQL-Station from Computer Associates, Rapid SQL from Embarcedero, and Oracle Enterprise Manager from Oracle are examples of such tools.
Modeling tools such as AllFusion (Erwin) from Computer Associates and ER/Studio from Embarcadero can also be employed to reverse engineer the Oracle database.
The goal of this chapter is to show how an Oracle schema and its objects should be migrated to SQL Server. Scripts should be used for performing most of the tasks in this step, because scripts make the migration easier to execute compared to the use of GUI tools.
Scripting Migrated Schema Objects
The development process is built on the requirement that when the development is complete, the code will be in place for unit testing and implementation. These scripts will allow recreation of the objects in a reliable manner when creating the database in the Stabilizing Phase and the Deploying Phase. There are some basic rules that should be followed when scripting the database for promotion to a staging environment for unit testing:
Script everything
Provide support documentation
Protect the scripts
Each of these basic rules is discussed under the following headings.
Script Everything
Script everything associated with the database implementation. The Generate SQL Script feature of SQL Server Enterprise Manager can be used to script each database object in the migrated database. After the scripts are created, conduct a validation process to verify that the number of objects scripted is the same as the number of objects that exist in the development database. This process is very important because it helps to ensure that the database being promoted to the staging environment is identical to the database in the development environment.
Provide Support Documentation
Provide support documentation for each script. Comment the code to describe what is happening in the script and include any observations gathered when it was last executed, such as how long execution took and how many rows were affected.
Protect the Scripts
Protecting the scripts is very important for the database implementation. Treat the scripts like production objects at this point. To ensure that the appropriate scripts are used when the database is promoted between development, staging, and production environments, keep the scripts in a secured directory or under version control. Versioning is very important because an Oracle to SQL Server migration is not a mere mapping of objects from one database to another. The differences between Oracle and SQL Server will require that some of the SQL statements be rewritten for the new environment. These changes in SQL will also prompt changes to the database objects and their design. These changes will be driven by the application development (migration) process and not from within the database migration process described here. Only the development and deployment teams should have access to the scripts.
Migrate the Schema
This section discusses the necessary steps in migrating the schema owner and setting up the storage in preparation for migrating the schema objects and its data.
The high-level steps for migrating the storage architecture of a schema are:
Map the storage architecture.
Create a database for the schema.
Create filegroups for the tablespaces.
Add datafiles to filegroups.
Each of these four high-level steps is discussed in detail under the following subheadings.
Map the Storage Architecture
The first step in migrating an Oracle schema is to understand the storage architecture and how these architectural components will map to their equivalents in SQL Server. Information on the various characteristics of the tablespaces and datafiles in which the Oracle schema is stored has to be gathered.
There are several tools, both from Oracle as well as third parties, which are used in administering Oracle databases. Any of these tools can be employed to gather information on the tablespaces and datafiles. In the example migration that is provided at the end of this section, methods for capturing the requisite data purely using SQL are provided.
Figures 6.1 and 6.2 provide a view of how the storage objects will align in Oracle and SQL Server.
.gif)
Figure 6.1 Oracle schema illustrated
.gif)
Figure 6.2 SQL Server schema equivalent
Start by compiling a list of tablespaces that contain objects belonging to the schema and gather the following related information:
Tablespace name.
Status. The status of an Oracle tablespace may be either ONLINE, OFFLINE, or READ ONLY. If a tablespace is OFFLINE, then a decision has to be made if it is required to be migrated. Data in an offline tablespace cannot be accessed and it has to be brought online for migration.
Tablespace contents. Only tablespaces whose tablespace content attribute is set to PERMANENT have to be migrated.
Logging attribute. Tablespaces may be set to LOGGING or NOLOGGING, which specifies the default characteristic for bulk operations against the objects in the tablespace.
Note In SQL Server, the attributes of status, tablespace contents, and logging are set at the database level and not the filegroup level. If an Oracle schema has tablespaces with more than one value for these attributes, more than one database will have to be created in SQL Server. For example, if a schema uses two tablespaces (one is ONLINE and the other READ ONLY), then the two tablespaces have to be mapped to two separate databases in SQL Server. The two databases can then be set to ONLINE and READ_ONLY, respectively, using the state options available in SQL Server.
Gather the following information for each of the datafiles in the tablespaces:
File name
File size
File autoextensibility
Maximum file size
File auto extension increment
From the information gathered about tablespaces and their datafiles, produce a map for the databases, filegroups. and datafiles to be created in SQL Server as shown in Figure 6.2.
Create Databases for the Schema
To migrate the schemas from Oracle, databases are created in SQL Server for the schemas. The databases will hold the schema objects and their data. This section provides details on how to create databases in SQL Server.
To an Oracle DBA, creating a database means creating an entire database system that contains control files, redo logs, data dictionary and temporary tablespace. In SQL Server, these tasks are accomplished as part of the installation process. Hence creating a database in SQL Server implies adding a user database to the already existing system databases. Members of the fixed server roles sysadmin and dbcreator, or any user with such privileges, can create a database. By default, the creator of the database becomes the owner of the database.
Databases can be created either through Enterprise Manager or by using the CREATE DATABASE T-SQL command. Only members of the sysadmin and dbcreator fixed server roles or users who have been granted the CREATE DATABASE permission can create a new database.
The following basic characteristics of the database have to be decided before creating a database:
Database name. Naming is constrained by the same rules as identifiers. A meaningful name can be used and need not be the same as the name of the Oracle schema being migrated.
Database owner. By default, the user who created the database becomes the database owner (dbo). The owner can be changed using sp_changedbowner. To mimic the Oracle schema, a login with the same name as the schema can be made the database owner. A more detailed account of the implications of database owner and database object owner is provided in the "Qualifiers and Name Resolution" section later in this chapter.
Filegroups. These are the equivalents of tablespaces. A default primary filegroup is created during the database creation.
Primary file. Every database is created with a default primary file belonging to the default primary filegroup identifiable by the .mdf file name extension. The primary file contains startup information for the database and is used to store user data just as any other datafile. Only the size and growth characteristics can be changed.
Secondary files. Additional files can be created in a database and associated with the primary file group or any additional filegroup. The recommended file name extension for secondary files is .ndf.
Transaction log. Hold rollback and redo information required to recover the database. Notice that, unlike Oracle, where rollback segments and redo logs are central to the instance, every database has at least one transaction log. Transaction logs are not part of any filegroup.
There are two ways to create a new database using Enterprise Manager: using the Database Properties dialog and by using the Create Database Wizard. An example of each of these procedures is provided here.
To create a database using the Database Properties dialog, follow these steps:
Right-click Databases, and then left-click New Database, or right-click Server (Server Name\Instance Name), then right-click New, and then left-click Database.
In the Database Properties window, enter the database name in the General pane, the datafile and filegroup configuration in the Data Files pane, and the transaction log configuration in the Transaction Log pane.
To create a new database using the Create Database Wizard, follow these steps:
Expand a server group, and then expand the server in which to create a database.
On the Tools menu, click Wizards.
Expand Database.
Double-click Create Database Wizard.
Complete the steps in the wizard.
SQL Server also offers the CREATE DATABASE statement for creating databases.
To create a database using the T-SQL CREATE DATABASE statement, use the following syntax:
CREATE DATABASE database_name
[ ON
[ < filespec > [ ,...n ] ]
[ , < filegroup > [ ,...n ] ]
]
[ LOG ON { < filespec > [ ,...n ] } ]
[ COLLATE collation_name ]
[ FOR LOAD | FOR ATTACH ]
< filespec > ::=
[ PRIMARY ]
( [ NAME = logical_file_name , ]
FILENAME = 'os_file_name'
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] ) [ ,...n ]
< filegroup > ::=
FILEGROUP filegroup_name < filespec > [ ,...n ]
Configure the Databases
SQL Server is very similar to Oracle in its architecture and can be similarly configured. In the discussion of system configuration options, several options that apply to the individual databases but are set at the instance level are discussed. These options apply uniformly to all databases in the instance. In contrast, the following five different sets of options are set at the database level and apply only to the targeted database.
Auto options
Cursor options
Recovery options
SQL options
State options
These options are set using the ALTER DATABASE statement. A subset of the available options can be set using Enterprise Manager. For a description of each of these options, refer to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_03_6ohf.asp.
If the source Oracle database is in NOARCHIVELOG mode, then the Simple Recovery model is chosen for the database and the Full Recovery model is used if the Oracle database is in ARCHIVELOG mode. If the Oracle tablespace is in read-only mode, then the corresponding database created in SQL Server for that database has to be set to read-only mode. Similarly, if the Oracle tablespace is offline, the corresponding database can be taken offline after the migration is complete. If logging has been turned off for the Oracle tablespace, then set the recovery model for the database to BULK_LOGGED.
An overview of the SQL Server recovery models is available at https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag2k/html/dbRecovery.asp.
Create Filegroups for the Tablespaces
Filegroups in SQL Server are similar to tablespaces in Oracle. They are used to logically group storage. When a database is created in SQL Server, it has one filegroup by default. Based on the number of tablespaces that the schema uses, additional filegroups have to be created in the newly created database.
Every database is created with a default primary filegroup that cannot be renamed or dropped. As a result, one of the tablespaces will have to map to the primary filegroup during migration. Additional secondary or user filegroups can be created with user-specified names.
In Oracle, tablespaces are created as locally managed or dictionary managed. The type of tablespace determines what storage parameters can be used. Creating filegroups is similar to creating tablespaces under Oracle except that a filegroup is added to a specific SQL Server database. Filegroups added to a database are called secondary filegroups, and they can be added to any database. Datafiles are added separately from the definition of a filegroup.
The following two options are available for creating a filegroup.
To create a filegroup using Enterprise Manager, follow these steps:
Expand Databases and right-click Properties of the database to which the filegroup has to be added.
In the Database Properties window, left-click the Filegroups pane.
Type the filegroup name in the next available empty line that follows the filegroup listing.
To add a filegroup to a database using T-SQL, execute the following statement:
ALTER DATABASE database_name ADD FILEGROUP filegroup_name
Add Datafiles to Filegroups
Storage has to be allocated in the SQL Server database to meet the requirements of the schema being migrated. Datafiles are added to filegroups much as datafiles are added to tablespaces in Oracle.
Adding a datafile in SQL Server is similar to the process used in Oracle. The characteristics that can be set are related to auto-growth and are similar in function to Oracle's AUTOEXTEND feature. An additional characteristic that does not exist in Oracle is the logical file name.
Datafiles can be added to the primary filegroup as well as any secondary filegroups either through Enterprise Manager or using T-SQL.
To add a datafile by using Enterprise Manager, follow these steps:
Expand Databases and right-click Properties of the database to which the filegroup has to be added.
In the Database Properties window, left-click the Data Files pane.
Add file information in the next available empty line in the file listing. In the filegroup column, select the filegroup name from the drop-down list. Also set the growth characteristics for the file.
To add a datafile to the primary filegroup or secondary filegroups using T-SQL, use the following syntax:
ALTER DATABASE database
{ ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ]
where <filespec> ::=
( NAME = logical_file_name
[ , FILENAME = 'os_file_name' ]
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] )
The TO FILEGROUP clause is not required to add datafiles to the primary filegroup.
Add Transaction Logs
In Oracle, information on transactions and the changes they make is recorded in redo logs. The redo logs are common to the entire instance. In SQL Server, transactional changes are logged in the transaction log for the database whose objects are involved in the transaction. A database is created with a single default transaction log. The default transaction log has to be sized or new ones added based on the update activity against the database.
Transaction logs can be added using a process similar to that used for adding datafiles, as shown in the following procedure.
To add a datafile by using Enterprise Manager, follow these steps:
Expand Databases and right-click Properties of the database to which the transaction log has to be added.
In the Database Properties window, left-click the Transaction Log pane.
Add the transaction log file information in the next available empty line in the file listing. Also set the growth characteristics.
To add a transaction log to a database using T-SQL, use the following syntax:
ALTER DATABASE database
{ ADD LOG FILE < filespec > [ ,...n ]
where <filespec> ::=
( NAME = logical_file_name
[ , FILENAME = 'os_file_name' ]
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] )
Sample Schema Migration
This section describes an example schema migration using the four steps that have been described earlier. The HR schema that is bundled with the Oracle installation software is used to illustrate the steps in migrating a schema. The storage characteristics of the tablespaces used by the HR schema have been modified to demonstrate some of the
steps that would be common in migrations. This example is conducted using the features and tools offered by Oracle and SQL Server and no third-party tool is employed.
Extract the schema storage requirements by following these substeps:
Produce a listing of tablespaces in which the schema has objects by using the following statement:
SELECT DISTINCT tablespace_name "TABLESPACE NAME"
FROM dba_segments WHERE owner = 'HR'
The
<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">owner</pre>
in the statement is the name of the schema to be migrated. The output is as follows:
<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">TABLESPACE NAME
EXAMPLE INDX
2. Obtain the characteristics of each of the tablespaces using the following statement:
<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT tablespace_name, status, contents, logging
FROM dba_tablespaces WHERE tablespace_name IN (SELECT DISTINCT tablespace_name FROM dba_segments WHERE owner = 'HR')
The
<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">owner</pre>
in the statement is the name of the schema to be migrated. The output is as follows:
<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">TABLESPACE_NAME STATUS CONTENTS LOGGING
EXAMPLE ONLINE PERMANENT LOGGING INDX ONLINE PERMANENT LOGGING
3. Find the datafiles associated with the tablespaces using the following statement:
<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT tablespace_name "TS NAME",
file_name "FILE NAME", bytes/1024/1024 "SIZE MB", autoextensible, maxbytes/1024/1024 "MAX SIZE MB", increment_by*8192/1024/1024 "INCR SIZE MB" FROM dba_data_files WHERE tablespace_name IN (SELECT DISTINCT tablespace_name FROM dba_segments WHERE owner = 'HR')
The
<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">owner</pre>
in the statement is the name of the schema to be migrated. The output is as follows:
<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">TS NAME FILE NAME SIZE AUTO MAX SZ INCR
EXAMPLE /u02/oradata/oracle92/example01.dbf 1000 YES 1000 100 EXAMPLE /u02/oradata/oracle92/example02.dbf 100 YES 1000 100 INDX /u03/oradata/oracle92/indx01.dbf 1000 YES 1000 50 INDX /u03/oradata/oracle92/indx02.dbf 50 YES 1000 50
**Note** The output has been reformatted to fit the page.
Figures 6.1 and 6.2 provide schematics for the hierarchical organization of storage in Oracle and SQL Server. Terminologies such as schema, database, tablespace, filegroup, datafile and transaction log are used here. These figures are modified in Figure 6.3 and Figure 6.4 to show instances for the terminologies based on the HR schema:
[.gif)](https://technet.microsoft.com/en-us/bb497065.ors06_03_big\(en-us,technet.10\).gif)
**Figure 6.3 Oracle storage map for sample HR schema**
[.gif)](https://technet.microsoft.com/en-us/bb497065.ors06_04_big\(en-us,technet.10\).gif)
**Figure 6.4 Equivalent SQL Server storage map for sample HR schema**
**Note** Instead of putting the HR\_DATA files under the required PRIMARY filegroup, a separate HR\_DATA filegroup can be created. This helps make the filegroup and datafiles correlation easier.
Create and configure a new database in the SQL Server instance to hold the schema objects.
Create the database.
Create the database with storage attributes selected using the information gathered in step 1. The location of the files is dependent on the file system of the target Windows® server on which SQL Server is installed.
Create the database using the following commands:
USE master
GO CREATE DATABASE HRDATA ON PRIMARY ( NAME='HRDATA_01', FILENAME='E:\mssql\Mssql$corp1\data\hr_data_01.mdf', SIZE=1000MB, FILEGROWTH=0) LOG ON ( NAME='HRDATA_LOG_01', FILENAME='G:\mssql\Mssql$corp1\log\hr_log_01.ldf', SIZE=10MB, MAXSIZE=100MB, FILEGROWTH=10) GO
All filegroups and files (data and transaction log) can be added at the time of creation of the database. The following is the syntax for doing this:
<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">USE master
GO CREATE DATABASE HRDATA ON PRIMARY ( NAME='HRDATA_01', FILENAME='E:\mssql\Mssql$corp1\data\hr_data_01.mdf', SIZE=1000MB, FILEGROWTH=0), ( NAME='HRDATA_02', FILENAME='E:\mssql\Mssql$corp1\data\hr_data_02.ndf', SIZE=100MB, MAXSIZE=1000MB, FILEGROWTH=100MB), FILEGROUP HR_INDX ( NAME='HRINDX_01', FILENAME='F:\mssql\Mssql$corp1\data\hr_indx_01.ndf', SIZE=1000MB, FILEGROWTH=0), ( NAME='HRINDX_02', FILENAME='F:\mssql\Mssql$corp1\data\hr_indx_02.ndf', SIZE=100MB, MAXSIZE=1000MB, FILEGROWTH=50MB) LOG ON ( NAME='HRDATA_LOG_01', FILENAME='G:\mssql\Mssql$corp1\log\hr_log_01.ldf', SIZE=10MB, MAXSIZE=100MB, FILEGROWTH=10) GO
In this example, only the required primary filegroup with a primary file and the transaction log are created as part of the database creation, and additional filegroups and files are added in subsequent steps to enable demonstration of functions such as adding filegroups and data files.
2. Configure the database.
The mode for the Oracle database can be found using the following query:
<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT log_mode
FROM v$database
If the database is in ARCHIVELOG mode, then set the database to Simple Recovery model; if it is in NOARCHIVELOG mode, then set to Full Recovery model. If the tablespace corresponding to the database is in NOLOGGING mode, set the database to Bulk-Logged Recovery model. For more information on the recovery models available in SQL Server, refer to <https://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4l83.asp>
If the tablespace is in read only or offline mode, set the database to the respective mode after migration of the schema and data is complete.
Add secondary filegroups.
The HR_INDX filegroup can be added using the following commands:
Use master
GO ALTER DATABASE HRDATA ADD FILEGROUP HR_INDX GO
Add secondary datafiles.
The Filename, Current Size, Autoextensibility, Max Size, and Increment Size values retrieved from the Oracle database are used in creating similar datafiles for the HRDATA database. The primary file has already been created to match the first file in the HR_DATA tablespace.
Create additional datafiles for the filegroups using the following commands:
ALTER DATABASE HRDATA
ADD FILE ( NAME='HRDATA_02', FILENAME='E:\mssql\Mssql$corp1\data\hr_data_02.ndf', SIZE=100MB, MAXSIZE=1000MB, FILEGROWTH=100MB) GO ALTER DATABASE HRDATA ADD FILE ( NAME='HRINDX_01', FILENAME='F:\mssql\Mssql$corp1\data\hr_indx_01.ndf', SIZE=1000MB, FILEGROWTH=0), ( NAME='HRINDX_02', FILENAME='F:\mssql\Mssql$corp1\data\hr_indx_02.ndf', SIZE=100MB, MAXSIZE=1000MB, FILEGROWTH=50MB) TO FILEGROUP HR_INDX GO
In this example, the focus was restricted to migrating a single schema. When migrating an entire database consisting of multiple schemas, the storage decisions will have to be made using the best practices in storage allocation that were followed in Oracle. Place temporary files on separate devices, separate data and log files, separate data and index files, distribute I/O load across all available decisions, and separate tables with high activity onto separate devices.
For descriptions of best practices for the storage component of SQL Server, refer to the section titled "Optimizing the Storage Component" at https://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3361.mspx.
Multiple schemas commonly share tablespaces in Oracle databases. Conceptually, SQL Server databases can be viewed more like Oracle tablespaces, and objects from multiple schemas can be created in the same database. From this perspective, SQL Server databases function similarly to Oracle tablespaces. Figure 6.5 shows the relation between schemas and tablespaces, where a schema's objects can be in multiple tablespaces and a tablespace can have objects from multiple schemas.
.gif)
Figure 6.5 Sharing of tablespaces/databases by multiple schemas/owners
Migrate the Schema Objects
In the "Migrate the Schema" section earlier in this chapter, the focus was limited to the schema and its storage structures. This focus culminated in creation of databases with appropriate filegroups and datafiles. The database merely forms the shell in which the schema objects will be enclosed. In this section, the following high-level steps in migrating the schema objects are taken. These steps cover tasks related to schema objects and security.
Create the schema owner.
Create the schema objects.
These steps are discussed in detail under the following subheadings.
Create the Schema Owner
A schema owner in Oracle is a user with privileges to create objects. This is true in SQL Server as well. A user will have to be created in SQL Server in the database in which the schemas objects will be created. The user is then given privileges in the database to create objects and populate the data.
Users of Oracle and SQL Server databases can be classified as administrative users, application users, and schema owners.
Administrative users are users with special roles, such as database administrator and security administrator.
Application users are users who manipulate data in the owning user's tables.
Schema owners are users who create and maintain objects related to an application.
The basics for the creation of all the three types of users are the same and are discussed in detail here. This knowledge is useful in creating application users and administrative users in Chapter 7, "Developing: Databases — Migrating the Database Users."
A discussion of user accounts for each of the categories of users is presented in the rest of this section. This discussion covers the differences in the logins between Oracle and SQL Server and the associated authentication and password functionality.
Accounts
In terms of logins, all three types of users are created equal and are differentiated only by the privileges they are bestowed. However, keeping with the practice of isolation and autonomy of user databases, logins are implemented a little differently in SQL Server. It is important to understand this difference before starting the migration of schemas and users. SQL Server has two levels (not types) of logins. In addition to the instance login, SQL Server requires separate logins be created for each database that the user or schema needs to connect to.
To avoid confusion while referring to these various logins and accounts, this guide uses the following terminologies commonly employed for the various logins in Oracle and SQL Server.
Oracle — Instance Level — user or username
SQL Server — Instance Level — login (login ID)
SQL Server — Database Level — user
Logins provide access to the instance of SQL Server, whereas the user account controls privileges to objects inside the database. Figure 6.6 illustrates these relationships.
.gif)
Figure 6.6 Migration of schema owner security from Oracle to SQL Server
The migration of a schema owner from Oracle to SQL Server requires a login be created at the instance level and a user be created at the database level. The impact of this architectural change is observed in how objects are qualified. In Oracle, a schema object is identified in SQL statements using schema.object_name. While migrating to SQL Server the schema object has to be fully qualified as database.owner.object_name.
Even though there is no difference between the login aspects of the different types of users, only the schema owners and their migration are discussed here. A discussion of application users and administrative users is postponed to the next task of the database migration, migrating the user, which is discussed in Chapter 7, "Developing: Databases — Migrating the Database Users."
Authentication
Oracle offers several options for the authentication of users. The two popular methods in use are authentication by the database and authentication by the operating system. In SQL Server, the database mode is called SQL Server Authentication Mode and the operating system mode is called Windows Authentication Mode. The database authentication modes in Oracle and SQL Server are closely compatible and use a user name and password pair. The operating system authentication is quite different between Oracle and SQL Server. Oracle's operating system mode can only authenticate users with local accounts on UNIX servers. Windows authentication for SQL Server is actually performed by the domain and not the local account on the Windows server.
Password Management
The Oracle RDBMS also provides password management functions, such as account locking, password lifetime and expiration, password history, and password complexity verification. The SQL Server RDBMS does not provide these services, and Windows security is used to provide these features.
Hence the migration of Oracle user names to SQL Server logins and users is dependent on the type of authentication in use as well as the requirements of password management. Table 6.1 shows the migration options for Oracle logins based on authentication mode and the requirements on password management functionality.
Table 6.1: Login Migration Based on Authentication Requirements
Oracle Authentication Mode |
Oracle Password Management |
SQL Server Authentication Mode |
|---|---|---|
Database |
None |
Database |
Database |
Required |
Windows |
Operating system |
N/A |
Windows |
As a practice, the schema owner login should not be used for connection by the application instance (three-tier) or the application users (two-tier and three-tier). Hence a SQL Server authenticated login would be appropriate. However, for clients who restrict authentication to Windows mode only because of security concerns, a domain login should be created for the schema owner.
Based on the security model — either Windows users or groups — review the guidelines in the "SQL Server 2000 SP3 Security Features and Best Practices" white paper available at https://www.microsoft.com/sql/techinfo/administration/2000/security/securityWP.asp to assist with implementation.
The following SQL statement can be used to extract the characteristics of the schema owner account in the Oracle database:
SELECT du.username, DECODE(du.password,'EXTERNAL','EXTERNAL','DB') "AUTHENTICATION MODE", du.default_tablespace, du.temporary_tablespace, dp.resource_name, dp.limit FROM dba_users du, dba_profiles dp WHERE du.profile = dp.profile AND dp.resource_type = 'PASSWORD'
SQL Server does not have a CREATE USER statement. There are two system stored procedures to add logins for the two modes of authentication, and a separate stored procedure to add users at the database level.
It is possible to add a new login for SQL Server using Enterprise Manager and system stored procedures.
To add a new login to a SQL Server instance using Enterprise Manager:
Expand the SQL Server Group and expand the Server (Server Name\Instance Name).
Expand Security, right-click Logins, and then click New Login.
Provide the login name and authentication method in the General pane. System and database level privileges can be granted through fixed roles under the Server Roles pane. Object-level privileges can be granted either directly or through user roles in the Database Access pane.
If the user is to be authenticated by the domain, the correct domain should be picked using the drop-down menu under Windows Authentication in the General pane. If domain authentication is used, the name of the login is in the form Domain name\Domain login name.
The default database that the user will connect to when connecting to SQL Server can be set under the Defaults heading in the General pane. This is different from the default tablespace setting for a user in Oracle.
By checking the appropriate database, the new user can be granted privileges on specific databases in the SQL Server instance by checking the appropriate database in the Database Access pane. This action also creates user accounts of the same name in the respective databases. For a schema owner, the db_owner role has to be granted for the privilege of creating objects in the target database. The db_owner role has complete administrative authority over the database and can be considered to be similar to the Oracle dba role but restricted to a single SQL Server database. To give the SQL Server database object owner equivalent of the resource role in Oracle, which grants DDL privileges, the database role db_ddladmin can be used. SQL Server also has the CREATE TABLE, CREATE TRIGGER, CREATE VIEW and CREATE PROCEDURE roles that can be used to further curtail the capabilities of the schema owner.
To add a new Windows authenticated login to a SQL Server instance using T-SQL, use the following syntax:
sp_grantlogin [ @loginame = ] 'login_name'
where
login_name
is of the form FakePre-4b6ea626ecd6493bb05bb24bd908fc94-e3379ed0ac9d4a9eade0d0cee9eae7a9
To add a new database authenticated login to a SQL Server instance using T-SQL, use the following syntax:
sp_addlogin [ @loginame = ] 'login_name [ , [ @passwd = ] 'password' ] [ , [ @defdb = ] 'database_name' ] [ , [ @encryptopt = ] 'encryption_option' ]
where database_name specifies the database the login connects to after logging in (default database). While passwords are encrypted in SQL Server by default, the option exists to skip encryption to allow custom password encryption by the application using a different algorithm.
A user account should be created separately for the login in the default database.
To create a user account to a SQL Server database using T-SQL, use the following syntax:
sp_grantdbaccess [ @loginame = ] 'login_name'[, [ @name_in_db = ] 'user_name'
The name chosen for the user account can be different from that for the login account.
Some of the other characteristics associated with a user or schema owner in Oracle that need to be addressed are:
Default tablespace. In SQL Server, a default filegroup can be set for each database which has the same function and effect as the Oracle default tablespace using the following syntax.
ALTER DATABASE database_name
MODIFY FILEGROUP filegroup_name DEFAULT
Temporary tablespace. By default, all users of SQL Server use the tempdb database.
Tablespace quota. Quotas cannot be set in SQL Server at the instance, database, or filegroup level.
Create the Schema Objects
This section discusses the migration of the schema objects from Oracle to SQL Server.
The following is a complete list of objects that are classified by Oracle as schema objects:
Tables
Clusters
Object Tables
Index-organized Tables
Constraints
Triggers
Indexes
Views
Object Views
Functions
Stored Procedures
Packages
Synonyms
Sequences
Database Links
Object Types
Table 6.2 provides a high-level view of how the Oracle schema objects map to SQL Server.
Table 6.2: SQL Server Objects that Replace Oracle Schema Objects
Oracle |
SQL Server |
|---|---|
Table |
Table |
Cluster |
Table |
Object Table |
Cluster |
Index-organized Table |
Table (with clustered index) |
Constraints |
Constraints |
Triggers |
Triggers |
Index |
Index |
View |
View |
Object View |
N/A |
Synonym |
View |
Sequence |
Identity |
Database Link |
Linked Server |
Object Types |
N/A |
Procedure |
Stored Procedure |
Function |
Function |
Package |
Stored Procedure |
Some of these objects fall under broader classifications, such as tables and views. While the discussion of some of the objects is beyond the scope of this guide, the following are covered in this chapter:
Tables
Comments
Constraints
Triggers
Views
Indexes
Stored Programs, including functions, stored procedures, and packages
Objects not found in SQL Server, including sequences, synonyms, and database links
The data definition language (DDL) for defining objects is in accord with ANSI SQL standards. Because both Oracle and SQL Server maintain compliance with SQL-92 and SQL-99 standards, the syntax for defining objects is very similar.
Identifiers and Naming
Oracle object and column names are generally not case-sensitive. However, no assumptions should be made because they can be forced to be case sensitive by delimiting them (enclosing them) in double quotation marks (“). For example, the name TRANSACTION_DATE can be made case sensitive by using “Transaction_Date”. Also, quotes can be used to create irregular identifiers such as “Transaction Date” (with a blank space). Similarly, object and column names in SQL Server are not case-sensitive by default, but this default behavior can be changed by modifying configuration settings. Nonstandard or delimited identifiers can be created just as in Oracle using either quotation marks (“) or brackets ([ ]). Read the "Using Identifiers" section in SQL Server Books Online for rules about the construction and use of regular and delimited identifiers.
Oracle table and column names are stored in data dictionary tables as uppercase strings unless forced to be case sensitive using delimiters. In contrast, the default SQL Server behavior is to store them in the case used when they were created. Because scripts for use with Oracle are written with such expectations, it is recommended you use uppercase identifiers while creating the objects in SQL Server (without forcing case by using delimiters).
Qualifiers and Name Resolution
The names of objects are influenced by uniqueness requirements. Oracle object names have to be unique for a schema. In SQL Server, the same owner can own objects of the same name in two different databases, that is, the combination of owner.table_name needs to be unique only within a database. Hence objects are qualified in Oracle as
[schema.]object_name
whereas in SQL Server the complete qualifier is
[database.]owner.]object_name
Rules have been established in both Oracle and SQL Server for resolving an object_name that is not qualified.
In Oracle, the resolution is governed by synonyms and the concept of namespaces. Tables, views, snapshots, sequences, synonyms, procedures, functions, and packages are in a single namespace. Triggers, indexes, and clusters each have their own individual namespace. The following order is used to resolve an object that falls in the table namespace:
Table namespace of current user's schema
Private synonym in the current user's schema
Public synonym
The order is different in SQL Server, and resolution occurs as:
Current user's schema in the current database (set with USE statement)
Database owner dbo of the current database
These relationships have to be taken care of during the migration of the users and applications. Because a user can own objects of the same name in multiple databases and different users can have objects of the same name in the same database, it is recommended that both database name and owner name be used to qualify objects in SQL Server.
Changing object identifiers to meet SQL Server standards can affect the applications using them. However, changing the names of certain objects, such as indexes and constraints, could be transparent to the business applications. Given the rules for naming in Oracle and SQL Server, there is no need to change the names of any of the identifiers.
Note It is recommended that all objects in the migrated SQL Server database be qualified by database and owner because the rules for resolving names could otherwise lead to the wrong object.
Working with Data Types
Oracle and SQL Server offer two types of data types: native data types and user defined data types. The data types that are provided by the DBMS are called native data types or system data types. There are four basic classes of data: character, numeric, datetime, and binary. In addition, both Oracle and SQL Server have a few data types that are unique to each system. Tables 6.3 through 6.5 provide a complete mapping of Oracle data types to SQL Server data types that you should use when migrating.
Care has been taken to suggest the closest possible data type both in terms of type of data being considered as well as the scale or size of the data that has to be accommodated. A bad choice in data type can lead to a lot of problems during data migration. The choice has to be driven by the definition in the source table and not by the data in the table. Even when data migration is successful, the application may fail some time in the future because the table cannot handle data that it was originally designed for. SQL Server data types can comfortably provide a close equivalent of the Oracle data types, and over sizing is not recommended because it drives up the row size and the storage requirements and will affect performance.
Character or Alphanumeric Data
Note that Oracle and SQL Server differ in the parameter used to define Char (Nchar), and Varchar (NVarchar) columns. For example, when CHAR(n) is used to specify a column data type in Oracle, n represents the number of characters, while in SQL Server it represents number of bytes.
Table 6.3 provides the SQL Server data types to use when migrating Oracle character data.
Table 6.3: SQL Server Equivalent for Oracle Character-based Data Types
Oracle Data Type |
Max Size (Bytes) |
SQL Server Data Type |
Max Size (Bytes) |
|---|---|---|---|
Char |
2000 |
Char |
8000 |
NChar |
2000 |
NChar |
4000 |
Varchar |
4000 |
Varchar |
8000 |
NVarchar |
4000 |
NVarchar |
4000 |
Varchar2 |
4000 |
Varchar2 |
8000 |
NVarchar2 |
4000 |
NVarchar |
4000 |
LONG |
231 |
Text |
231–1 |
CLOB |
232 |
Text |
231–1 |
NCLOB |
232 |
NText |
230–1 |
Numeric Data
Oracle has only one numeric data type, NUMBER, that can store zero, positive, negative, fixed, and floating point numbers, with a precision (p) of 38 digits and a scale (s) ranging from -84 to 127. Synonyms, such as NUMERIC, DECIMAL, FLOAT, INTEGER, can be used. SQL Server, on the other hand, has eight distinct named numeric data types that constrain the range of values they can hold. SQL Server also has three other data types that are of the number category: BIT, MONEY, and SMALLMONEY. Table 6.4 compares numeric data types in Oracle and SQL Server.
Table 6.4: Finding Closest SQL Server Equivalent to Oracle Numeric Data Types
Oracle |
SQL Server |
|---|---|
Number(19,0) |
BigInt |
Int or Number(10,0) |
Int |
SmallInt or Number(6,0) |
SmallInt |
Number(3,0) |
TinyInt |
Number(p,0) |
Decimal or Numeric(p,s) |
Float or DoublePrecision or Number(38) |
Float |
Real or Number(19) or Float(63) |
Real or Float(24) |
Number(19,4) |
Money |
Number(10,4) |
SmallMoney |
Binary Data
Oracle's BLOB replaces the older RAW and LONG RAW data types. The IMAGE data type in SQL Server is the closest equivalent for Oracle’s BLOB data type, and it can support data up to 2 GB in size. The BINARY data type is a fixed size column while VARBINARY is its variable size counterpart. Table 6.5 provides SQL Server equivalents for Oracle binary data types.
Table 6.5: SQL Server Equivalent for Oracle Binary Data Types
Oracle Data Type |
Max Size |
SQL Server Data Type |
Max Size |
|---|---|---|---|
BLOB |
4 GB |
Image |
2 GB |
Raw |
2000 bytes |
Image |
2 GB |
Long Raw |
2 GB |
Image |
2 GB |
BFile |
4 GB (file pointer) |
N/A |
|
Raw(n) |
2000 bytes |
Binary(n) or VarBinary(n) |
8000 bytes |
For more information about designing for and implementing BLOBs in SQL Server, refer to https://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx.
Date and Time
Historically, Oracle has had one data type, Date, which stores both date and time information. In Oracle 9i, three new date and time data types have been added to address these shortcomings. The Timestamp data type can store fractional seconds up to 9 digits of precision. Time zone aware data types, Timestamp With Time Zone and Timestamp with Local Time Zone, are also available and they can handle daylight saving time.
SQL Server has two data types: DateTime and SmallDateTime. DateTime can represent the date and time in the range January 1, 1753 to December 31, 9999 with a precision of one-three hundredth of a second. SmallDateTime can represent dates in the range January 1, 1900, through June 6, 2079 with a precision limited to the minute. The Oracle Date and Timestamp data types should be migrated to the DateTime data type. The other two time zone aware data types do not have an equivalent in SQL Server.
User Defined Types
A simple implementation of user-defined data types is the capability to define data types as simple variations of the primitive data types. For example, defining a data type ZIP as a native type Char, with a length of 5, provides a uniform definition that avoids any ambiguity between designers and developers.
For example, the user-defined data type ZIP can be created as follows:
CREATE TYPE zip_type AS OBJECT (zip char(5)) /
In SQL Server, the user-defined ZIP data type is:
EXEC sp_addtype zip_type, 'char(5)'
User-defined data types can be recursively used to define other user-defined data types. However, SQL Server does not support object types and their collections.
Tables
The definition of the table data structure is the same, both in concept and form, in both Oracle and SQL Server. This can be attributed to compliance with ANSI SQL standards.
When designing the table data structure, data integrity is as important as data access. To serve these purposes, Oracle provides several options for how data can be organized within the table. Table 6.6 summarizes them:
Table 6.6: Oracle and SQL Server Table Types Compared
Oracle |
SQL Server |
|---|---|
Heap-Organized Table |
Heap |
Clustered Table |
- |
Partitioned Table |
- |
Nested Table |
- |
Temporary Table |
Temporary Table |
External Table |
- |
Object Table |
- |
Index-Organized Table |
Clustered Index |
The most common implementation of the table object is, in its basic form, the heap-organized table, and this table object is the focus of the remainder of this section.
The CREATE TABLE syntax, which creates a simple heap organized table, has three major parts:
Table name
Body (enclosed in parenthesis) — column name, column properties (data type, defaults, inline column constraints), out-of-line column constraints, table constraints
Storage specification
Table 6.7 compares the structural composition of the CREATE TABLE syntax in Oracle and SQL Server.
Table 6.7: High-level Comparison of Table Definition Syntax
Oracle |
SQL Server |
|---|---|
CREATE TABLE |
CREATE TABLE |
[schema.]table_name |
[[database.]owner.]table_name |
( column name column data type default expression column constraint, ... out of line or table constraint ) |
( column name column data type defaults expression column constraint, ... out of line or table constraint ) |
[storage_specification] |
[storage_specification] |
The steps for creating a table in SQL Server using both Enterprise Manager and T-SQL are provided in the following procedures.
To create a new table using Enterprise Manager, follow these steps:
Expand Server, then Databases, and then the target database.
Right-click Tables and select New Table. A new window appears for adding columns and other objects, such as indexes and constraints related to the table. This window is called the Table Designer tool.
Add new columns in the top grid (column definition grid) along with the column data type and nullability constraint. Some of the column's properties, such as Default Value, can be provided in the Columns pane at the bottom. The Identity property is discussed in greater detail under the "Sequences" heading.
Click the Table and Index Properties button found on the Columns window, or right-click anywhere in the column definition grid and select Properties to bring up the table Properties window, which can be used to specify table level properties, constraints, and indexes.
To create a new table using T-SQL, use the following syntax:
CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > } [ ,...n ]
)
[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]
where < column_definition > ::= column_name data_type
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expression ]
The following list covers each of the components of the CREATE TABLE syntax:
Table and column name. As is the case in the "Identifiers and Naming" section earlier in this chapter, Oracle table and column names do not need any changes when migrated to SQL Server.
Column data type. The column data type forms part of the table definition and is an integral part of creating a table. For the sake of reducing the complexity in the discussion of tables (because there are several components to be dealt with here), the discussion of data types has been covered in the section "Working with Data Types" earlier in this chapter.
Default value. The rules for specifying default value are very similar in both databases, and default values can be specified in most cases. One of the difficulties that may be encountered while migrating to SQL Server is the lack of an equivalent Oracle system function used for defining the default values. In SQL Server, a default value cannot be specified for a column of type timestamp. While Oracle treats defaults as a property of the column, SQL Server defines defaults as a constraint.
Constraints. A detailed discussion of constraints appears in the "Constraints" section later in this chapter.
Storage properties. A detailed discussion of the storage architecture appears in the "Migrate the Storage Architecture" section in Chapter 5, Developing: Databases — Migrating the Database Architecture." The only storage property that can be specified during table creation is the filegroup name. If a filegroup is not specified, then the table is created in the database's default filegroup.
In the Table and Index Properties window, the Table Filegroup drop-down list can be used pick a filegroup from which the table will be allocated storage. The Text Filegroup drop-down list can be used to specify the filegroup to be used for large objects, such as text and image columns.
Note In Oracle and SQL Server, tables can be created based on the definition of other tables.
In Oracle, this is accomplished using the following statementCREATE TABLE table_name AS SELECT ...
The same can be achieved in SQL Server using the following syntax:
SELECT ... INTO table_name
The various table types found in Oracle can be migrated as described in the following list. Detailed knowledge of each table type is not necessary to accomplish the migration.
Clustered table. Sometimes simply called clusters, these do not have an equivalent in SQL Server. The tables in the cluster have to be created as regular heaps. If rows are frequently accessed using a range search, a clustered index may be created on such column(s). Only one clustered index can be created on a table.
This migration of clustered tables in Oracle to heaps in SQL Server will be transparent to the application and users.
Partitioned table. SQL Server does not have the partitioned table option. However, SQL Server offers partitioned views that are built using the same strategy that was used in Oracle (before Oracle 8i) when true horizontal partitioning was not available. This involves creating separate tables for each of the partitions, with a check constraint on the partition key column(s) to enforce a range of values. A partitioned view is then created as a union of all the constituent tables. Details on the implementation of partitioned views can be found under the topic "Creating a Partitioned View" in SQL Server Books Online.
This migration of partitioned tables in Oracle to heaps in SQL Server will be transparent to the application and users.
Nested table. SQL Server does not support nested tables. The implementation strategy used in Oracle can be imitated in SQL Server for migrating them. The nested table column can be separated out into its own table (denormalized with similar definition) with a unique identifier used to connect the rows from the parent table to the child table.
- Minor modification to retrieve the data from the child table will be required in the code to accommodate this change.
Temporary table. SQL Server supports both local and global temporary tables. SQL Server's local temporary table is equivalent to Oracle's global temporary table (with the ON COMMIT PRESERVE ROWS) because it provides the same level of isolation from other sessions.
Table 6.8 shows the syntax for creating temporary tables in SQL Server.
Table 6.8: SQL Statements for Creating Temporary Tables in Oracle and SQL Server
Local temporary tables in SQL Server work identically to Oracle temporary tables and do not warrant any changes in the code. But the code pieces that assume that rows are deleted automatically when working with tables with ON COMMIT DELETE ROWS has to be modified to include a DELETE statement in compensation.Oracle
SQL Server
CREATE GLOBAL TEMPORARY TABLE table_name ...
ON COMMIT DELETE|PRESERVE ROWS
CREATE TABLE #table_name
External table. SQL Server does not have the option to create tables whose data resides in flat files. Hence such data will have to be imported into the database.
Object table. SQL Server does not support objects. The strategy for migrating object tables to SQL Server is to flatten (absorb) the object column into the table itself. This will induce only a small change in the SQL code that accesses the data.
Index-organized table. The SQL Server clustered indexes are very similar to the index-organized table (IOT), where the index is merged into the table instead of two separate structures. Clustered indexes are implemented in a fashion similar to index-organized tables and have very similar features.
Index-organized tables are sorted on the primary key. To migrate an index-organized table to SQL Server, the CLUSTERED keyword can be used in conjunction with the PRIMARY KEY constraint, as described in the syntax given in Table 6.9.
In SQL Server, a clustered index can be created on any column(s) of a table, but it has to be created using the CREATE INDEX clause. If a clustered index is created with non-unique columns, SQL Server enforces uniqueness by adding an uniqueifier to the rows with duplicates.
In most cases, the primary key would be the ideal column for a clustered index. An identity column can also be used for a clustered key. In SQL Server, it is recommended that every table should have a clustered index.
Note Clustered indexes should be created before creating any nonclustered indexes. If clustered indexes are planned for a table, defer creation of nonclustered indexes (as part of PRIMARY KEY or UNIQUE constraint definition) during the table creation in this step.
Table 6.9 shows the SQL Server options for creating the equivalent of an Oracle index-organized table.
Table 6.9: SQL Statements for Creating Index-organized Tables in Oracle and SQL Server
Oracle
SQL Server
CREATE TABLE table_name
(column_name datatype, ... ) ORGANIZATION INDEX [ storage_definition ]
CREATE TABLE table_name (column_name datatype, [ CONSTRAINT constraint_name ] PRIMARY KEY CLUSTERED [ ON filegroup | DEFAULT ] ) or CREATE [UNIQUE] CLUSTERED INDEX index_name ON table_name ( column_name [, ...] ) [ ON filegroup | DEFAULT ]
Migration of tables is complicated by the referential integrity (foreign key) constraints between tables. There are two options for how to migrate tables. Option one is to create
tables in a specific order based on the foreign key constraints (parent tables first). Option two is to create tables in any order, leaving out the foreign key constraints, and adding them after all the tables have been created.
Comments
This is a property of the table that is often not documented and, hence, easily overlooked during a migration. SQL Server does not have a comment property associated with tables and columns. However, SQL Server provides the capability to associate custom properties, called extended properties, with objects.
Comments can be added to tables and columns using Enterprise Manager and system stored procedures.
To add a comment on tables and columns using Enterprise Manager, follow these steps:
Expand Server, then Databases, and then the target database.
Left-click Tables to display the list of tables.
Right-click the target table and left-click Design Table. This brings up the Table Designer.
Comments for the individual columns can be added in the Description field of the Columns pane at the bottom of the Table Designer window.
In the Table Designer window, click the Table and Index Properties button found above the grid.
Left-click the Tables pane of the Properties window. Table comments can be added in the Description field in the Tables pane.
The sp_addextendedproperty system stored procedure can be used to add any user-defined metadata to any column in SQL Server. This (and any other properties that are added to the column) can be retrieved with the fn_listextendedproperty function.
Table 6.10 shows the syntax for adding comments at the table and column levels.
Table 6.10: Comparison of Functionality for Adding Comments in Oracle and SQL Server
Oracle
SQL Server
COMMENT ON TABLE table_name IS comment
sp_addextendedproperty 'comment', comment, 'user', schema_name, 'table', table_name
COMMENT ON COLUMN table_name.column_name IS comment
sp_addextendedproperty 'comment', comment, 'user', schema_name, 'table', table_name, 'column', column_name
Constraints
Constraints are data integrity rules that are defined on the columns of a table to enforce certain business rules. As an example, the specification of data types in the definition of the column constitutes a constraint.
Table 6.11 compares the availability of various types of constraints in Oracle and SQL Server:
Table 6.11: Constraints Available in Oracle and SQL Server
Oracle
SQL Server
NOT NULL
NOT NULL
UNIQUE
UNIQUE
PRIMARY KEY
PRIMARY KEY
FOREIGN KEY
FOREIGN KEY
CHECK
CHECK
The functionality provided by Oracle and SQL Server to define constraints on columns, including the syntactic use of inline (or column) constraints and out-of-line (or table) constraints, is almost identical. The syntax in Table 6.12 illustrates the similarity. Out-of-line constraints have to be used when more than one column is involved in the definition of a constraint. If a constraint name is not specified using the CONSTRAINT clause, a default name is generated by the database. The default names given by Oracle are of the form SYS_Cn where n is a unique number generated by Oracle. The names provided by SQL Server are more suggestive with respect to the type of constraint (for example, CK, PK, and FK) and the table and column(s) involved. The syntax for the two options for defining constraints in SQL Server is provided in Table 6.12.
Table 6.12: T-SQL Statements for Defining Inline and Out-of-line Constraints in SQL Server
Inline (Column) Constraint
Out-of-line (Table) Constraint
CREATE TABLE table_name (column_name datatype [ CONSTRAINT constraint_name ] [ [ NOT ] NULL | UNIQUE | PRIMARY KEY | CHECK (condition) | REFERENCES ref_table (ref_column) ] )
CREATE TABLE table_name (column_name datatype, [ CONSTRAINT constraint_name ] [ UNIQUE (column_name [, ...]) | PRIMARY KEY (column_name [, ...]) | CHECK (condition) | FOREIGN KEY (column_name [, ...]) REFERENCES ref_table (ref_column) ] )
NOT NULL Constraint
In both Oracle and SQL Server, the NOT NULL constraint can be specified only as a column constraint. More often than not, a constraint name is not provided while creating NOT NULL constraints.
When a column has not been specifically defined as NOT NULL in Oracle, it defaults to being nullable. When NULL or NOT NULL is not specified explicitly for a column definition, SQL Server uses the default of NOT NULL. For ANSI compatibility, setting the database option ANSI_NULL_DEFAULT to ON changes the database default to NULL. In SQL Server, database and session settings can override the nullability specified by the column definition.
Some DBAs like to use meaningful names for all constraints so that errors referring to constraint violations can be easily recognized in error messages. When migrating from Oracle, NOT NULL constraints with user-defined names can be queried using the following syntax:
CREATE TABLE NOT NULL CONSTRAINTS (constraint_name varchar2(30), owner varchar2(30) table_name varchar2(30), column_name varchar2(30), search_condition clob); INSERT INTO NOT NULL CONSTRAINTS SELECT dc.constraint_name, dc.owner, dc.table_name, dcc.column_name, to_lob(dc.search condition) FROM dba_constraints dc, dba_cons_columns dcc WHERE dc.owner = dcc.owner AND dc.constraint_name = dcc.constraint_name AND dc.constraint_type = 'C' AND dc.owner = 'user_name'; COMMIT; SELECT * FROM NOT_NULL_CONSTRAINTS WHERE dbms_lob.instr(search_condition, 'IS NOT NULL',1,1) > 0;
NOT NULL constraints can be created on tables using Enterprise Manager and the CREATE TABLE T-SQL statement.
To create a NOT NULL constraint using Enterprise Manager, follow these steps:
- In the Table Designer, the Allow Nulls column should be unchecked for enforcing the NOT NULL constraint and checked to indicate a NULL constraint.
To create a NOT NULL constraint Using T-SQL, use the following syntax:
CREATE TABLE table_name (column_name datatype [ CONSTRAINT constraint_name ] { NULL | NOT NULL }, ... )Check Constraint
Both Oracle and SQL Server use the same syntax to define check constraints; both also have similar restrictions in its usage. CHECK constraints can be defined on a single column or multiple columns at the table level. The conditions specified in the CHECK clause should evaluate to a Boolean value of TRUE or FALSE. The conditions can refer to other columns but are restricted to the row being modified.
Both Oracle and SQL Server allow multiple CHECK constraints on a column. A column constraint can only reference the column it is being created on. Each constraint can have multiple concatenated conditions. The process to create check constraints on tables using Enterprise Manager and T-SQL is provided in the following procedures.
To create a check constraint using the Table Designer in Enterprise Manager, follow these steps:
In the Table Designer, click the Manage Constraints button to bring up the table Properties window with the Check Constraints pane active.
Click the New button to add a constraint.
A name of the type CK_table_name is inserted by the system in the Constraint name box. The constraint name can be changed to match the name given in Oracle only after entering a valid condition in the Constraint expression box.
To create a check constraint using T-SQL, use the following syntax:
Table 6.13 provides the syntax for the two alternative formats available in SQL to specify CHECK constraints during table creation. The syntax is the same in Oracle and SQL Server.
Table 6.13: T-SQL Statements for Defining Inline and Out-of-line CHECK Constraints in SQL Server
Inline (Column) Constraint
Out-of-line (Table) Constraint
CREATE TABLE table_name
(column_name datatype
[ CONSTRAINT constraint_name ] CHECK (condition)
...
)
CREATE TABLE table_name
(column_name datatype,
...
[ CONSTRAINT constraint_name ] CHECK (condition)
)
Unique Constraints
Oracle and SQL Server use indexes to enforce unique constraints. In Oracle, if a unique or non-unique index already exists on the constraint columns, the index is used to enforce the constraint without creating new ones. Therefore, if a user-defined index is preferred instead of a system-defined (and named) one, it is recommended that the index be created first. Unless a clustered index is explicitly specified, SQL Server creates a nonclustered index on the unique key column(s).
An important difference in the implementation of the UNIQUE constraint is Oracle allows multiple rows with NULL values in all the columns making up the unique constraint, whereas SQL Server allows only one row to have a NULL value for the UNIQUE column.
In Oracle, the UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints can be a composite of up to 32 columns, while in SQL Server the limit is 16.
Unique constraints can be created using Enterprise Manager as well as T-SQL statements.
To create a UNIQUE constraint using Enterprise Manager, follow these steps:
In the Table Designer, click the Table and Index Properties button.
Select the Indexes/Keys tab and click New. A system-assigned index name appears with the column name, column order, index filegroup, and fill factor filled in using defaults. You will need to replace these default values with your own.
Enter the name of the new index in the Index name text box and select the columns to be included in the constraint in the correct order in the grid.
Check the Create UNIQUE check box and select the Constraint radio button.
Check the Create as CLUSTERED box if a clustered index is to be used to enforce the constraint. If a nonclustered index is chosen, a filegroup can be specified for the index. Because only a single clustered index can be created on a table, and the filegroup cannot be changed from the primary filegroup.
The equivalent of Oracle's PCTFREE can be specified for the index that will be created to enforce the constraint in the Fill Factor box.
To create a UNIQUE constraint using T-SQL, use the following syntax:
The only syntactic difference in the UNIQUE constraint definition between Oracle and SQL Server is with the USING INDEX clause used to define the index that enforces the constraint. The USING INDEX clause of Oracle has an equivalent in SQL Server and can be used with both column and table constraints. The syntax for table constraint is described in Table 6.14.
Table 6.14: SQL Statements for Defining UNIQUE Constraints in Oracle and SQL Server
Oracle
SQL Server
CREATE TABLE table_name (column_name datatype, ..., [ CONSTRAINT constraint_name ] UNIQUE ( column_name [, ...] ) [ USING INDEX [ TABLESPACE tablespace_name ] [ storage_definition ] ]
CREATE TABLE table_name (column_name datatype, ..., [ CONSTRAINT constraint_name ] UNIQUE [ CLUSTERED | NONCLUSTERED ] ( column_name [, ...] ) [ WITH FILLFACTOR = fillfactor ] [ ON FILEGROUP { filegroup | DEFAULT } ]Primary Key Constraint
PRIMARY KEY constraints have all the characteristics of UNIQUE constraints, with the additional restriction that all primary key columns be NOT NULL. Oracle creates a UNIQUE index and a NOT NULL constraint to implement PRIMARY KEY constraints. With Oracle 9i, an existing non-unique index may be used to enforce the primary key constraint. In SQL Server, if a clustered index does not already exist on the table, or a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint. The following procedures demonstrate that it is far easier to use T-SQL than Enterprise Manager when it comes to creating a PRIMARY KEY constraint.
To create a primary key constraint using Enterprise Manager, follow these steps:
In the Table Designer, click and select the column(s) that are part of the primary key (use the Ctrl key for this).
Right-click anywhere along the selected column(s) and click Set Primary Key. A check mark appears next to the Set Primary Key option.
A unique clustered index using the naming convention PK_table_name is automatically created by the system.
The name and properties of the index can be changed from those assigned by the system through the Indexes/Keys pane of the Table and Index Properties window.
To create a primary key constraint using T-SQL, use the following syntax:
Oracle and SQL Server differ only in the USING INDEX part of the PRIMARY KEY constraint syntax. The syntax differs from the UNIQUE constraint definition only in the UNIQUE keyword being replaced with PRIMARY KEY. In both Oracle and SQL Server, a NOT NULL constraint is added by the system for all primary key columns when one is not explicitly specified.
The CREATE TABLE syntax in Table 6.15 is for defining primary key constraints and its associated index.
Table 6.15: SQL Statements for Defining PRIMARY KEY Constraints in Oracle and SQL Server
Oracle
SQL Server
CREATE TABLE table_name
(
column_name datatype,
...,
[ CONSTRAINT constraint_name ] PRIMARY KEY ( column_name [, ...] ) [ USING INDEX [ TABLESPACE tablespace_name ] [ storage specs ] ]
CREATE TABLE table_name
(
column_name datatype,
...,
[ CONSTRAINT constraint_name ] PRIMARY KEY [ CLUSTERED | NONCLUSTERED ] ( column_name [, ...] ) [ WITH FILLFACTOR = fillfactor ] [ ON FILEGROUP { filegroup | DEFAULT } ]
Foreign Key Constraint
A foreign key constraint enforces the rule that a non-null value in one or more columns of a table (child) must exist in a corresponding set of columns in the same (self-referential) or another (parent) table. The referenced key columns must possess a primary key or unique key index on them.
The number and data types of columns specified in the FOREIGN KEY clause should match the corresponding columns of the referenced table. FOREIGN KEY constraints in SQL Server have a restriction of not being able to cross database boundaries. In the case where foreign key constraints existing between different schemas of a source Oracle database are migrated to separate databases in SQL Server, the constraints will have to be replaced with triggers. In Oracle, the ON DELETE clause is used to specify the actions that will automatically be undertaken if a parent row is deleted. Additionally, SQL Server has the ON UPDATE clause for handling updates to the parent rows.
Table 6.16 shows the availability of various control actions using these clauses in Oracle and SQL Server:
Table 6.16: Functionality Available in Oracle and SQL Server with Respect to Foreign Key Constraint
Operation
Action
Oracle
SQL Server
ON DELETE
SET NULL
Yes
No
ON DELETE
CASCADE
Yes
Yes
ON DELETE
NO ACTION
Yes
Yes
ON UPDATE
CASCADE
No
Yes
ON UPDATE
NO ACTION
No
Yes
Note In Oracle, the default of NO ACTION (restrict) is assumed only by the absence of an ON DELETE { SET NULL | CASCADE } clause in the definition. In SQL Server, the default is NO ACTION for both operations. To mimic the action of SET NULL, it has to be handled programmatically in the application using an appropriate SQL statement.
Foreign key constraints can be created in SQL Server using Enterprise Manager and T-SQL.
To create a foreign key constraint using Enterprise Manager, follow these steps:
Right-click the table name on the table list, click Design Table, and then click Table and Index Properties.
Select the Relationships tab and click New.
A new relationship is created with a default name and current table selected in the Foreign key table drop-down list. Type in or select (from drop-down list) the appropriate columns involved in the current table (child table). Also select the appropriate table for the Parent key table and its parent columns.
Select appropriate behaviors for the trigger in the check boxes at the bottom of the screen. The important behaviors are Check existing data on creation and Enforce relationship for INSERTs and UPDATEs.
To create a foreign key constraint using the Database Diagram Wizard, follow these steps:
Expand Server, then Databases, then target database.
Right-click Diagrams and click New Database Diagram.
The wizard will lead you through adding the tables to be included in the diagram. See Figure 6.7.
.gif)
Figure 6.7 Using Database Diagram Wizard for creating referentials
To add a relationship, select the foreign key column of the child table, and drag it to the primary or unique key column of the parent table.
A pop-up dialog will appear with columns filled in based on the columns selected in the drag and drop. The actions need not be precise because information such as name, primary key, and foreign key columns, can be modified before saving. See Figure 6.8.
Figure 6.8 Choosing the right relationship properties
To create a foreign key constraint using T-SQL, use the following syntax:
Table 6.17 contains the syntax to add foreign key constraints with the actions of ON DELETE and also the ON UPDATE statement, which does not exist in Oracle.
Table 6.17: SQL Statements for Defining FOREIGN KEY Constraints in Oracle SQL Server
Operation
Oracle
SQL Server
ON DELETE
CREATE TABLE table_name
(...,
column_name datatype,
...,
[ CONSTRAINT constraint_name ] FOREIGN KEY (column_name [,...] REFERENCES [schema.]ref_table_name (ref_column_name[, ...]) ON DELETE { SET NULL | CASCADE }
CREATE TABLE table_name
(...,
column_name datatype,
...,
[ CONSTRAINT constraint_name ] FOREIGN KEY (column_name [,...] REFERENCES ref_table_name (ref_column_name[, ...]) ON DELETE { CASCADE | NO ACTION}
ON UPDATE
CREATE TABLE table_name
(...,
column_name datatype,
...,
[ CONSTRAINT constraint_name ] FOREIGN KEY (column_name [,...] REFERENCES ref_table_name (ref_column_name[, ...]) ON UPDATE { CASCADE | NO ACTION}
Triggers
Triggers are used to enforce more complex business rules than can be enforced using constraints. Triggers are stored procedures that are implicitly executed when certain data modification (using data manipulation language statements, also known as DML) is performed against tables and views. In Oracle, there can be up to twelve combinations of trigger executions (actions) based on:
DML operation: INSERT, UPDATE, DELETE
Timing: BEFORE, AFTER
Level: ROW, STATEMENT
Table 6.18 evaluates SQL Server support for the Oracle trigger functionality.
Table 6.18: Functionality of Oracle Triggers Mapped to SQL Server
Trigger Feature
Oracle
SQL Server
DML – INSERT
Yes
Yes
DML – UPDATE
Column/Row
Row
DML – DELETE
Yes
Yes
Timing – BEFORE
Yes
Yes (INSTEAD OF)
Timing – AFTER
Yes
Yes
Level
Row/Statement
Row
Views – INSTEAD OF
Yes
Yes
Multiple triggers per action
Yes
Yes (first/last specified)
The SQL Server INSTEAD OF triggers are equivalent to Oracle’s BEFORE triggers. When migrating triggers from Oracle, the only drawback is that SQL Server does not support statement-level triggers. However, there are two pseudo-tables, inserted and deleted, that are populated during the trigger execution and which can be used to simulate statement-level operations. These inserted and deleted pseudo tables are similar to the :old and :new pseudo-rows in Oracle and are populated with all (multiple) rows affected by the trigger execution. The SQL Server deleted and inserted pseudo-tables are available by default and are not specified as part of the trigger definition. In
Oracle, the pseudo-rows have to be defined in the trigger using the REFERENCING clause.
Note This section is concerned only with creation of the trigger object. A detailed discussion on trigger logic (code) and its migration is found in Chapter 11, "Developing: Applications — Migrating Oracle SQL and PL/SQL" under the "Migrating the Data Access" section.
When multiple triggers exist on a table for the same action, Oracle does not guarantee any particular order of execution of multiple triggers. SQL Server has the capability to specify which trigger should be fired before (first) and after (last) all other triggers using the system stored procedure sp_settriggerorder. This feature has been found useful in eliminating some of the complexities in the code found in Oracle triggers to manage the sequence of events.
Triggers can be defined on tables either using Enterprise Manager or using T-SQL.
To create a trigger using Enterprise Manager, follow these steps:
In the Enterprise Manager, expand Server, then Databases, then target database, and then Tables.
Right-click the table name on the table list, click All Tasks, and then click Manage Triggers. Or, click the Triggers button in the Table Designer.
Type in the trigger text using the provided template.
Click Apply if more than one trigger is to be created, or OK to save all changes and close the dialog box.
To create a trigger using T-SQL, use the following syntax:
Table 6.19 compares the commonly used functionality of the trigger definition CREATE TRIGGER statement in Oracle and SQL Server.
Table 6.19: SQL Statements for Creating Triggers in Oracle and SQL Server
Oracle
SQL Server
CREATE TRIGGER trigger_name { BEFORE | AFTER | INSTEAD OF } { { INSERT [ OR ] | DELETE [ OR ] | UPDATE [ OF column_name [, ...] ] } } ON table_name REFERENCING [OLD AS :old] [NEW AS :new] [ FOR EACH ROW ] WHEN ( condition )Pl/sql_blockCREATE TRIGGER trigger_name ON table_name | view_name { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS [ { IF UPDATE ( column_name ) [ { AND | OR } UPDATE ( column_name ) ] [ ...n ] | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] } ]sql_statement [ ...n ] } }Indexes
There are two major categories of indexes in Oracle: B-tree indexes and Bitmap indexes. All other indexes are variations of these two basic types to provide additional features. Table 6.20 provides a quick comparison of the indexing schemes available in Oracle and SQL Server:
Table 6.20: Indexing Schemes Available in Oracle and SQL Server
Index Scheme
Oracle
SQL Server
B-tree — Unique
Yes
Yes
B-tree — Non-unique
Yes
Yes
B-tree — Composite
Yes (32 columns)
Yes (16 columns)
B-tree — Ascending
Yes
Yes
B-tree — Descending
Yes
Yes
B-tree — Cluster
Yes
No
B-tree — Reverse Key
Yes
No
B-tree — Key Compressed
Yes
No
B-tree — Function-based
Yes
No
B-tree — Index-Organized Table
Yes
Yes (Clustered)
B-tree — Partitioned
Yes
No
Bitmap
Yes (30 columns)
No
Bitmap — Join
Yes
No
B-Tree Indexes
SQL Server offers two types of indexes: clustered and nonclustered. Both these types are based on the B-tree data structure. Hence assumptions can be drawn as to their performance in the two systems. An important difference between the two implementations is Oracle does not index rows when all the key columns have null values in them, while SQL Server does.
Both Oracle and SQL Server support the following basic versions of the B-tree index:
Composite. Oracle allows indexes with up to 32 columns, whereas SQL Server allows up to 16 columns. While indexes with 16 or more columns are rare, the number of columns will have to be trimmed down if such indexes were to be migrated to SQL Server.
Unique. Unique indexes are used to enforce PRIMARY KEY and UNIQUE constraints. Even though nulls cannot be compared (nor considered equal) for sake of uniqueness, when two rows are identical, null values are considered as identical. In SQL Server, primary key constraints cannot have null values, whereas unique indexes allow one row with null value. For more information, refer to the "Unique Constraints" section earlier in this chapter.
Non-unique. This is the basic form of the B-tree or nonclustered index, where key values can be repeated.
Ascending. The key values are sorted and stored in ascending order.
Descending. The key values are sorted and stored in descending order.
The following list provides variations of the B-tree index, implemented with a modification to the basic B-tree structure, that provide a specific feature:
Cluster indexes. Such indexes can be found in Oracle on clustered tables (clusters). Clustered tables are not supported by SQL Server and neither are cluster indexes.
Index-organized tables. The clustered indexes in SQL Server are similar to the index organized tables. Refer to the discussion under the "Tables" section earlier in this chapter.
Reverse key indexes. Oracle developed these indexes to reduce contention for index blocks by indexes on columns that have sequential values that are written almost simultaneously. This is achieved by reversing the bytes in the key value to produce non-sequential numbers. This indexing scheme is not available in SQL Server. The reverse key index is useful in Oracle Real Application Cluster (RAC) implementations, and there is no disadvantage in migrating such indexes to clustered or nonclustered indexes.
Partitioned indexes. In Oracle, B-tree indexes can be created that are local to the partition or global to the entire table (partitioned or non- partitioned). This feature is not available in SQL Server and should be replaced by clustered or nonclustered indexes.
Key compressed index. In key compression, the leading subsets of a key can be compressed in a manner similar to clustering by storing the leading subset only once for repeating values. SQL Server does not support key compression.
Function-based index. In Oracle, expressions in the WHERE clause that contain functions do not use indexes as an access path. To overcome this disadvantage, indexes can be created by applying the function to compute the value of the expression and storing it in an index. For example, if the column part_name is accessed using the expression UPPER(part_name), a function-based index can be defined on UPPER(part_name). SQL Server does not support function-based indexes. There is no other index type in SQL Server that can provide similar functionality.
Bitmap Index
The bitmap indexes are specially designed for improving the retrieval of data based on columns with very low cardinality. Because maintenance of the bitmap indexes is very expensive, their utility is restricted to Data Warehouses and DSS systems where there is very low or zero update activity.
Bitmap indexes are not available in SQL Server and cannot be substituted with B-tree indexes.
In SQL Server and versions of Oracle before 9i, indexes are used only when the leading subset of key values are involved. In Oracle 9i, Oracle introduced the concept of fast index scan, whereby the entire index is scanned for matching values in the non-leading keys by skipping the leading key columns. In SQL Server, additional indexes have to be created for the non-leading columns to provide access paths similar to Oracle. This requirement is not documented inside the database and is dependent on the SQL statements used in applications. These situations can only be discovered by profiling the application during testing.
The various types of indexes can be created using Enterprise Manager, the Create Index Wizard, or T-SQL.
To create an index using Enterprise Manager, follow these steps:
The steps for creating an index are very similar to that described for UNIQUE key. Creation of a non-unique, nonclustered index is demonstrated here.
Expand Server, then Databases, then target database, and then Tables.
Right-click the table name on the table list, click Design Table, and then click Table and Index Properties.
Select the Indexes/Keys tab and click New.
Enter the name of the new index in the Index name text box. Insert the columns or the index in grid along with the required sort order for the column.
Uncheck the Create UNIQUE check box to specify a non-unique index.
Select the target filegroup from the Index Filegroup drop-down list.
Click the desired value for Fill factor (equivalent of PCTFREE).
To create an index using the Create Index Wizard, follow these steps:
Expand a server group, and then expand the server in which to create the index.
On the Tools menu, click Wizards.
Expand Database.
Double-click Create Index Wizard.
Complete the steps in the wizard
To create an index using T-SQL, use the following syntax:
Table 6.21 shows the syntax of the CREATE INDEX statement for the features and functionality discussed here.
Table 6.21: SQL statements available for Creating Indexes in Oracle and SQL Server
Oracle
SQL Server
CREATE [UNIQUE | BITMAP ]
INDEX index_name
ON table_name ( column_name
[ ASC | DESC ] [, ...] )
[ physical_attributes ]
[ { COMPRESS | NOCOMPRESS } prefix_length ]
[ REVERSE ]
[ TABLESPACE tablespace_name ]
[ storage_description ]
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON table_name ( column_name
[ ASC | DESC ] [, ... ] )
[ WITH index_options ]
[ ON filegroup ]
Views
Views are used in Oracle and SQL Server to hide query complexity and encourage query reuse. Both Oracle and SQL Server have added features, such as updateability and indexes to views, to improve their utility and performance.
Table 6.22 compares the availability of the various types of views in Oracle and SQL Server.
Table 6.22: Types of Views Available in Oracle and SQL Server
View Type
Oracle
SQL Server
Simple Views
Yes
Yes
Join Views
Yes
Yes
Partitioned Views
Yes
Yes
Read-only Views
Yes
No
Updateable Views
Yes
Yes
Inline Views
Yes
Yes
Object Views
Yes
No
The following are the various types of views available in Oracle and the support for them in SQL Server:
Simple views. This view query is based on a single table and their migration is trivial.
Join views. This view query is based on more than one table. The join views available in SQL Server are less restrictive than their Oracle counterparts in their use of aggregate functions in the query. Hence migrating them from Oracle to SQL Server should not pose any problems. When a view is defined with an outer join and is queried with a qualification on a column from the inner table of the outer join, the results from SQL Server and Oracle can differ.
Partitioned views. This view query provides a union of partitions of data in different tables (regular non-partitioned tables). Use of partitioned views was common in Oracle before the introduction of partitioned tables in Oracle 8. SQL Server supports the entire range of features that are available in Oracle with respect to partitioned views.
Read-only views. This view query with the WITH READ ONLY clause specified to curb update activity against the base tables of the view. SQL Server does not have an equivalent for this feature. When migrating a read-only view, care has to be taken to ensure that only SELECT privileges are granted to the users.
Updatable views. These are simple, join, or partitioned views against which DML statements can be executed, subject to certain restrictions. In SQL Server, the restrictions can be bypassed (except the use of aggregate functions) by defining INSTEAD OF triggers on the views. The WITH CHECK OPTION clause prevents modifications to data that violate the criteria in the WHERE clause of the view query. DML statements on join views can modify data only in one base table.
Inline views. An inline view is a subquery that is used like a view in the FROM clause of SQL statements. SQL Server fully supports their definition.
Object views. Object views are virtual object tables that can be used to manipulate object data types as well as relational data cast as objects. Object views are not available in SQL Server and should be recreated (flattened) as regular views.
Indexed views. Oracle does not support creation of indexes on views. However, UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints can be defined on views which indirectly create indexes. These constraints are a subset of those available with tables and work similarly. SQL Server allows creation of indexes. However the first index has to be a unique clustered index. Additional nonclustered indexes can then be created. Thus, SQL Server can support the migration of indexed views.
Views can be created using Enterprise Manager, the Create View Wizard, or T-SQL.
To create a view using Enterprise Manager, follow these steps:
Open Enterprise Manager, expand Server, then Databases, and then the target database.
Right-click Views and click New View.
The view can be created using the diagram and grid panes (top two areas) or by entering the view query in the SQL pane (SELECT area). See Figure 6.9.
.gif)
Figure 6.9 Using the View Designer of Enterprise Manager
To create a view using the Create View Wizard, follow these steps:
Expand a server group and then expand the server in which to create the view.
On the Tools menu, click Wizards.
Expand Database.
Double-click Create View Wizard.
Complete the steps in the wizard.
To create a view using T-SQL, use the following syntax:
Table 6.23 offers a comparison of the CREATE VIEW statements in Oracle and SQL Server.
Table 6.23: SQL Statement for Creating Views in Oracle and SQL Server
Oracle
SQL Server
CREATE [OR REPLACE] [FORCE |
NO FORCE] VIEW [schema.]view_name
[ ( column_alias [ inline_constraint ] [,...] [,
out_of_line_constraint ] ) ]
AS select_statement
[ WITH READ ONLY ]
[ [ WITH CHECK OPTION ] [ CONSTRAINT constraint_name ] ]
CREATE VIEW [owner.]view_name
[ ( column_name [,...] ) ]
AS select_statement
[ WITH CHECK OPTION ]
In Oracle, views can be created without permissions on the base objects, or even without the base objects existing, by using the FORCE keyword. This is not allowed in SQL Server. SQL Server also does not allow ORDER BY and GROUP BY clauses in the view definition.
Note When a view is created in Oracle using an asterisk (*) in the SELECT clause, the asterisk is expanded to the actual column names in the definition of the view. SQL Server retains the exact definition used in the DDL. Oracle does the same thing when an asterisk (*) is not used. While converting from Oracle to SQL Server, the view definition may be changed back to an asterisk instead of specifying every column.
Stored Programs
Oracle and SQL Server have the capability to store complex business logic (beyond constraints) inside the database. The code to support such requirements cannot be provided by SQL because it cannot execute commands based on logical conditions. It also fails to support looping operations. In spite of recent extensions to SQL, such as incorporation of a CASE expression, it is still difficult to perform more than one operation based on a logical condition using these methods. The need to implement control structures and programming constructs cannot be met with SQL alone. Hence Oracle and SQL Server both offer procedural extensions to SQL, PL/SQL, and Transact-SQL (T-SQL), respectively, that offer a more complete environment for defining stored programs (or subprograms).
Stored programs provide modularity (top-down design), encapsulation (logic is hidden), abstraction (black box approach), security (only execute on subprogram), and extensibility (user-defined functionality). Additionally, existence inside the database promotes accessibility (available to all users), reuse (available to all database users), speed of execution (stored programs are in compiled form), performance (handling of large amounts of data close to its source), and reduced resource requirements (reduces network bandwidth, memory, and so on because only the results are transported).
In Oracle, Java can be used to write native stored procedures and user-defined functions. A PL/SQL procedure can also call external procedures or functions written in the C programming language and stored in a shared library.
In Oracle, there are four types of stored programs: functions, procedures, packages, and triggers. These objects are very similar in construction, functionality, and usage to those used in SQL Server. Hence the migration of their structures is trivial. However, because of the vast difference between the syntax in the languages (PL/SQL and T-SQL) used in these objects, the migration of the embedded code is far from trivial.
Triggers have already been discussed in this chapter. Functions, procedures, and packages are covered here.
Oracle ensures the purity of the actions that are performed inside stored programs. Hence there are no hidden side effects when migrated to SQL Server and you can concentrate on reproducing the logic using T-SQL.
Here are a few points that concern all types of stored programs:
Overloading. SQL Server does not support overloading, and such functions and procedures will have to be recreated using unique names.
Execution privileges. Oracle stored programs can be defined to execute with definer-rights (owner) or invoker-rights (user). SQL Server only uses invoker-rights.
Oracle stored programs that are built on definer-rights architecture can be converted to SQL Server invoker-rights subprograms by properly qualifying objects inside stored programs. With invoker rights, the schema name could also be passed in and SQL dynamically constructed.
Parameters. Both Oracle and SQL Server support positional, named, or mixed notation for parameters. SQL Server can handle all the types of parameters that are found in Oracle, including tables.
Oracle has three parameter modes: IN, OUT, and IN OUT. SQL Server does not allow the IN OUT type of parameters, and this type warrants some rework in the application design when migrating. Hence IN OUT parameters will have to be replaced with two separate IN and OUT parameters. The keyword OUTPUT is used in place of OUT, while the keyword IN does not exist and is implied because it is the default mode. Also, even though it is not a good practice, there is no restriction on the usage of OUT and IN OUT parameters in Oracle functions. Violations of this rule should be trapped and corrected during migration.
In Oracle and SQL Server, IN parameters can be given default values. If such parameters are skipped during a call, the default value is applied. In SQL Server, this rule holds for stored procedures but not for functions. With functions, the keyword "default" has to be used in the call to the function and cannot be skipped as with stored procedures (which imply the use of the default value).
SQL Server parameters are prefixed by the at (@) symbol.
Using same name as system. If the first three characters of the procedure name are sp_, SQL Server searches the master database for the procedure. If no qualified procedure name is provided, SQL Server searches for the procedure as if the owner name is dbo. To resolve the stored procedure name as a user-defined stored procedure with the same name as a system stored procedure, provide the fully qualified procedure name.
Only the migration of the object structure is provided in this chapter. Refer to Chapter 11, "Developing: Applications — Oracle SQL and PL/SQL," for a detailed discussion about migrating the embedded PL/SQL code to T-SQL.
Functions
While the system provides several built-in functions, Oracle and SQL Server provide the capability to define custom functions called user-defined functions. These user-defined extensions to SQL always return a result. They are employed primarily in SQL statements.
Functions can be created using Enterprise Manager or T-SQL.
To create a function using Enterprise Manager, follow these steps:
Expand a server group, and then expand a server.
Expand Databases, and then expand the database in which to create the function.
Right-click User Defined Functions and select New User Defined Function.
In the Text box, enter the text of the function. Use TAB to indent the text of a function.
To check the syntax, click Check Syntax and OK to create.
To create a function using T-SQL, use the following syntax:
Functions can be created in SQL Server using the CREATE FUNCTION statement, which is very similar to the one found in Oracle, as show in Table 6.24.
Table 6.24: SQL Statements for Creating Triggers in Oracle and SQL Server
Oracle
SQL Server
CREATE OR REPLACE FUNCTION [schema.]function_name [ ({parameter [IN] datatype [= default]} [, ...]) ] RETURN scalar_expression {IS | AS}variable_declaration BEGIN statements RETURN scalar_expression EXCEPTION statements END;CREATE FUNCTION [owner.] function_name [ ({@parameter [AS] datatype [ = default ]} [, ...]) ] RETURN scalar_expression [ AS ] BEGIN variable_declaration statements RETURN scalar_expression ENDStored Procedures
SQL Server has stored procedures that closely resemble Oracle. These stored procedures provide the capability to pass multiple parameters back to the calling environment. Another advantage of stored procedures is that they can perform actions in the database without being tied to a SQL statement.
Stored procedures can be created using the Create Stored Procedure Wizard, Enterprise Manager, or T-SQL.
To create a view using the Create Stored Procedure Wizard, follow these steps:
Expand a server group and then expand the server in which to create the view.
On the Tools menu, click Wizards.
Expand Database.
Double-click Create Stored Procedure Wizard.
Complete the steps in the wizard.
To create a stored procedure using Enterprise Manager, follow these steps:
Expand a server group, and then expand a server.
Expand Databases, and then expand the database in which to create the stored procedure.
Right-click Stored Procedures and select New Stored Procedure.
In the Text box, enter the text of the stored procedure. Use TAB to indent the text within.
To check the syntax, click Check Syntax and OK to create.
To create a stored procedure using T-SQL, use the following syntax:
Both Oracle and SQL Server offer the CREATE PROCEDURE statement, which is very similar in structure and is shown in Table 6.25.
Table 6.25: SQL Statements for Creating Procedures in Oracle and SQL Server
Oracle
SQL Server
CREATE OR REPLACE PROCEDURE [schema.]procedure_name [ ({parameter [IN|OUT|IN OUT] datatype [= default]} [, ...]) ] {IS | AS}variable_declaration BEGIN statements EXCEPTION statements END;CREATE PROCEDURE [owner.] procedure_name [ ({@parameter [AS] datatype [ = default ] [OUTPUT]} [, ...]) ] AS BEGIN variable_declaration statements ENDPackages
One of the main differentiators between stored procedures and packages is the capability to declare variables in the package header that act as global variables. SQL Server does not have packages, and these have to be replaced with stored procedures. However, SQL Server allows the nesting of procedures, and this capability can be used to simulate the structure of packages by nesting functions and procedures. Variables can be made available to all the nested functions and procedures by declaring them in the outmost (or main) procedure. This, however, does not provide database-wide accessibility to these variables, a shortcoming which has to be accommodated by migrating the global variable from the package to the application code.
Solutions for Objects not Found in SQL Server
Some Oracle objects, such as synonyms and sequences, are widely used in Oracle applications. However, there is no equivalent object with similar functionality in SQL Server. Finding solutions to implement the logic and functionality around these objects in SQL Server is critical to the migration.
Synonyms
In Oracle, synonyms are aliases for tables, views, sequences, functions, procedures, and packages. The primary purpose of using synonyms is for location transparency and naming transparency. By creating public synonyms or private synonyms (for each user),
applications can be written without schema qualification in the SQL code because the qualifier is defined in the synonyms. The original object names can also be hidden.
Synonyms for tables are more easily migrated by substituting them with views. Views provide the required location and naming transparency as well. For example, a private synonym, CLINTB.DEPT, which connects to the table HR.DEPARTMENT, can be replaced in SQL Server by a VIEW as follows:
CREATE VIEW CLINTB.DEPT AS SELECT * FROM HR.DEPARTMENT
One of the known drawbacks when creating SELECT * views in Oracle is that any changes that are made to the base table definition in terms of adding, dropping, and renaming columns are not automatically reflected in the view definition. This is because Oracle converts the query piece:
SELECT * FROM table_name
into
SELECT column_name [, ...] FROM table_name
Consequently, when a column is added to the base table, the view definition does not change. Dropping or renaming a column invalidates the view. However, SQL Server does not have this problem because the view DDL is stored as it was written, that is, SELECT * FROM table_name.
It is not possible to find an object-based solution for synonyms connecting to other types of objects, such as procedures and sequences. The simplest solution is to qualify the object schema or owner in the application rather than resolve it at the database login level.
The inherent quality of SQL Server in resolution of objects can be used for providing a solution. Synonyms are usually employed in situations where there are multiple versions of the same application being used to support different clients, with each client having its own schema. The schema that will be used is driven by the user login and not the application itself. This can be achieved in SQL Server by setting the default database for a user account and giving the login the appropriate privileges at the object level. This scheme will work as long as all the objects that will be accessed are in a single database and it is set as the default database for the user. For more information about these issues, refer to Chapter 7, "Developing: Databases — Migrating the Database Users."
Sequences
In Oracle, sequences are autonomous schema objects that can be used to generate numbers that are in sequential order. In applications, sequences are commonly used to produce artificial keys for tables which do not have a natural key, or the natural key is too long. SQL Server does not have an equivalent for sequences. However, a SQL Server table can have columns that are defined with the IDENTITY property, which simulates the behavior of Oracle sequences. The IDENTITY column is an Oracle sequence and table column rolled into one, that is, the column definition includes the sequence definition.
The syntax of the SQL Server IDENTITY property is
IDENTITY [(seed, increment)]
The Oracle
start with
is replaced by the SQL Server FakePre-c1e465c74fb9481c989ae3486055efe0-117790aed99b42118d364f0ee6fd9b45
The Oracle
increment
FakePre-000893924af74732bd98f3cd661f36ef-1391662598464f19bbdb84efdc359c7b is equivalent to the SQL Server FakePre-179522ce0c6c40edae5df272decd82ae-2c9b5a2092b44c42b7b473483eba32f1
The Oracle
maxvalue
is provided by the SQL Server column data type.
Table 6.26 shows the implementation of the IDENTITY property feature in SQL Server and what would be the equivalent in Oracle.
Table 6.26: Implementation of Oracle Sequence in SQL Server
Oracle
SQL Server
CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1
-
CREATE TABLE table_name (column1 NUMBER DEFAULT sequence_name.NEXTVAl,
column2 ...
);
INSERT INTO table_name (column2, ...) VALUES ( ... )
CREATE TABLE table_name (column1 NUMBER IDENTITY(1,1),
column2 ...
);
INSERT INTO table_name (column2, ...) VALUES ( ... )
CREATE TABLE table_name (column1 NUMBER,
column2 ...
);
INSERT INTO table_name (column1, column2, ...) VALUES (sequence_name.NEXTVAL, ... )
CREATE TABLE table_name (column1 NUMBER IDENTITY(1,1),
column2 ...
);
INSERT INTO table_name (column2, ...) VALUES ( ... )
One of the advantages of sequences being independent objects is that they can be used to supply a unique identifier across multiple tables. This property of Oracle sequences is not inherent in SQL Server’s IDENTITY property. If an identifier is required to be unique across tables (or even databases across the globe), use the uniqueidentifier data type, which can be populated using the NEWID() function. The IDENTITY column can only handle unique values and hence it cannot support the cycle feature of Oracle sequence. A maximum value also cannot be set but can be enforced by choosing an appropriate sized data type for the IDENTITY column.
Note There is no sufficient information inside the Oracle database to figure out how and where a sequence is used. Hence the application's documentation, code, or developer will have to be consulted to learn which sequence is used to populate a column. This information is then used to define an IDENTITY column in that table.
Database Links
Oracle database links are used to form a bridge between databases (instances). Remote and distributed transactions can be executed against the remote database using the database link. SQL Server provides similar functionality under the linked server.
Table 6.27 compares some of the characteristics of database links in Oracle and SQL Server.
Table 6.27: Comparison of Database Links in Oracle and SQL Server
Attribute
Oracle
SQL Server
Link Types
Public and Private
Public
Database Types
Homogeneous
Homogeneous and Heterogeneous
CREATE [ PUBLIC ] DATABASE LINK [ CONNECT TO { CURRENT_USER | user IDENTIFIED BY password } ] USING 'connect_string'
sp_addlinkedserver [ @server = ] 'server'
[ , [ @srvproduct = ] 'product_name' ]
[ , [ @provider = ] 'provider_name' ]
[ , [ @datasrc = ] 'data_source' ]
[ , [ @location = ] 'location' ]
[ , [ @provstr = ] 'provider_string' ]
[ , [ @catalog = ] 'catalog'
Note The provider_name for an OLE DB-based connection to another SQL Server is 'SQLOLEDB'.
The following example shows the use of sp_addlinkedserver to create a linked server to connect to another database for executing distributed queries:
sp_addlinkedserver @server='CORP2', @srvproduct='', @provider='SQLOLEDB', @datasrc='SHUTTLE\CORP2'
Sample Schema Object Migration
The following example illustrates the schema owner and schema objects migration task of the database migration. The HR schema has been used in this example.
Create the SQL Server login account (database authenticated).
The following commands create a database-authenticated login with name HR.
Use MASTER
EXEC sp_addlogin
@loginame='HR', @passwd='HR', @defdb='HRDATA'Create user account in the database.
The following commands create a user account for the HR login in the HRDATA database in which objects will be created.
Use HRDATA
EXEC sp_grantdbaccess @loginame='HR', @name_in_db='HR'
Grant privileges to create objects in the database.
Privileges can be granted to the schema owner to create objects in a database using either the CREATE permissions or the db_owner fixed database role.
Grant complete authority on the database.
The following command can be used to grant the db_owner role:
EXEC sp_addrolemember
@rolename='db_owner', @membername='HR'
2. Grant privileges to create objects only. The following commands can be executed to only grant permission to create objects: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">GRANT CREATE FUNCTION TO HRGRANT CREATE PROCEDURE TO HR GRANT CREATE TABLE TO HR GRANT CREATE VIEW TO HR
Create sequences.
While sequences cannot be migrated to SQL Server, information about the sequences in the Oracle database should be extracted first. Because sequences are migrated to IDENTITY columns in tables, the information about sequences is required to appropriately define tables (in the next step).
The following command extracts the required information on sequences.
SELECT sequence_name, min_value, max_value, increment_by
FROM dba_sequences WHERE sequence_owner = 'HR'
The following output is obtained: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BYDEPARTMENTS_SEQ 1 9990 10 EMPLOYEES_SEQ 1 999999999999999999999999999 1 LOCATIONS_SEQ 1 9900 100
The three sequences in the HR schema are assumed to be used to populate the columns DEPARTMENTS.DEPARTMENT\_ID, EMPLOYEES.EMPLOYEE\_ID and LOCATIONS.LOCATION\_ID, respectively.Create tables.
There are two options for how to migrate tables. Option one is to create tables in a specific order based on the foreign key constraints (parent tables first). Option two is to create tables in any order, leaving out the foreign key constraints and then adding them after all the tables have been created.
Obtain a list of tables.
If option one is followed, the dependencies between tables and the right order in which to create the tables is required. There are several scripts available (mostly PL/SQL-based) to produce such a list. Tables without any dependencies form the first level, and other levels are formed based on the nesting of the dependencies.
Note None of the available scripts will work when there are interdependencies between tables that form a closed loop. The following script shows such circular dependencies that make these scripts not viable.
SELECT table_name parent_table_name, NULL child_table_name
FROM dba_tables ut WHERE owner = 'HR' AND NOT EXISTS (SELECT 'x' FROM user_constraints uc WHERE constraint_type = 'R' AND ut.table_name = uc.table_name) UNION ALL SELECT p.table_name parent_table_name, c.table_name child_table_name FROM dba_constraints p, dba_constraints c WHERE p.owner = c.owner AND p.owner = 'HR' AND p.constraint_type IN ('P','U') AND c.constraint_type = 'R' AND p.constraint_name = c.r_constraint_name
**Note** In the following output there is a parent-child relationship between EMPLOYEES and DEPARTMENTS in both directions that creates the loop. <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">PARENT_TABLE_NAME CHILD_TABLE_NAMEJOBS REGIONS REGIONS COUNTRIES LOCATIONS DEPARTMENTS EMPLOYEES DEPARTMENTS DEPARTMENTS EMPLOYEES JOBS EMPLOYEES EMPLOYEES EMPLOYEES JOBS JOB_HISTORY EMPLOYEES JOB_HISTORY DEPARTMENTS JOB_HISTORY COUNTRIES LOCATIONS
To implement option two, first obtain a list of all tables that need to be migrated using the following command: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT table_name, tablespace_name, iot_typeFROM dba_tables WHERE owner = 'HR' The output obtained is: TABLE_NAME TABLESPACE_NAME IOT_TYPE COUNTRIES IOT DEPARTMENTS EXAMPLE EMPLOYEES EXAMPLE JOBS EXAMPLE JOB_HISTORY EXAMPLE LOCATIONS EXAMPLE REGIONS EXAMPLE
2. Extract DDL along with constraints. There are several scripts and tools that can be used to extract the DDL for the table object. For an Oracle 9i database, the Oracle-supplied package DBMS\_METADATA can be used. First, execute the following in SQL\*Plus to define the output style. <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">set pagesize 0set long 10000 EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE); EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', TRUE); EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE); EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS', TRUE); EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS', TRUE); EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE);
Then execute DBMS\_METADATA.get\_ddl for each of the tables. The following two examples show how to extract the DDL for entire tables using this procedure: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT dbms_metadata.get_ddl('TABLE', 'COUNTRIES', 'HR')FROM DUAL; The output is as follows: CREATE TABLE "HR"."COUNTRIES" ("COUNTRY_ID" CHAR(2) CONSTRAINT "COUNTRY_ID_NN" NOT NULL ENABLE, "COUNTRY_NAME" VARCHAR2(40), "REGION_ID" NUMBER, CONSTRAINT "COUNTRY_C_ID_PK" PRIMARY KEY ("COUNTRY_ID") ENABLE ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING TABLESPACE "EXAMPLE" PCTTHRESHOLD 50; ALTER TABLE "HR"."COUNTRIES" ADD CONSTRAINT "COUNTR_REG_FK" FOREIGN KEY ("REGION_ID") REFERENCES "HR"."REGIONS" ("REGION_ID") ENABLE;
Execute the following command to obtain the definition of the LOCATIONS table. <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT dbms_metadata.get_ddl('TABLE', 'LOCATIONS', 'HR') FROM DUAL;
The output returned is: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">CREATE TABLE "HR"."LOCATIONS"("LOCATION_ID" NUMBER(4,0), "STREET_ADDRESS" VARCHAR2(40), "POSTAL_CODE" VARCHAR2(12), "CITY" VARCHAR2(30) CONSTRAINT "LOC_CITY_NN" NOT NULL ENABLE, "STATE_PROVINCE" VARCHAR2(25), "COUNTRY_ID" CHAR(2) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "EXAMPLE" ; CREATE UNIQUE INDEX "HR"."LOC_ID_PK" ON "HR"."LOCATIONS" ("LOCATION_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING TABLESPACE "INDX" ; ALTER TABLE "HR"."LOCATIONS" ADD CONSTRAINT "LOC_ID_PK" PRIMARY KEY ("LOCATION_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING TABLESPACE "INDX" ENABLE; ALTER TABLE "HR"."LOCATIONS" ADD CONSTRAINT "LOC_C_ID_FK" FOREIGN KEY ("COUNTRY_ID") REFERENCES "HR"."COUNTRIES" ("COUNTRY_ID") ENABLE;
3. Convert the extracted Oracle DDL to SQL Server syntax. In this step, the table DDL is converted along with constraint and index definitions. Foreign key constraints are handled separately in the next step. Create clustered indexes before creating any non-clustered indexes. If clustered indexes are planned for a table, defer creation of nonclustered indexes (as part of PRIMARY KEY or UNIQUE constraint definition) during the table creation in this step. The first table in the example, COUNTRIES, is an index-organized table. For SQL Server, it is converted into a heap with a clustered index on the primary key, COUNTRY\_ID. **Note** In Oracle, there is a single datatype of the number variety which is constrained with appropriate scale. While converting to SQL Server, the data type which is closest in scale needs to be used, as has been demonstrated in the example. The following commands are used to create the COUNTRIES and LOCATIONS tables respectively. <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">CREATE TABLE HR.COUNTRIES(COUNTRY_ID CHAR(2) CONSTRAINT NN_COUNTRY_ID NOT NULL, COUNTRY_NAME VARCHAR(40), REGION_ID NUMERIC, CONSTRAINT PK_COUNTRY_C_ID PRIMARY KEY CLUSTERED (COUNTRY_ID) ON HR_INDX ) CREATE TABLE HR.LOCATIONS (LOCATION_ID SMALLINT NOT NULL, STREET_ADDRESS VARCHAR (40), POSTAL_CODE VARCHAR(12), CITY VARCHAR(30) CONSTRAINT NN_LOC_CITY NOT NULL, STATE_PROVINCE VARCHAR(25), COUNTRY_ID CHAR(2) ) ALTER TABLE HR.LOCATIONS ADD CONSTRAINT PK__ID PRIMARY KEY NONCLUSTERED (LOCATION_ID) ON HR_INDX
4. Create the foreign key constraints. After all the tables have been created, the foreign key constraints can be created in any order. The following commands create foreign keys on the COUNTRIES and LOCATIONS tables. <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">ALTER TABLE HR.COUNTRIES ADD CONSTRAINTFK_COUNTR_REG FOREIGN KEY (REGION_ID) REFERENCES HR.REGIONS (REGION_ID); ALTER TABLE HR.LOCATIONS ADD CONSTRAINT FK_LOC_C_ID FOREIGN KEY (COUNTRY_ID) REFERENCES HR.COUNTRIES (COUNTRY_ID);
5. Extract and migrate the table comments. The following command can be used to extract comments in tables in the HR schema. <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT table_name, commentsFROM dba_tab_comments WHERE owner = 'HR' AND comments IS NOT NULL
The output returned contains the comments for the tables in the HR schema, such as COUNTRIES, DEPARTMENTS, EMPLOYEES, JOBS, JOB\_HISTORY and LOCATIONS. The output can then be copied into the statements for adding comments to tables in SQL Server. The following is an example of the use of the **sp\_addextendedproperty** procedure to add a comment on the COUNTRIES table. <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">sp_addextendedproperty 'comment', 'country table. Contains 25 rows. References with locations table.', 'user', HR, 'table', countries</pre> 6. Extract and migrate the column comments: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT table_name, column_name, commentsFROM dba_col_comments WHERE owner = 'HR' AND comments IS NOT NULL
The output contains the column level comments. The following is an example of the use of the **sp\_addextendedproperty** procedure to add a comment on the REGION\_ID column of the COUNTRIES table. <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">sp_addextendedproperty 'comment', 'Region ID for the country. Foreign key to region_id column in the departments table.' , 'user', hr, 'table', countries, 'column', region_id</pre>Create triggers.
This step demonstrates how to migrate the table triggers from Oracle to SQL Server. The logic (code) that is embedded in the triggers is not discussed here. For more information, refer to the "Migrating the Data Access" section in Chapter 11, "Developing: Databases — Migrating Oracle SQL and PL/SQL."
SELECT table_name, 'CREATE TRIGGER ' || description header
FROM dba_triggers WHERE owner = table_owner AND table_owner = 'HR' AND base_object_type = 'TABLE' TABLE_NAME HEADER EMPLOYEES CREATE TRIGGER secure_employees BEFORE INSERT OR UPDATE OR DELETE ON employees EMPLOYEES CREATE TRIGGER update_job_history AFTER UPDATE OF job_id, department_id ON employees FOR EACH ROW
The equivalent syntax for SQL Server can be generated with the aid of Table 6.24. The commands for creating the secure\_employees and update\_job\_history triggers will convert as: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">CREATE TRIGGER secure_employeesON employees INSTEAD OF INSERT, UPDATE, DELETE AS ... GO CREATE TRIGGER update_job_history ON employees AFTER UPDATE AS IF (UPDATE(job_id) or UPDATE(department_id)) ... GO
Create indexes.
All indexes related to constraints should have been created in Substep 5.b. This step covers the indexes that are not related to any constraints.
The following query can be used to retrieve information on indexes in Oracle that are not related to any constraints. This query will work only in Oracle 9i because the data dictionary before this release of Oracle does not have the columns used in this query.
SELECT i.table_name, i.index_name, c.column_name, i.uniqueness,
i.tablespace_name, i.pct_free FROM dba_indexes i, dba_ind_columns c WHERE i.table_name = c.table_name AND i.index_name = c.index_name AND i.owner = c.index_owner AND i.owner = 'HR' AND (i.owner, i.index_name) NOT IN (SELECT index_owner, index_name FROM dba_constraints WHERE owner = 'HR' AND index_name IS NOT NULL) ORDER BY i.table_name, i.index_name, c.column_position
The output obtained has been formatted as follows: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">TABLE_NAME INDEX_NAME COLUMN_NAME UNIQUENESS TABLESPACE_NAME PCT_FREEDEPARTMENTSDEPT_LOCATION_IX LOCATION_ID NONUNIQUE INDX 10 EMPLOYEES EMP_DEPARTMENT_IX DEPARTMENT_ID NONUNIQUE INDX 10 EMPLOYEES EMP-JOB-IX JOB_ID NONUNIQUE INDX 10 EMPLOYEES EMP_MANAGER_IX MANAGER_ID NONUNIQUE INDX 10 EMPLOYEES EMP_NAME_IX LAST_NAME NONUNIQUE INDX 10 EMPLOYEES EMP_NAME_IX FIRST_NAME NONUNIQUE INDX 10 JOB_HISTORYJHIST_DEPARTMENT_IX DEPARTMENT_ID NONUNIQUE INDX 10 JOB_HISTORYJHIST_EMPLOYEE_IX EMPLOYEE_ID NONUNIQUE INDX 10 JOB_HISTORYJHIST_JOB_IX JOB_ID NONUNIQUE INDX 10 LOCATIONS LOC_CITY_IX CITY NONUNIQUE INDX 10 LOCATIONS LOC_COUNTRY_IX COUNTRY_ID NONUNIQUE INDX 10 LOCATIONS LOC_STATE_PROVINCE_IX STATE_PROVINCE NONUNIQUE INDX 10
This information can be used to create indexes in SQL Server as follows: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">CREATE NONCLUSTERED INDEX hr.dept_location_ixON hr.departments(location_id) WITH FILLFACTOR = 90 ON HR_INDX CREATE NONCLUSTERED INDEX hr.emp_name_ix ON hr.employees(last_name, first_name) WITH FILLFACTOR = 90 ON HR_INDX
Because indexes have been created over several steps, you should verify that an equivalent index has been created in SQL Server for each index in Oracle. In Oracle, generate a listing of all indexes using the following command: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT i.table_name, i.index_name,c.column_name, i.uniqueness, i.tablespace_name, i.pct_free FROM dba_indexes i, dba_ind_columns c WHERE i.table_name = c.table_name AND i.index_name = c.index_name AND i.owner = c.index_owner AND i.owner = 'HR' ORDER BY i.table_name, i.index_name, c.column_position
In SQL Server, execute the following command on each table with the table name substituted for name to retrieve information on the indexes available for the table: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">sp_helpindex [ @objname = ] 'name'</pre>Create views.
Extract the views and their definition from Oracle using the following command:
SELECT view_name, text
FROM dba_views WHERE owner = 'HR'
The output provides the view query. The query for DEPT is as follows: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">select "DEPARTMENT_ID", "DEPARTMENT_NAME", "MANAGER_ID", "LOCATION_D", "DEPT_LOC"from hr.departments
The query for EMB\_DETAILS\_VIEW is as follows: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECTe.employee_id, e.job_id, e.manager_id, e.department_id, d.location_id, l.country_id, e.first_name, e.last_name, e.salary, e.commission_pct, d.department_name, j.job_title, l.city, l.state_province, c.country_name, r.region_name FROM employees e, departments d, jobs j, locations l, countries c, regions r WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id AND c.region_id = r.region_id AND j.job_id = e.job_id WITH READ ONLY
For the view DEPT, the view text shows capitalized names enclosed in double quotes for the column names: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">select "DEPARTMENT_ID", "DEPARTMENT_NAME", "MANAGER_ID", "LOCATION_D", "DEPT_LOC" from hr.departments</pre> This is an indication that the column names were filled in by the system, and the original DDL for the CREATE VIEW dept is as follows: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">select * from hr.departments</pre> As pointed out in the discussion on using views instead of synonyms for tables, SQL Server retains the exact definition used in the DDL. Oracle does the same thing when an asterisk (\*) is not used. This behavior is shown in the EMP\_DETAILS\_VIEW view text. Thus, while converting the syntax, the DDL may be changed back to an asterisk instead of specifying every column. **Note** The DEPT view was added to the HR schema to demonstrate this point. The EMP\_DETAILS\_VIEW can be created under the HR schema using the following command: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">CREATE VIEW hr.EMP_DETAILS_VIEW ASSELECT e.employee_id, e.job_id, e.manager_id, e.department_id, d.location_id, l.country_id, e.first_name, e.last_name, e.salary, e.commission_pct, d.department_name, j.job_title, l.city, l.state_province, c.country_name, r.region_name FROM hr.employees e INNER JOIN hr.departments d ON e.department_id = d.department_id INNER JOIN hr.locations l ON d.location_id = l.location_id INNER JOIN hr.countries c ON l.country_id = c.country_id INNER JOIN hr.regions r ON c.region_id = r.region_id INNER JOIN hr.jobs j ON j.job_id = e.job_id;
The WITH READ ONLY clause will have to be enforced by ensuring only SELECT privilege is given to users.Create functions, stored procedures, and packages.
The following command can be used to get a listing of all functions defined by the schema owner, HR:
SELECT object_name, object_type
FROM dba_objects WHERE owner = 'HR' AND object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY')
The output retuned is: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">OBJECT_NAME OBJECT_TYPEADD_JOB_HISTORY PROCEDURE SECURE_DML PROCEDURE
The source code of each of these objects can be extracted from the data dictionary using the command: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT textFROM dba_source WHERE owner = 'HR' AND NAME = 'ADD_JOB_HISTORY' ORDER BY line
The output is: <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">TEXTPROCEDURE add_job_history ( p_emp_id job_history.employee_id%type , p_start_date job_history.start_date%type , p_end_date job_history.end_date%type , p_job_id job_history.job_id%type , p_department_id job_history.department_id%type ) IS BEGIN INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id) VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id); END add_job_history;
An overview of the effort in migrating functions, stored procedures, and packages has been given in the "Stored Programs" section of this chapter. For a detailed discussion of migrating the code, refer to the "Migrating the Data Access" section in Chapter 11.Create synonyms.
Synonyms are used with non-schema users to provide location transparency. Because SQL Server does not support synonyms, the SQL will have to be modified to contain a schema prefix.
Note An Oracle schema could potentially have objects or properties that are not in use. Examples of such objects are disabled constraints, invalid views, invalid synonyms, invalid function, invalid procedures, and dropped column (not purged from table). Although it is not necessary to migrate these objects to the new environment, certain clients may differ in this regard.
Download
Get the Solution Guide for Migrating Oracle on UNIX to SQL Server on Windows
Update Notifications
Sign up to learn about updates and new releases
Feedback
.gif)
.gif)
.gif)