Chapter 18 - Migrating Oracle Databases to MS SQL Server 7.0

This chapter is for developers of Oracle applications who want to convert their applications to Microsoft SQL Server applications. The tools, processes, and techniques required for a successful conversion are described. Also highlighted are the essential design points that allow you to create high-performance, high-concurrency SQL Server applications.

Target Audience

The target audience can be new to Microsoft SQL Server and its operation but should have a solid foundation in the Oracle RDBMS and database concepts. The target audience should possess:

This chapter assumes that you are familiar with the terms, concepts, and tools associated with the Oracle RDBMS. For more information about the Oracle RDBMS and its architecture, see the Oracle 7 Server Concepts Manual. For using the Oracle scripts and examples, it is also assumed that you are familiar with the Oracle Server Manager and Oracle SQL*Plus tools. For more information about these tools, see your Oracle documentation.

Development and Application Platforms

For clarity and ease of presentation, the reference development and application platform is assumed to be Microsoft Visual Studio version 6.0, Microsoft Windows NT version 4 (Service Pack 4), SQL Server 7.0, and Oracle 7.3. The Visigenic Software ODBC driver (version 2.00.0300) is used with Oracle 7.3, and the Microsoft Corporation ODBC driver (version 3.70) is used with SQL Server 7.0. Microsoft SQL Server 7.0 includes an OLE DB driver for Oracle, but that driver is not discussed extensively in this chapter.

Top of page

Overview

The application migration process can appear complicated. There are many architectural differences between each RDBMS. The words and terminology used to describe Oracle architecture often have completely different meanings in Microsoft SQL Server. Additionally, both Oracle and SQL Server have made many proprietary extensions to the SQL-92 standard.

From an application developer's perspective, Oracle and SQL Server manage data in similar ways. The internal differences between Oracle and SQL Server are significant, but if managed properly, have minimal impact on a migrated application.

SQL Language Extensions

The most significant migration issue that confronts the developer is the implementation of the SQL-92 SQL language standard and the extensions that each RDBMS has to offer. Some developers use only standard SQL language statements, preferring to keep their program code as generic as possible. Generally, this means restricting program code to the entry-level SQL-92 standard, which is implemented consistently across many database products, including Oracle and SQL Server.

This approach can produce unneeded complexity in the program code and can substantially affect program performance. For example, Oracle's DECODE function is a nonstandard SQL extension specific to Oracle. Microsoft SQL Server's CASE expression is a SQL-92 extension beyond entry level and is not implemented in all database products.

Both the Oracle DECODE and the SQL Server CASE expressions can perform sophisticated conditional evaluation from within a query. The alternative to not using these functions is to perform the function programmatically, which might require that substantially more data be retrieved from the RDBMS.

Also, procedural extensions to the SQL language can cause difficulties. The Oracle PL/SQL and SQL Server Transact-SQL languages are similar in function, but different in syntax. There is no exact symmetry between each RDBMS and its procedural extensions. Consequently, you might decide not to use stored programs such as procedures and triggers. This is unfortunate because they can offer substantial performance and security benefits that cannot be duplicated in any other way.

The use of proprietary development interfaces introduces additional issues. The conversion of a program using the Oracle OCI (Oracle Call Interface) often requires a significant investment in resources. When developing an application that may use multiple RDBMSs, consider using the Open Database Connectivity (ODBC) interface.

ODBC

ODBC is designed to work with numerous database management systems. ODBC provides a consistent application programming interface (API) that works with different databases through the services of a database-specific driver.

A consistent API means that the functions a program calls to make a connection, execute a command, and retrieve results are identical whether the program is talking to Oracle or SQL Server.

ODBC also defines a standardized call-level interface and uses standard escape sequences to specify SQL functions that perform common tasks but have different syntax in different databases. The ODBC drivers can automatically convert this ODBC syntax to either Oracle native or Microsoft SQL Server native SQL syntax without requiring the revision of any program code. In some situations, the best approach is to write one program and allow ODBC to perform the conversion process at run time.

ODBC is not a magical solution for achieving complete database independence, full functionality, and high performance from all databases. Different databases and third-party vendors offer varying levels of ODBC support. Some drivers just implement core API functions mapped on top of other interface libraries. Other drivers, such as the Microsoft SQL Server driver, offer full Level 2 support in a native, high-performance driver.

If a program uses only the core ODBC API, it will likely forego features and performance capabilities with some databases. Furthermore, not all native SQL extensions can be represented in ODBC escape sequences (such as Oracle DECODE and SQL Server CASE expressions).

Additionally, it is common practice to write SQL statements to take advantage of the database's optimizer. The techniques and methods that enhance performance within Oracle are not necessarily optimal within Microsoft SQL Server 7.0. The ODBC interface can not translate techniques from one RDBMS to another.

ODBC does not prevent an application from using database-specific features and tuning for performance, but the application needs some database-specific sections of code. ODBC makes it easy to keep the program structure and the majority of the program code consistent across multiple databases.

OLE DB

OLE DB is the next generation of data access technology. Microsoft SQL Server 7.0 takes advantage of OLE DB within the components of SQL Server itself. Additionally, application developers should consider OLE DB for new development with SQL Server 7.0. Microsoft includes an OLE DB provider for Oracle 7.3 with SQL Server 7.0.

OLE DB is Microsoft's strategic system-level programming interface to manage data across the organization. OLE DB is an open specification designed to build on the features of ODBC. ODBC was created to access relational databases, and OLE DB is designed to access relational and nonrelational information sources, such as mainframe ISAM/VSAM and hierarchical databases, e-mail and file system stores, text, graphical and geographical data, and custom business objects.

OLE DB defines a collection of COM interfaces that encapsulate various database management system services and allows the creation of software components that implement such services. OLE DB components consist of data providers (that contain and expose data), data consumers (that use data), and service components (that process and transport data, for example, query processors and cursor engines).

OLE DB interfaces are designed to help components integrate smoothly so that OLE DB component vendors can bring high quality OLE DB components to the market quickly. In addition, OLE DB includes a bridge to ODBC to allow continued support for the broad range of ODBC relational database drivers available today.

Organization of this Chapter

To assist you in implementing a step-by-step migration from Oracle to SQL Server, each section includes an overview of the relevant differences between Oracle 7.3 and Microsoft SQL Server 7.0. It also includes conversion considerations, SQL Server 7.0 advantages, and multiple examples.

Top of page

Architecture and Terminology

To start a successful migration, you should understand the basic architecture and terminology associated with Microsoft SQL Server 7.0. Many of the examples in this section have been drawn from the sample Oracle and SQL Server applications included as part of this chapter.

Definition of Database

In Oracle, a database refers to the entire Oracle RDBMS environment and includes these components.

A Microsoft SQL Server database provides a logical separation of data, applications, and security mechanisms, much like a tablespace. Where Oracle supports multiple tablespaces, SQL Server supports multiple databases. Tablespaces also can be used to support the physical placement of data; SQL Server provides this same functionality with filegroups.

Microsoft SQL Server also installs the following databases by default:

For more information about the default databases, see SQL Server Books Online.

Database System Catalogs

Each Oracle database runs on one centralized system catalog, or data dictionary, which resides in the SYSTEM tablespace. Each Microsoft SQL Server 7.0 database maintains its own system catalog, which contains information about:

SQL Server also contains a centralized system catalog in the master database, which contains system catalogs as well as some information about the individual databases:

Like the SYSTEM tablespace in Oracle, the SQL Server master database must be available to access any other database. As such, it is important to protect against failures by backing up the master database after any significant changes are made in the database. Database administrators can also mirror the files that make up the master database.

Physical and Logical Storage Structures

The Oracle RDBMS is comprised of tablespaces, which in turn are comprised of data files. Tablespace data files are formatted into internal units termed blocks. The block size, set by the DBA when the Oracle database is first created, can range from 512 to 8192 bytes. When an object is created in an Oracle tablespace, the user specifies its space in units called extents (initial extent, next extent, min extents, and max extents). An Oracle extent varies in size and must contain a chain of at least five contiguous blocks.

Microsoft SQL Server uses filegroups at the database level to control the physical placement of tables and indexes. Filegroups are logical containers of one or more files, and data contained within a filegroup is proportionally filled across all files belonging to the filegroup.

If filegroups are not defined and used, database objects are placed in a default filegroup that is implicitly defined during the creation of a database. Filegroups allow you to:

SQL Server formats files into internal units called pages. The page size is fixed at 8192 bytes (8 KB). Pages are organized into extents that are fixed in size at 8 contiguous pages. When a table or index is created in a SQL Server database, it is automatically allocated one page. This allows for more efficient storage of smaller tables and indexes when compared to allocating an entire extent.

Striping Data

Oracle-type segments are not needed for most Microsoft SQL Server installations. Instead, SQL Server can distribute, or stripe, data better with hardware-based RAID or Windows NT software–based RAID. Windows NT software–based RAID or hardware-based RAID can set up stripe sets consisting of multiple disk drives that appear as one logical drive. If database files are created on this stripe set, the disk subsystem assumes responsibility for distributing I/O load across multiple disks. It is recommended that administrators spread out the data over multiple physical disks using RAID.

The recommended RAID configuration for SQL Server is RAID 1 (mirroring) or RAID 5 (stripe sets with an extra parity drive, for redundancy). RAID 10 (mirroring of striped sets with parity) is also recommended, but is much more expensive than the first two options. Stripe sets work very well to spread out the usually random I/O done on database files.

If RAID is not an option, filegroups are an attractive alternative and provide some of the same benefits available with RAID. Additionally, for very large databases that might span multiple physical RAID arrays, filegroups may be an attractive way to further distribute your I/O across multiple RAID arrays in a controlled fashion.

Transaction log files must be optimized for sequential I/O and must be secured against a single point of failure. Accordingly, RAID 1 (mirroring) is recommended for transaction logs. The size of this drive should be at least as large as the sum of the size of the online redo logs and the rollback segment tablespace(s). Create one or more log files that take up all the space defined on the logical drive. Unlike data stored in filegroups, transaction log entries are always written sequentially and are not proportionally filled.

For more information about RAID, see SQL Server Books Online, your Windows NT Server documentation, and the Microsoft Windows NT Resource Kit.

Transaction Logs and Automatic Recovery

The Oracle RDBMS performs automatic recovery each time it is started. It verifies that the contents of the tablespace files are coordinated with the contents of the online redo log files. If they are not, Oracle applies the contents of the online redo log files to the tablespace files (roll forward), and then removes any uncommitted transactions that are found in the rollback segments (roll back). If Oracle cannot obtain the information it requires from the online redo log files, it consults the archived redo log files.

Microsoft SQL Server 7.0 also performs automatic data recovery by checking each database in the system each time it is started. It first checks the master database and then launches threads to recover all of the other databases in the system. For each SQL Server database, the automatic recovery mechanism checks the transaction log. If the transaction log contains any uncommitted transactions, the transactions are rolled back. The recovery mechanism then checks the transaction log for committed transactions that have not yet been written out to the database. If it finds any, it performs those transactions again, rolling forward.

Each SQL Server transaction log has the combined functionality of an Oracle rollback segment and an Oracle online redo log. Each database has its own transaction log that records all changes to the database and is shared by all users of that database. When a transaction begins and a data modification occurs, a BEGIN TRANSACTION event (as well as the modification event) is recorded in the log. This event is used during automatic recovery to determine the starting point of a transaction. As each data modification statement is received, the changes are written to the transaction log prior to being written to the database itself. For more information, see the "Transactions, Locking, and Concurrency" section later in this chapter.

SQL Server has an automatic checkpoint mechanism that ensures completed transactions are regularly written from the SQL Server disk cache to the transaction log file. A checkpoint writes any cached page that has been modified since the last checkpoint to the database. Checkpointing these cached pages, known as dirty pages, onto the database, ensures that all completed transactions are written out to disk. This process shortens the time that it takes to recover from a system failure, such as a power outage. This setting can be changed by modifying the recovery interval setting by using SQL Server Enterprise Manager or with Transact-SQL (sp_configure system stored procedure).

Backing Up and Restoring Data

Microsoft SQL Server offers several options for backing up data:

Full database backup

To make a full database backup, use the BACKUP DATABASE statement or the Backup Wizard.

Differential backup

After a full database backup, regularly back up just the changed data and index pages using the BACKUP DATABASE WITH DIFFERENTIAL statement or the Backup Wizard.

Transaction log backup

Transaction logs in Microsoft SQL Server are associated with individual databases. The transaction log fills until it is backed up or truncated. The default configuration of SQL Server 7.0 is that the transaction log grows automatically until it uses all available disk space or it meets its maximum configured size. When a transaction log gets too full, it can create an error and prevent further data modifications until it is backed up or truncated. Other databases are not affected. Transaction logs can be backed up using the BACKUP LOG statement or the Backup Wizard.

File or filegroup backup

SQL Server can back up files and filegroups. For more information, see SQL Server Books Online.

Backups can be performed while the database is in use, allowing backups to be made of systems that must run continually. The backup processing and internal data structures of SQL Server 7.0 have been improved so that backups maximize their rate of data transfer with minimal effect on transaction throughput.

Both Oracle and SQL Server require a specific format for log files. In SQL Server, these files, called backup devices, are created using SQL Server Enterprise Manager, the Transact-SQL sp_addumpdevice stored procedure, or the equivalent SQL-DMO command.

Although backups can be performed manually, it is recommended that you use SQL Server Enterprise Manager and/or the Database Maintenance Plan Wizard to schedule periodic backups, or backups based on database activity.

A database can be restored to a certain point in time by applying transaction log backups and/or differential backups to a full database backup (device). A database restore overwrites the data with the information contained in the backups. Restores can be performed using SQL Server Enterprise Manager, Transact-SQL (RESTORE DATABASE), or SQL-DMO.

Just as you can turn off the Oracle archiver to override automatic backups, in Microsoft SQL Server, members of the db_owner fixed database role can force the transaction log to erase its contents every time a checkpoint occurs. This can be accomplished by using SQL Server Enterprise Manager (truncate log on checkpoint), Transact-SQL (sp_dboption stored procedure), or SQL-DMO.

Networks

Oracle SQL*Net supports networked connections between Oracle database servers and their clients. It communicates with the Transparent Network Substrate (TNS) data stream protocol, and allows users to run many different network protocols without writing specialized code. SQL*Net is not included with the core Oracle database software product.

With Microsoft SQL Server, Net-Libraries (network libraries) support the networked connections between the clients and the server by using the Tabular Data Stream (TDS) protocol. They enable simultaneous connections from clients running Named Pipes, TCP/IP Sockets, or other Inter-Process Communication (IPC) mechanisms. The SQL Server CD-ROM includes all client Net-Libraries so that there is no need to purchase them.

SQL Server Net-Library options can be changed after installation. The Client Network utility configures the default Net-Library and server connection information for a client running the Windows NT, Windows 95, or Windows 98 operating systems. All ODBC client applications use the same default Net-Library and server connection information, unless it is changed during ODBC data source setup or explicitly coded in the ODBC connection string. For more information about Net-Libraries, see SQL Server Books Online.

Database Security and Roles

To adequately migrate your Oracle applications to Microsoft SQL Server 7.0, you must understand SQL Server's implementation of database security and roles.

Login Accounts

A login account allows a user to access SQL Server data or administrative options. The login account allows users only to log in to SQL Server and view databases that allow guest access. (The guest account is not set up by default and must be created.)

SQL Server offers two types of login security: Windows NT Authentication Mode (also known as integrated) and SQL Server Authentication Mode (also known as standard). SQL Server 7.0 also supports a combination of standard and integrated security, known as mixed.

The Windows NT Authentication Mode uses the security mechanisms within Windows NT when validating login connections, and relies on a user's Windows NT security credentials. Users do not need to enter login IDs or passwords for SQL Server—their login information is taken directly from the network connection. When this occurs, an entry is written to the syslogins table and is verified between Windows NT and SQL Server. This is known as a trusted connection and works like a trust relationship between two Windows NT servers. This functions like the IDENTIFIED EXTERNALLY option associated with Oracle user accounts.

The SQL Server Authentication Mode requires that a user enter a login ID and password when requesting access to SQL Server. This is known as a nontrusted connection. This functions like the IDENTIFIED BY PASSWORD option associated with Oracle user accounts. With the use of the standard security model, the login provides access to the SQL Server database engine only; it does not provide access to the user databases.

For more information about these security mechanisms, see SQL Server Books Online.

Groups, Roles, and Permissions

Microsoft SQL Server and Oracle use permissions to enforce database security. SQL Server statement-level permissions are used to restrict the ability to create new database objects (similar to the Oracle system-level permissions).

SQL Server also offers object-level permissions. As in Oracle, object-level ownership is assigned to the creator of the object and cannot be transferred. Object-level permissions must be granted to other database users before they can access the object. Members of the sysadmin fixed server role, db_owner fixed database role, or db_securityadmin fixed database role can also grant permissions on one user's objects to other users.

SQL Server statement- and object-level permissions can be granted directly to database user accounts. However, it is often simpler to administer permissions to database roles. SQL Server roles are used for granting and revoking privileges to groups of database users (much like Oracle roles). Roles are database objects associated with a specific database. There are a few specific fixed server roles associated with each installation, which work across databases. An example of a fixed server role is sysadmin. Windows NT groups can also be added as SQL Server logins, as well as database users. Permissions can be granted to a Windows NT group or a Windows NT user.

A database can have any number of roles or Windows NT groups. The default role public is always found in every database and cannot be removed. The public role functions much like the PUBLIC account in Oracle. Each database user is always a member of the public role. A database user can be a member of any number of roles in addition to the public role. A Windows NT user or group can also be a member of any number of roles, and is also always in the public role.

Database Users and the guest Account

In Microsoft SQL Server, a user login account must be authorized to use a database and its objects. One of the following methods can be used by a login account to access a database:

Members of the db_owner or db_accessadmin roles, or the sysadmin fixed server role, create the database user account roles. An account can include several parameters: the SQL Server login ID, database username (optional), and up to one role name (optional). The database username does not have to be the same as the user's login ID. If a database username is not provided, the user's login ID and database username are identical. If a role name is not provided, the database user is only a member of the public role. After creating the database user, the user can be assigned to as many roles as necessary.

Members of the db_owner or db_accessadmin roles can also create a guest account. The guest account allows any valid SQL Server login account to access a database even without a database user account. By default, the guest account inherits any privileges that have been assigned to the public role; however, these privileges can be changed to be greater or less than that of the public role.

A Windows NT user account or group account can be granted access to a database, just as a SQL Server login can. When a Windows NT user who is a member in a group connects to the database, the user receives the permissions assigned to the Windows NT group. If a member of more than one Windows NT group that has been granted access to the database, the user receives the combined rights of all of the groups to which he belongs.

The sysadmin Role

Members of the Microsoft SQL Server sysadmin fixed server role have similar permissions to that of an Oracle DBA. In SQL Server 7.0, the sa SQL Server Authentication Mode login account is a member of this role by default, as are members of the local Administrators group if SQL Server is installed on a Windows NT computer. A member of the sysadmin role can add or remove Windows NT users and groups, as well as SQL Server logins. Members of this role typically have the following responsibilities:

*These items can be delegated to other security roles or users.

There are no restrictions on what a member of the sysadmin fixed server role can do in SQL Server 7.0. Therefore, a member of this role can access any database and all of the objects (including data) on a particular instance of SQL Server. Like an Oracle DBA, there are several commands and system procedures that only members of the sysadmin role can issue.

The db_owner Role

Although a Microsoft SQL Server database is similar to an Oracle tablespace in use, it is administered differently. Each SQL Server database is a self-contained administrative domain. Each database is assigned a database owner (dbo). This user is always a member of the db_owner fixed database role. Other users can also be members of the db_owner role. Any user who is a member of this role has the ability to manage the administrative tasks related to her database (unlike Oracle, in which one DBA manages the administrative tasks for all tablespaces). These tasks include:

Members of the db_owner role have permissions to do anything within their database. Most rights assigned to this role are separated into several fixed database roles, or can be granted to database users. It is not necessary to have sysadmin server-wide privileges to have db_owner privileges in a database.

Top of page

Installing and Configuring Microsoft SQL Server

With an understanding of the basic structural differences between Oracle and SQL Server, you are ready to perform the first step in the migration process. SQL Server Query Analyzer should be used to run these scripts:

USE MASTER EXEC SP_ADDLOGIN STUDENT_ADMIN, STUDENT_ADMIN EXEC SP_ADDLOGIN DEPT_ADMIN, DEPT_ADMIN EXEC SP_ADDLOGIN ENDUSER1, ENDUSER1 GO

USE USER_DB EXEC SP_ADDROLE DATA_ADMIN EXEC SP_ADDROLE USER_LOGON GO

GRANT CREATE TABLE, CREATE TRIGGER, CREATE VIEW,  CREATE PROCEDURE TO DATA_ADMIN GO

EXEC SP_ADDUSER ENDUSER1, ENDUSER1, USER_LOGON EXEC SP_ADDUSER DEPT_ADMIN, DEPT_ADMIN, DATA_ADMIN EXEC SP_ADDUSER STUDENT_ADMIN, STUDENT_ADMIN, DATA_ADMIN GO

The illustration shows the SQL Server and Oracle environments after this process is completed.

Top of page

Defining Database Objects

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

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

Database Object Identifiers

The following chart compares how Oracle and Microsoft SQL Server handle object identifiers. In most cases, you do not need to change the names of objects when migrating to SQL Server.

Qualifying Table Names

When accessing tables that exist in your Oracle user account, the table can be selected simply by its unqualified name. Accessing tables in other Oracle schemas requires that the schema name be prefixed to the table name with a single period (.). Oracle synonyms can provide additional location transparency.

Microsoft SQL Server uses a different convention when it references tables. Because one SQL Server login account can create a table by the same name in multiple databases, the following convention is used to access tables and views: [[database_name.]owner_name.]table_name

Here are guidelines for naming Microsoft SQL Server tables and views:

At the same time, other users in these databases might own objects by the same name:

Therefore, it is recommended that you include the owner name as part of the reference to a database object. If the application has multiple databases, it is recommended that the database name also is included as part of the reference. If the query spans multiple servers, include the server name.

The object's owner can be omitted by separating the database and table name by two periods. For example, if an application references STUDENT_DB..STUDENT, SQL Server searches as follows:

If the application uses only a single database at a time, omitting the database name from an object reference makes it easy to use the application with another database. All object references implicitly access the database that is currently being used. This is useful when you want to maintain a test database and a production database on the same server.

Creating Tables

Because Oracle and SQL Server support SQL-92 entry-level conventions for identifying RDBMS objects, the CREATE TABLE syntax is similar.

Oracle database object names are not case-sensitive. In Microsoft SQL Server, database object names can be case-sensitive, depending on the installation options selected.

When SQL Server is first set up, the default sort order is dictionary order, case-insensitive. (This can be configured differently using SQL Server Setup.) Because Oracle object names are always unique, you should not have any problems migrating the database objects to SQL Server. It is recommended that all table and column names in both Oracle and SQL Server be uppercase to avoid problems if a user installs on a case-sensitive SQL Server.

Table and Index Storage Parameters

With Microsoft SQL Server, using RAID usually simplifies the placement of database objects. A SQL Server clustered index is integrated into the structure of the table, like an Oracle index-organized table.

Creating Tables with SELECT Statements

Using Oracle, a table can be created with any valid SELECT command. Microsoft SQL Server provides the same functionality with different syntax.

SELECT…INTO does not work unless the database to which this is applied has the database configuration option select into/bulkcopy set to true. (The database owner can set this option using SQL Server Enterprise Manager or the Transact-SQL sp_dboption system stored procedure.) Use the sp_helpdb system stored procedure to check the status of the database. If select into/bulkcopy is not set to true, you can still use a SELECT statement to copy into a temporary table:

SELECT * INTO #student_backup FROM user_db.student_admin.student

When new tables are created using SELECT.. INTO statements, referential integrity definitions are not transferred to the new table.

The need to have the select into/bulkcopy option set to true may complicate the migration process. If you must copy data into tables by using a SELECT statement, create the table first, and then use the INSERT INTO…SELECT statement to load the table. The syntax is the same for Oracle and SQL Server, and does not require that any database option be set.

Views

The syntax used to create views in Microsoft SQL Server is similar to that of Oracle.

SQL Server views require that the tables exist and that the view owner has privileges to access the requested tables(s) specified in the SELECT statement (similar to the Oracle FORCE option).

By default, data modification statements on views are not checked to determine whether the rows affected are within the scope of the view. To check all modifications, use the WITH CHECK OPTION. The primary difference between the WITH CHECK OPTION is that Oracle defines it as a constraint, while SQL Server does not. Otherwise, it functions the same in both.

Oracle provides a WITH READ ONLY option when defining views. SQL Server applications can achieve the same result by granting only SELECT permission to the users of the view.

Both SQL Server and Oracle views support derived columns, using arithmetic expressions, functions, and constant expressions. Some of the specific SQL Server differences are:

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. In most cases, Oracle views are easily translated into SQL Server views.

Indexes

Microsoft SQL Server offers clustered and nonclustered index structures. These indexes are made up of pages that form a branching structure known as a B-tree (similar to the Oracle B-tree index structure). The starting page (root level) specifies ranges of values within the table. Each range on the root-level page points to another page (decision node), which contains a more limited range of values for the table. In turn, these decision nodes can point to other decision nodes, further narrowing the search range. The final level in the branching structure is called the leaf level.

Clustered Indexes

Clustered indexes are implemented in Oracle as index-organized tables. A clustered index is an index that has been physically merged with a table. The table and index share the same storage area. The clustered index physically rearranges the rows of data in indexed order, forming the intermediate decision nodes. The leaf pages of the index contain the actual table data. This architecture permits only one clustered index per table. Microsoft SQL Server automatically creates a clustered index for the table whenever a PRIMARY KEY or UNIQUE constraint is placed on the table. Clustered indexes are useful for:

SELECT * FROM STUDENT WHERE GRAD_DATE BETWEEN '1/1/97' AND '12/31/97'

Dropping and re-creating a clustered index is a common technique for reorganizing a table in SQL Server. It is an easy way to ensure that data pages are contiguous on disk and to reestablish some free space in the table. This is similar to exporting, dropping, and importing a table in Oracle.

A SQL Server clustered index is not at all like an Oracle cluster. An Oracle cluster is a physical grouping of two or more tables that share the same data blocks and use common columns as a cluster key. SQL Server does not have a structure similar to an Oracle cluster.

As a general rule, defining a clustered index on a table improves SQL Server performance and space management. If you do not know the query or update patterns for a given table, you can create the clustered index on the primary key.

The table shows an excerpt from the sample application source code. Note the use of the SQL Server clustered index.

Nonclustered Indexes

In nonclustered indexes, the index data and the table data are physically separate, and the rows in the table are not stored in the order of the index. You can move Oracle index definitions to Microsoft SQL Server nonclustered index definitions (as shown in the following example). For performance reasons, however, you might want to choose one of the indexes of a given table and create it as a clustered index.

Index Syntax and Naming

In Oracle, an index name is unique within a user account. In Microsoft SQL Server, an index name must be unique within a table name, but it does not have to be unique within a user account or database. Therefore, when creating or dropping an index in SQL Server, you must specify both the table name and the index name. Additionally, the SQL Server DROP INDEX statement can drop multiple indexes at one time.

Index Data Storage Parameters

The FILLFACTOR option in Microsoft SQL Server functions in much the same way as the PCTFREE variable does in Oracle. As tables grow in size, index pages split to accommodate new data. The index must reorganize itself to accommodate new data values. The fill factor percentage is used only when the index is created, and it is not maintained afterwards.

The FILLFACTOR option (values are 0 through 100) controls how much space is left on an index page when the index is initially created. The default fill factor of 0 is used if none is specified—this will completely fill index leaf pages and leave space on each decision node page for at least one entry (two for nonunique clustered indexes).

A lower fill factor value initially reduces the splitting of index pages and increases the number of levels in the B-tree index structure. A higher fill factor value uses index page space more efficiently, requires fewer disk I/Os to access index data, and reduces the number of levels in the B-tree index structure.

The PAD_INDEX option specifies that the fill factor setting be applied to the decision node pages as well as to the data pages in the index.

While it may be necessary to adjust the PCTFREE parameter for optimal performance in Oracle, it is seldom necessary to include the FILLFACTOR option in a CREATE INDEX statement. The fill factor is provided for fine-tuning performance. It is useful only when creating a new index on a table with existing data, and then it is useful only when you can accurately predict future changes in that data.

If you have set PCTFREE to 0 for your Oracle indexes, consider using a fill factor of 100. This is used when there will be no inserts or updates occurring in the table (a read-only table). When fill factor is set to 100, SQL Server creates indexes with each page 100 percent full.

Ignoring Duplicate Keys

With both Oracle and Microsoft SQL Server, users cannot insert duplicate values for a uniquely indexed column or columns. An attempt to do so generates an error message. Nevertheless, SQL Server lets the developer choose how the INSERT or UPDATE statement will respond to the error.

If IGNORE_DUP_KEY was specified in the CREATE INDEX statement, and an INSERT or UPDATE statement that creates a duplicate key is executed, SQL Server issues a warning message and ignores (does not insert) the duplicate row. If IGNORE_DUP_KEY was not specified for the index, SQL Server issues an error message and rolls back the entire INSERT statement. For more information about these options, see SQL Server Books Online.

Using Temporary Tables

An Oracle application might have to create tables that exist for short periods. The application must ensure that all tables created for this purpose are dropped at some point. If the application fails to do this, tablespaces can quickly become cluttered and unmanageable.

Microsoft SQL Server provides temporary table database objects, which are created for just such a purpose. These tables are always created in the tempdb database. The table name determines how long they reside within the tempdb database.

Indexes can be defined for temporary tables. Views can be defined only on tables explicitly created in tempdb without the # or ## prefix. The following example shows the creation of a temporary table and its associated index. When the user exits, the table and index are automatically dropped.

SELECT SUM(ISNULL(TUITION_PAID,0)) SUM_PAID, MAJOR INTO #SUM_STUDENT FROM USER_DB.STUDENT_ADMIN.STUDENT GROUP BY MAJOR  CREATE UNIQUE INDEX SUM STUDENT IDX ON #SUM STUDENT (MAJOR)

You may find that the benefits associated with using temporary tables justify a revision in your program code.

Data Types

Microsoft SQL Server has a more robust selection of data types than Oracle. There are many possible conversions between the Oracle and SQL Server data types. It is recommended that you use the DTS Wizard to automate the creation of the new CREATE TABLE statements. You can then modify these statements as necessary.

Using Unicode Data

The Unicode specification defines a single encoding scheme for practically all characters widely used in businesses around the world. All computers consistently translate the bit patterns in Unicode data into characters using the single Unicode specification. This ensures that the same bit pattern is always converted to the same character on all computers. Data can be freely transferred from one database or computer to another without concern that the receiving system will correctly translate the bit patterns into characters.

One problem with data types that use 1 byte to encode each character is that the data type can represent only 256 different characters. This forces multiple encoding specifications (or code pages) for different alphabets. It is also impossible to handle systems such as the Japanese Kanji or Korean Hangul alphabets that have thousands of characters.

Microsoft SQL Server translates the bit patterns in char, varchar, and text columns to characters using the definitions in the code page installed with SQL Server. Client computers use the code page installed with the operating system to interpret character bit patterns. There are many different code pages. Some characters appear on some code pages, but not on others. Some characters are defined with one bit pattern on some code pages, and with a different bit pattern on other code pages. When you build international systems that must handle different languages, it becomes difficult to pick code pages for all the computers that meet the language requirements of multiple countries. It is also difficult to ensure that every computer performs the correct translations when interfacing with a system that uses a different code page.

The Unicode specification addresses this problem by using 2 bytes to encode each character. There are enough different patterns (65,536) in 2 bytes for a single specification covering the most common business languages. Because all Unicode systems consistently use the same bit patterns to represent all characters, there is no problem with characters being converted incorrectly when moving from one system to another.

In SQL Server, nchar, nvarchar, and ntext data types support Unicode data. For more information about SQL Server data types, see SQL Server Books Online.

User-defined Data Types

User-defined data types can be created for the model database or for a single user database. If the user-defined data type is defined for model, that data type is available to all new user databases created from that point forward. The user-defined data type is defined with the sp_addtype system stored procedure. For more information, see SQL Server Books Online.

You can use a user-defined data type in the CREATE TABLE and ALTER TABLE statements, and bind defaults and rules to it. If nullability is explicitly defined when the user-defined data type is used during table creation, it takes precedence over the nullability defined when the data type was created.

This example shows how to create a user-defined data type. The arguments are the user-type name, data type, and nullability:

sp_addtype gender_type, 'varchar(1)', 'not null' go

This capability might initially appear to solve the problem of migrating Oracle table creation scripts to SQL Server. For example, it is quite easy to add the Oracle DATE data type:

sp_addtype date, datetime

This does not work with data types that require variable sizes, such as the Oracle data type NUMBER. An error message is returned indicating that a length must also be specified:

sp_addtype varchar2, varchar Go Msg 15091, Level 16, State 1 You must specify a length with this physical type.

Microsoft timestamp Columns

The timestamp columns enable BROWSE-mode updates and make cursor update operations more efficient. The timestamp is a data type that is automatically updated every time a row containing a timestamp column is inserted or updated.

Values in timestamp columns are not stored as an actual date or time, but are stored as binary(8) or varbinary(8), which indicates the sequence of events on rows in the table. A table can have only one timestamp column.

For more information, see SQL Server Books Online.

Object-level Permissions

Microsoft SQL Server object privileges can be granted to, denied from, and revoked from other database users, database groups, and the public role. SQL Server does not allow an object owner to grant ALTER TABLE and CREATE INDEX privileges for the object as Oracle does. Those privileges must remain with the object owner.

The GRANT statement creates an entry in the security system that allows a user in the current database to work with data in the current database or to execute specific Transact-SQL statements. The syntax of the GRANT statement is identical in Oracle and SQL Server.

The DENY statement creates an entry in the security system that denies a permission from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships. Oracle does not have a DENY statement. The REVOKE statement removes a previously granted or denied permission from a user in the current database.

For more information about object-level permissions, see SQL Server Books Online.

In Oracle, the REFERENCES privilege can be granted only to a user. SQL Server allows the REFERENCES privilege to be granted to both database users and database groups. The INSERT, UPDATE, DELETE, and SELECT privileges are granted in the same way in both Oracle and SQL Server.

Top of page

Enforcing Data Integrity and Business Rules

Enforcing data integrity ensures the quality of the data in the database. Two important steps when planning tables are identifying valid values for a column and deciding how to enforce the integrity of the data in the column. Data integrity falls into four categories, and is enforced in various ways.

Entity Integrity

Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties.

Naming Constraints

You should always name your constraints explicitly. If you do not, Oracle and Microsoft SQL Server will use different naming conventions to name the constraint implicitly. These differences in naming can complicate your migration process unnecessarily. The discrepancy appears when dropping or disabling constraints, because constraints must be dropped by name. The syntax for explicitly naming constraints is the same for Oracle and SQL Server:

CONSTRAINT constraint_name

Primary Keys and Unique Columns

The SQL-92 standard requires that all values in a primary key be unique and that the column not allow null values. Both Oracle and Microsoft SQL Server enforce uniqueness by automatically creating unique indexes whenever a PRIMARY KEY or UNIQUE constraint is defined. Additionally, primary key columns are automatically defined as NOT NULL. Only one primary key is allowed per table.

A SQL Server clustered index is created by default for a primary key, though a nonclustered index can be requested. The Oracle index on primary keys can be removed by either dropping or disabling the constraint, whereas the SQL Server index can be removed only by dropping the constraint.

In either RDBMS, alternate keys can be defined with a UNIQUE constraint. Multiple UNIQUE constraints can be defined on any table. UNIQUE constraint columns are nullable. In SQL Server, a nonclustered index is created by default, unless otherwise specified.

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

Adding and Removing Constraints

Disabling constraints can improve database performance and streamline the data replication process. For example, when you rebuild or replicate table data at a remote site, you do not have to repeat constraint checks, because the integrity of the data was checked when it was originally entered into the table. You can program an Oracle application to disable and enable constraints (except for PRIMARY KEY and UNIQUE). You can accomplish this in Microsoft SQL Server using the CHECK and WITH NOCHECK options with the ALTER TABLE statement.

This illustration shows a comparison of this process.

With SQL Server, you can defer all of the table constraints by using the ALL keyword with the NOCHECK clause.

If your Oracle application uses the CASCADE option to disable or drop PRIMARY KEY or UNIQUE constraints, you may need to rewrite some code because the CASCADE option disables or drops both the parent and any related child integrity constraints.

This is an example of the syntax:

DROP CONSTRAINT DEPT_DEPT_PK CASCADE

The SQL Server application must be modified to first drop the child constraints and then the parent constraints. For example, in order to drop the PRIMARY KEY constraint on the DEPT table, the foreign keys for the columns STUDENT.MAJOR and CLASS.DEPT must be dropped. This is an example of the syntax:

ALTER TABLE STUDENT DROP CONSTRAINT STUDENT_MAJOR_FK ALTER TABLE CLASS DROP CONSTRAINT CLASS_DEPT_FK ALTER TABLE DEPT DROP CONSTRAINT DEPT_DEPT_PK

The ALTER TABLE syntax that adds and drops constraints is almost identical for Oracle and SQL Server.

Generating Sequential Numeric Values

If your Oracle application uses SEQUENCEs, it can be altered easily to take advantage of the Microsoft SQL Server IDENTITY property.

Although the IDENTITY property automates row numbering within one table, separate tables, each with its own identifier column, can generate the same values. This is because the IDENTITY property is guaranteed to be unique only for the table on which it is used. If an application must generate an identifier column that is unique across the entire database, or every database on every networked computer in the world, use the ROWGUIDCOL property, the uniqueidentifier data type, and the NEWID function. SQL Server uses globally unique identifier columns for merge replication to ensure that rows are uniquely identified across multiple copies of the table.

For more information about creating and modifying identifier columns, see SQL Server Books Online.

Domain Integrity

Domain integrity enforces valid entries for a given column. Domain integrity is enforced by restricting the type (through data types), the format (through CHECK constraints), or the range of possible values (through REFERENCE and CHECK constraints).

DEFAULT and CHECK Constraints

Oracle treats a default as a column property, and Microsoft SQL Server treats a default as a constraint. The SQL Server DEFAULT constraint can contain constant values, built-in functions that do not take arguments (niladic functions), or NULL.

To easily migrate the Oracle DEFAULT column property, you should define DEFAULT constraints at the column level in SQL Server without applying constraint names. SQL Server generates a unique name for each DEFAULT constraint.

The syntax used to define CHECK constraints is the same in Oracle and SQL Server. The search condition must evaluate to a Boolean expression and cannot contain subqueries. A column-level CHECK constraint can reference only the constrained column, and a table-level check constraint can reference only columns of the constrained table. Multiple CHECK constraints can be defined for a table. SQL Server syntax allows only one column-level CHECK constraint to be created on a column in a CREATE TABLE statement, and the constraint can have multiple conditions.

The best way to test your modified CREATE TABLE statements is to use the SQL Server Query Analyzer in SQL Server, and parse only the syntax. The results pane indicate any errors. For more information about constraint syntax, see SQL Server Books Online.

A note about user-defined rules and defaults: The syntax for Microsoft SQL Server rules and defaults remains for backward compatibility purposes, but CHECK constraints and DEFAULT constraints are recommended for new application development. For more information, see SQL Server Books Online.

Nullability

Microsoft SQL Server and Oracle create column constraints to enforce nullability. An Oracle column defaults to NULL, unless NOT NULL is specified in the CREATE TABLE or ALTER TABLE statements. In Microsoft SQL Server, database and session settings can override the nullability of the data type used in a column definition.

All of your SQL scripts (whether Oracle or SQL Server) should explicitly define both NULL and NOT NULL for each column. To see how this strategy is implemented, see Oratable.sql and Sstable.sql, the sample table creation scripts. When not explicitly specified, column nullability follows these rules.

Referential Integrity

The table provides a comparison of the syntax used to define referential integrity constraints.

The NOT FOR REPLICATION clause is used to suspend column-level, FOREIGN KEY, and CHECK constraints during replication.

Foreign Keys

The rules for defining foreign keys are similar in each RDBMS. The number of columns and data type of each column specified in the foreign key clause must match the REFERENCES clause. A nonnull value entered in this column(s) must exist in the table and column(s) defined in the REFERENCES clause, and the referenced table's columns must have a PRIMARY KEY or UNIQUE constraint.

Microsoft SQL Server constraints provide the ability to reference tables within the same database. To implement referential integrity across databases, use table-based triggers.

Both Oracle and SQL Server support self-referenced tables, tables in which a reference (foreign key) can be placed against one or more columns on the same table. For example, the column prereq in the CLASS table can reference the column ccode in the CLASS table to ensure that a valid course code is entered as a course prerequisite.

Whereas cascading deletes and updates are implemented in Oracle with the CASCADE DELETE clause, SQL Server provides the same functionality with table triggers. For more information, see "SQL Language Support" later in this chapter.

User-defined Integrity

User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories.

Stored Procedures

Microsoft SQL Server stored procedures use the CREATE PROCEDURE statement to accept and return user-supplied parameters. With the exception of temporary stored procedures, stored procedures are created in the current database. The table shows the syntax for Oracle and SQL Server.

In SQL Server, temporary procedures are created in the tempdb database by prefacing procedure_name with a single number sign (#procedure_name) for local temporary procedures and a double number sign (##procedure_name) for global temporary procedures.

A local temporary procedure can be used only by the user who created it. Permission to execute a local temporary procedure cannot be granted to other users. Local temporary procedures are automatically dropped at the end of the user session.

A global temporary procedure is available to all SQL Server users. If a global temporary procedure is created, all users can access it, and permissions cannot be explicitly revoked. Global temporary procedures are dropped at the end of the last user session using the procedure.

SQL Server stored procedures can be nested up to 32 levels. The nesting level is incremented when the called procedure starts execution, and it is decremented when the called procedure finishes execution.

The following example demonstrates how a Transact-SQL stored procedure can be used to replace an Oracle PL/SQL packaged function. The Transact-SQL version is much simpler because of SQL Server's ability to return result sets directly from SELECT statements in a stored procedure, without using a cursor.

SQL Server does not support constructs similar to Oracle packages or functions, and does not support the CREATE OR REPLACE option for creating stored procedures.

Delaying the Execution of a Stored Procedure

Microsoft SQL Server provides WAITFOR, which allows developers to specify a time, time interval, or event that triggers the execution of a statement block, stored procedure, or transaction. This is the Transact-SQL equivalent to the Oracle dbms_lock.sleep.

WAITFOR {DELAY 'time' | TIME 'time'}

where

DELAY

Instructs Microsoft SQL Server to wait until the specified amount of time has passed, up to a maximum of 24 hours.

'time'

The amount of time to wait. time can be specified in one of the acceptable formats for datetime data, or it can be specified as a local variable. Dates cannot be specified; therefore, the data portion of the datetime value is not allowed.

TIME

Instructs SQL Server to wait until the specified time.

For example:

BEGIN WAITFOR TIME '22:20' EXECUTE update_all_stats END

Specifying Parameters in a Stored Procedure

To specify a parameter within a stored procedure, use this syntax.

Triggers

Both Oracle and Microsoft SQL Server have triggers, which have some differences in their implementations.

DELETED and INSERTED are logical (conceptual) tables created by SQL Server for trigger statements. They are structurally similar to the table on which the trigger is defined and hold the old values or new values of the rows that might be changed by the user action. The tables track row-level changes in Transact-SQL. These tables provide the same functionality as Oracle row-level triggers. When an INSERT, UPDATE, or DELETE statement is executed in SQL Server, rows are added to the trigger table and to the INSERTED and DELETED table(s) simultaneously.

The INSERTED and DELETED tables are identical to the trigger table. They have the same column names and the same data types. For example, if a trigger is placed on the GRADE table, the INSERTED and DELETED tables have this structure.

The INSERTED and DELETED tables can be examined by the trigger to determine what types of trigger actions should be carried out. The INSERTED table is used with the INSERT and UPDATE statements. The DELETED table is used with DELETE and UPDATE statements.

The UPDATE statement uses both the INSERTED and DELETED tables because SQL Server always deletes the old row and inserts a new row whenever an UPDATE operation is performed. Consequently, when an UPDATE is performed, the rows in the INSERTED table are always duplicates of the rows in the DELETED table.

The following example uses the INSERTED and DELETED tables to replace a PL/SQL row-level trigger. A full outer join is used to query all rows from either table.

You can create a trigger only in the current database, though you can reference objects outside the current database. If you use an owner name to qualify a trigger, qualify the table name the same way.

Triggers can be nested 32 levels deep. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled. Additionally, if an update trigger on one column of a table results in an update to another column, the update trigger is activated only once.

Microsoft SQL Server declarative referential integrity (DRI) does not provide cross-database referential integrity. If cross-database referential integrity is required, use triggers.

The following statements are not allowed in a Transact-SQL trigger:

For more information about triggers, see SQL Server Books Online.

Top of page

Transactions, Locking, and Concurrency

This section explains how transactions are executed in both Oracle and Microsoft SQL Server and presents the differences between the locking processes and concurrency issues in both database types.

Transactions

In Oracle, a transaction is started automatically when an insert, update, or delete operation is performed. An application must issue a COMMIT command to save all changes to the database. If a COMMIT is not performed, all changes are rolled back or undone automatically.