Migrating Oracle Databases to Microsoft SQL Server 7.0

February 1999

Microsoft Corporation

Summary: This article 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.

The target audience for this article should possess:

• A strong background in Oracle RDBMS fundamentals.
  
  
• General database management knowledge.
  
  
• Familiarity with the Oracle SQL and PL/SQL languages.
  
  
• A working knowledge of the C/C++ programming language.
  
  
• Membership in the sysadmin fixed server role.

This article 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.

Contents

Development and Application Platforms
Overview
Organization of this Article
Architecture and Terminology
Installing and Configuring Microsoft SQL Server
Defining Database Objects
Enforcing Data Integrity and Business Rules
Transactions, Locking, and Concurrency
Deadlocks
SQL Language Support
Implementing Cursors
Tuning SQL Statements
Using ODBC
Developing and Administering Database Replication
Migrating Your Data and Applications
Database Examples

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.

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 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 Article

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.

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 article.

Definition of Database

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

• Oracle database processes and buffers (instance).
  
  
• SYSTEM tablespace containing one centralized system catalog, which is made up of one or more datafiles.
  
  
• Other tablespaces as defined by the DBA (optional), each made up of one or more datafiles.
  
  
• Two or more online Redo Logs.
  
  
• Archived Redo Logs (optional).
  
  
• Miscellaneous other files (control file, Init.ora,  config.ora, etc.).

A Microsoft SQL Server database provides a logical separation of data, applications, and security mechanisms.  A SQL Server installation can support multiple databases. Applications built using SQL Server can use databases to logically divide business functionality.

Each SQL Server database can support filegroups, which provide the ability to physically distribute the placement of the data.  A SQL Server filegroup categorizes the operating system files containing data from a single SQL Server database to simplify database administration tasks, such as backup. A filegroup is a property of a SQL Server database and cannot contain the operating system files of more than one database, though a single database can contain more than one filegroup. After database creation, filegroups can be added to the database.

Microsoft SQL Server also installs the following databases by default:

• The model database is a template for all newly created user databases.
  
  
• The tempdb database is similar to an Oracle temporary tablespace in that it is used for temporary working storage and sort operations. Unlike the Oracle temporary tablespace, users can create temporary tables that are automatically dropped when the user logs off.
  
  
• The msdb database supports the SQL Server Agent and its scheduled jobs, alerts, and replication information.
  
  
• The pubs and Northwind databases are provided as sample databases for training.

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:

• Database objects (tables, indexes, stored procedures, views, triggers, and so on).
  
  
• Constraints.
  
  
• Users and permissions.
  
  
• User-defined data types.
  
  
• Replication definitions.
  
  
• Files used by the database.

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:

• Database names and the primary file location for each database.
  
  
• SQL Server login accounts.
  
  
• System messages.
  
  
• Database configuration values.
  
  
• Remote and/or linked servers.
  
  
• Current activity information.
  
  
• System stored procedures.

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:

• Distribute large tables across multiple files to improve I/O throughput.
  
  
• Store indexes on different files than their respective tables, again to improve I/O throughput and disk concurrency.
  
  
• Store text, ntext, and image columns (large objects) on separate files from the table.
  
  
• Place database objects on specific disk spindles.
  
  
• Back up and restore individual tables or sets of tables within a filegroup.

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

  A database backup makes a copy of the full database. Not all pages are copied to the backup set, only those actually containing data. Both data pages and transaction log pages are copied to the backup set.

  A database backup set is used to re-create the database as it was at the time the BACKUP statement completed. If only database backups exist for a database, it can be recovered only to the time of the last database backup taken before the failure of the server or database.  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 backup, filegroup backup

  A file or filegroup backup copies one or more files of a specified database, allowing a database to be backed up in smaller units: at the file or filegroup level. 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 security.

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:

• The login account can be specified as a database user.
  
  
• The login account can use a guest account in the database.
  
  
• A Windows NT group login can be mapped to a database role. Individual Windows NT accounts that are members of that group can then connect to the 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:

• Installing SQL Server.
  
  
• Configuring servers and clients.
  
  
• Creating databases.*
  
  
• Establishing login rights and user permissions.*
  
  
• Transferring data in and out of SQL Server databases.*
  
  
• Backing up and restoring databases.*
  
  
• Implementing and maintaining replication.
  
  
• Scheduling unattended operations.*
  
  
• Monitoring and tuning SQL Server performance.*
  
  
• Diagnosing system problems.

*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:

• Managing database access.
  
  
• Changing database options (read-only, single user, and so on).
  
  
• Backing up and restoring the database contents.
  
  
• Granting and revoking database permissions.
  
  
• Creating and dropping database objects.

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.

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:

1. Use Windows NT software–based RAID or hardware-based RAID level 5 to create a logical drive large enough to hold all of your data. An estimate of space can be calculated by adding the total file space used by the Oracle system, temporary, and application tablespaces.
   
   
2. Create a second logical drive for holding transaction logs by using Windows NT software–based RAID or hardware-based RAID level 1. The size of this drive should be at least as large as the sum of the size of the online redo and rollback segment tablespace(s).
   
   
3. Use SQL Server Enterprise Manager to create a database with the same name as the Oracle application's tablespace. (The sample application uses the database name USER_DB.) Specify the file locations to coincide with the disks you created in steps 1 and 2 for the data and transaction logs, respectively. If you are using multiple Oracle tablespaces, it is not necessary or even recommended that you create multiple SQL Server databases. RAID will distribute the data for you.
   
   
4. Create the SQL Server login accounts:

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

5. Add the roles to the database:

   USE USER_DB
   EXEC SP_ADDROLE DATA_ADMIN
   EXEC SP_ADDROLE USER_LOGON
   GO
   

6. Grant permissions to the roles:

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

7. Add the login accounts as database user accounts:

   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.

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:

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

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:

• Using the database name and username is optional. When a table is referenced only by name (for example, STUDENT), SQL Server searches for that table in the current user's account in the current database. If it does not find one, it looks for an object of the same name owned by the reserved username of dbo in the database. Table names must be unique within a user's account within a database.
  
  
• The same SQL Server login account can own tables with the same name in multiple databases. For example, the ENDUSER1 account owns the following database objects: USER_DB.ENDUSER1.STUDENT and OTHER_DB.ENDUSER1.STUDENT. The qualifier is the database username, not the SQL Server login name, because they do not have to be the same.

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

• USER_DB.DBO.STUDENT
  
  
• USER_DB.DEPT_ADMIN.STUDENT
  
  
• USER_DB.STUDENT_ADMIN.STUDENT
  
  
• OTHER_DB.DBO.STUDENT

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.

• Every connection to SQL Server has a current database context, set at login time with the USE statement. For example, assume the following scenario:

  A user, using the ENDUSER1 account, is logged in to the USER_DB database. The user requests the STUDENT table. SQL Server searches for the table ENDUSER1.STUDENT. If the table is found, SQL Server performs the requested database operation on USER_DB.ENDUSER1.STUDENT. If the table is not found in the ENDUSER1 database account, SQL Server searches for USER_DB.DBO.STUDENT in the dbo account for that database. If the table is still not found, SQL Server returns an error message indicating the table does not exist.

• If another user, for example DEPT_ADMIN, owns the table, the table name must be prefixed with the database user's name (DEPT_ADMIN.STUDENT). Otherwise, the database name defaults to the database that is currently in context.
  
  
• If the referenced table exists in another database, the database name must be used as part of the reference. For example, to access the STUDENT table owned by ENDUSER1 in the OTHERDB database, use OTHER_DB.ENDUSER1.STUDENT.

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:

1. STUDENT_DB.current_user.STUDENT
   
   
2. STUDENT_DB.DBO.STUDENT

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 –based 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:

• Data modification statements (INSERT or UPDATE) are allowed on multitable views if the data modification statement affects only one base table. Data modification statements cannot be used on more than one table in a single statement.
  
  
• READTEXT or WRITETEXT cannot be used on text or image columns in views.
  
  
• ORDER BY, COMPUTE, FOR BROWSE, or COMPUTE BY clauses cannot be used.
  
  
• The INTO keyword cannot be used in a view.

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:

• Primary keys.
  
  
• Columns that are not updated.
  
  
• Queries that return a range of values, using operators such as BETWEEN, >, >=, <, and <=, for example:

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

• Queries that return large result sets:

  SELECT * FROM STUDENT WHERE LNAME = 'SMITH'
  

• Columns that are used in sort operations (ORDER BY, GROUP BY).

  For example, on the STUDENT table, it might be helpful to include a nonclustered index on the primary key of ssn, and a clustered index could be created on lname, fname, (last name, first name), because this is the way students are often grouped.

• Distributing update activity in a table to avoid hot spots. Hot spots are often caused by multiple users inserting into a table with an ascending key. This application scenario is usually addressed by row-level locking.

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

While some data type conversions from Oracle to SQL Server are straightforward, other data type conversions will require evaluating a few options.. It is recommended that you use the DTS Wizard to automate the creation of the new CREATE TABLE statements. These statements will provide you with the recommended conversion of the data types. 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.

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 KEYand UNIQUE). It is recommended that you use the NOT FOR REPLICATION clause to suspend column-level, foreign key, and CHECK constraints during replication. 

In cases where you are not replicating data, and need to remove a constraint, 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-based 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:

• CREATE statements (DATABASE, TABLE, INDEX, PROCEDURE, DEFAULT, RULE, TRIGGER, SCHEMA, and VIEW)
  
  
• DROP statements (TRIGGER, INDEX, TABLE, PROCEDURE, DATABASE, VIEW, DEFAULT, RULE)
  
  
• ALTER statements (DATABASE, TABLE, VIEW, PROCEDURE, TRIGGER)
  
  
• TRUNCATE TABLE
  
  
• GRANT, REVOKE, DENY
  
  
• UPDATE STATISTICS
  
  
• RECONFIGURE
  
  
• UPDATE STATISTICS
  
  
• RESTORE DATABASE, RESTORE LOG
  
  
• LOAD LOG, DATABASE
  
  
• DISK statements
  
  
• SELECT INTO (because it creates a table)

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

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 changes to the database. If a COMMIT is not performed, all changes are rolled back or undone automatically.

By default, Microsoft SQL Server automatically performs a COMMIT statement after every insert, update, or delete operation. Because the data is automatically saved, you are unable to roll back any changes.

You can start transactions in Microsoft® SQL Server™ as explicit, autocommit, or implicit transactions. Autocommit is the default behavior; you can use implicit or explicit transaction modes to change this default behavior.

Autocommit transactions

This is the default mode for SQL Server. Each individual Transact-SQL statement is committed when it completes. You do not have to specify any statements to control transactions.

Implicit transactions

As in Oracle, an implicit transaction is started whenever an INSERT, UPDATE, DELETE, or other data manipulating function is performed. To allow implicit transactions, use the SET IMPLICIT_TRANSACTIONS ON statement.

If this option is ON and there are no outstanding transactions, every SQL statement automatically starts a transaction. If there is an open transaction, no new transaction is started. The open transaction must be committed by the user explicitly with the COMMIT TRANSACTION statement for the changes to take effect and for all locks to be released.

Explicit transactions

An explicit transaction is a grouping of SQL statements surrounded by the following transaction delimiters. Note that BEGIN TRANSACTION and COMMIT TRANSACTION are required:

• BEGIN TRANSACTION [transaction_name]
  
  
• COMMIT TRANSACTION [transaction_name]
  
  
• ROLLBACK TRANSACTION [transaction_name | savepoint_name]
  
  
• SAVE TRAN[SACTION] {savepoint_name | @savepoint_variable}

The SAVE TRANSACTION statement functions in the same way as the Oracle SAVEPOINT command, setting a savepoint in the transaction that allows partial rollbacks.

In the following example, the English department is changed to the Literature department. Note the use of the BEGIN TRANSACTION and COMMIT TRANSACTION statements.

Transactions can be nested one within another. If this occurs, the outermost pair creates and commits the transaction, and the inner pairs track the nesting level. When a nested transaction is encountered, the @@TRANCOUNT function is incremented. Usually, this apparent transaction nesting occurs as stored procedures or triggers with BEGIN…COMMIT pairs calling each other. Although transactions can be nested, they have little effect on the behavior of ROLLBACK TRANSACTION statements.

In stored procedures and triggers, the number of BEGIN TRANSACTION statements must match the number of COMMIT TRANSACTION statements. A stored procedure or trigger that contains unpaired BEGIN TRANSACTION and COMMIT TRANSACTION statements produces an error message when executed. The syntax allows stored procedures and triggers to be called from within transactions if they contain BEGIN TRANSACTION and COMMIT TRANSACTION statements.

Wherever possible, break large transactions into smaller transactions. Make sure each transaction is well-defined within a single batch. To minimize possible concurrency conflicts, transactions should not span multiple batches nor wait for user input. Grouping many Transact-SQL statements into one long-running transaction can negatively affect recovery time and cause concurrency problems.

When programming with ODBC, you can select either the implicit or explicit transaction mode by using the SQLSetConnectOption function. An ODBC program's selection of one or the other depends on the AUTOCOMMIT connect option. If AUTOCOMMIT is ON (the default), you are in explicit mode. If AUTOCOMMIT is OFF, you are in implicit mode.

If you are issuing a script through SQL Server Query Analyzer or other query tools, you can either include the explicit BEGIN TRANSACTION statement shown previously, or start the script with the SET IMPLICIT_TRANSACTIONS ON statement. The BEGIN TRANSACTION approach is more flexible, and the implicit approach is more compatible with Oracle.

Locking and Transaction Isolation

One of the key functions of a database management system (DBMS) is to ensure that multiple users can read and write records in the database without reading inconsistent sets of records due to in-progress changes and without overwriting each other's changes inadvertently. Oracle and Microsoft® SQL Server™ approach this task with different locking and isolation strategies. You must consider these differences when you convert an application from Oracle to SQL Server or the resulting application may scale poorly to high numbers of users.

Oracle uses a multiversion consistency model for all SQL statements that read data, either explicitly or implicitly. In this model, data readers by default neither acquire locks nor wait for other locks to be released before reading rows of data. When a reader requests data that has been changed but not yet committed by other writers, Oracle recreates the old data by using its rollback segments to reconstruct a snapshot of rows.

Data writers in Oracle request locks on data that is updated, deleted, or inserted. These locks are held until the end of a transaction, and they prevent other users from overwriting uncommitted changes.

SQL Server, in contrast, uses shared locks to ensure that data readers only see committed data. These readers take and release shared locks as they read data. These shared locks do not affect other readers. A reader waits for a writer to commit the changes before reading a record. A reader holding shared locks also blocks a writer trying to update the same data.

Releasing locks quickly for applications that support high numbers of users is more important in SQL Server than in Oracle. Releasing locks quickly is usually a matter of keeping transactions short. If possible, a transaction should neither span multiple round-trips to the server nor wait for the user to respond. You also need to code your application to fetch data as quickly as possible because unfetched data scans can hold share locks at the server and thus block updaters.

It is important in any RDBMS that locks be released quickly to provide the maximum concurrency. You can ensure you are releasing locks quickly by keeping transactions as short as possible. If possible, a transaction should not span multiple round-trips to the server, nor include user "think" time. If you use cursors, you also need to code your application to fetch data quickly, because unfetched data scans can hold share locks at the server and thus block updaters. For more information, see "Using ODBC" later in this chapter.

Dynamic Locking

SQL Server uses a dynamic locking strategy to determine the most cost-effective locks. SQL Server automatically determines what locks are most appropriate when the query is executed, based on the characteristics of the schema and query. For example, to reduce the overhead of locking, the optimizer may choose page-level locks in an index when performing an index scan. Dynamic locking has the following advantages:

• Simplified database administration, because database administrators no longer have to be concerned with adjusting lock escalation thresholds.
  
  
• Increased performance, because SQL Server minimizes system overhead by using locks appropriate to the task.
  
  
• Application developers can concentrate on development, because SQL Server automatically adjusts locking.

Oracle's inability to escalate row-level locks can cause problems in queries that include the FOR UPDATE clause and in UPDATEs that request many rows. For example, assume that the STUDENT table has 100,000 rows, and an Oracle user issues the following statement—note that 100,000 rows are affected:

UPDATE STUDENT set (col) = (value);

The Oracle RDBMS locks every row in the STUDENT table, one row at a time; this can take quite a while, and can require many system resources. Oracle does not escalate the request to lock the entire table.

The same statement in SQL Server will cause the (default) row-level locks to escalate to a table-level lock, which is both efficient and fast.

Changing Default Locking Behavior

Both Microsoft SQL Server and Oracle use the same default transaction isolation level—"READ COMMITTED." Both databases also allow the developer to request nondefault locking and isolation behavior. In Oracle, the most common mechanisms for this are the FOR UPDATE clause on a SELECT command, the SET TRANSACTION READ ONLY command, and the explicit LOCK TABLE command.

Because their locking and isolation strategies are so different, it is difficult to map these locking options directly between Oracle and SQL Server. To obtain a better understanding of this process, it is important to understand the options that SQL Server provides for changing its default locking behavior.

In SQL Server, the most common mechanisms for changing default locking behavior are the SET TRANSACTION ISOLATION LEVEL statement and the locking hints that are supported in the SELECT and UPDATE statements. The SET TRANSACTION ISOLATION LEVEL statement sets transaction isolation levels for the duration of a user's session. This becomes the default behavior for the session unless a locking hint is specified at the table level in the FROM clause of an SQL statement. The transaction isolation is set like this:

SET TRANSACTION ISOLATION LEVEL 
    {
        READ COMMITTED 
        | READ UNCOMMITTED 
        | REPEATABLE READ 
        | SERIALIZABLE
    } 

READ COMMITTED

This option is the SQL Server default.  Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data.

READ UNCOMMITTED

Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.

REPEATABLE READ

Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.

SERIALIZABLE

A range lock is placed on the data set preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

SQL Server implements all four ANSI standard transaction isolation levels; Oracle only implements "READ COMMITTED," which is the default, and "SERIALIZABLE."

SQL Server does not directly support the non-ANSI standard READ ONLY transaction isolation level offered by Oracle. If a transaction in an application requires repeatable read behavior, you may need to use the SERIALIZABLE isolation level offered by SQL Server. If all of the database access is read only, you can improve performance by setting the SQL Server database option to READ ONLY.

SELECT…FOR UPDATE

The SELECT…FOR UPDATE statement in Oracle is used when an application needs to issue a positioned update or delete on a cursor using the WHERE CURRENT OF syntax. In this case, optionally remove the FOR UPDATE clause; SQL Server cursors are updateable by default.

SQL Server cursors usually do not hold locks under the fetched row. Rather, they use an optimistic concurrency strategy to prevent updates from overwriting each other. If one user attempts to update or delete a row that has been changed since it was read into the cursor, SQL Server detects the problem and issues an error message. The application can trap this error message and retry the update or delete as appropriate.

The optimistic technique supports higher concurrency in the normal case where conflicts between updaters are rare. If your application really needs to ensure that a row cannot be changed after it is fetched, you can use the UPDLOCK hint in your SELECT statement to achieve this effect.

This hint does not block other readers, but it prevents any other potential writers from also obtaining an update lock on the data. When using ODBC, you can also achieve a similar effect using SQLSETSTMTOPTION (…,SQL_CONCURRENCY)= SQL_CONCUR_LOCK. Either of these options reduces concurrency.

Explicitly Requesting Table-level Locks

Microsoft SQL Server can provide the same table-locking functionality as Oracle.

Handling Deadlocks

A deadlock occurs when one process locks a page or table needed by another process, and the second process locks a page that the first process needs. A deadlock is also known as a deadly embrace. SQL Server automatically detects and resolves deadlocks. If a deadlock is found, the server terminates the user process that has completed the deadly embrace.

After every data modification, your program code should check for message number 1205, which indicates a deadlock. If this message number is returned, a deadlock has occurred and the transaction was rolled back. In this situation, your application must restart the transaction.

Deadlocks can usually be avoided by using a few simple techniques:

• Access tables in the same order in all parts of your application.
  
  
• Use a clustered index on every table to force an explicit row ordering.
  
  
• Keep transactions short.

For more information, see the Microsoft Knowledge Base article, "Detecting and Avoiding Deadlocks in Microsoft SQL Server."

Remote Transactions

To perform remote transactions in Oracle, you must have access to a remote database node with a database link. In SQL Server, you must have access to a remote server. A remote server is a server running SQL Server on the network that users can access by using their local server. When a server is set up as a remote server, users can use the system procedures and the stored procedures on it without explicitly logging in to it.

Remote servers are set up in pairs. You must configure both servers to recognize each other as remote servers. The name of each server must be added to its partner with the sp_addlinkedserver system stored procedure or SQL Server Enterprise Manager.

After you set up a remote server, use the sp_addremotelogin system stored procedure or SQL Server Enterprise Manager to set up remote login IDs for the users who must access that remote server. After this step is completed, you must grant permissions to execute the stored procedures.

The EXECUTE statement is then used to run procedures on the remote server. This example executes the validate_student stored procedure on the remote server STUDSVR1 and stores the return status indicating success or failure in @retvalue1:

DECLARE @retvalue1 int
EXECUTE @retvalue = STUDSVR1.student_db.student_admin.validate_student    '111111111'

For more information, see SQL Server Books Online.

Distributed Transactions

Oracle automatically initiates a distributed transaction if changes are made to tables in two or more networked database nodes. SQL Server distributed transactions use the two-phase commit services of the Microsoft Distributed Transaction Coordinator (MS DTC) included with SQL Server.

By default, SQL Server must be instructed to participate in a distributed transaction. SQL Server's participation in an MS DTC transaction can be started by either of the following:

• The BEGIN DISTRIBUTED TRANSACTION statement. This statement begins a new MS DTC transaction.
  
  
• A client application calling DTC transaction interfaces directly.

In the example, notice the distributed update to both the local table GRADE and the remote table CLASS (using a class_name procedure):

BEGIN DISTRIBUTED TRANSACTION
UPDATE STUDENT_ADMIN.GRADE
   SET GRADE = 'B+' WHERE SSN = '111111111' AND CCODE = '1234'
DECLARE @retvalue1 int
EXECUTE @retvalue1 = CLASS_SVR1.dept_db.dept_admin.class_name '1234',    'Basketweaving'
COMMIT TRANSACTION
GO

If the application cannot complete the transaction, the application program cancels it by using the ROLLBACK TRANSACTION statement. If the application fails or a participating resource manager fails, MS DTC cancels the transaction. MS DTC does not support distributed savepoints or the SAVE TRANSACTION statement. If an MS DTC transaction is aborted or rolled back, the entire transaction is rolled back to the beginning of the distributed transaction, regardless of any savepoints.

Two-Phase Commit Processing

The Oracle and MS DTC two-phase commit mechanisms are similar in operation. In the first phase of a SQL Server two-phase commit, the transaction manager requests each enlisted resource manager to prepare to commit. If any resource manager cannot prepare, the transaction manager broadcasts an abort decision to everyone involved in the transaction.

If all resource managers can successfully prepare, the transaction manager broadcasts the commit decision. This is the second phase of the commit process. While a resource manager is prepared, it is in doubt about whether the transaction is committed or aborted. MS DTC keeps a sequential log so that its commit or abort decisions are durable. If a resource manager or transaction manager fails, they reconcile in-doubt transactions when they reconnect.

SQL Language Support

This section outlines the similarities and differences between Transact-SQL and PL/SQL language syntax and presents conversion strategies.

SELECT and Data Manipulation Statements

Use the following when migrating your Oracle DML statements and PL/SQL programs to SQL Server.

1. Verify that the syntax of all SELECT, INSERT, UPDATE, and DELETE statements is valid. Make any required modifications.
   
   
2. Change all outer joins to SQL-92 standard outer join syntax.
   
   
3. Replace Oracle functions with the appropriate SQL Server functions.
   
   
4. Check all comparison operators.
   
   
5. Replace the "||" string concatenation operator with the "+" string concatenation operator.
   
   
6. Replace PL/SQL programs with Transact-SQL programs.
   
   
7. Change all PL/SQL cursors to either noncursor SELECT statements or Transact-SQL cursors.
   
   
8. Replace PL/SQL procedures, functions, and packages with Transact-SQL procedures.
   
   
9. Convert PL/SQL triggers to Transact-SQL triggers.
   
   
10. Use the SET SHOWPLAN statement to tune your queries for performance.

SELECT statements

The SELECT statement syntax used by Oracle and Microsoft SQL Server is similar.

Oracle-specific cost-based optimizer hints are not supported by SQL Server, and must be removed. The recommended technique is to use SQL Server cost-based optimization. For more information, see "Tuning SQL Statements" later in this chapter.

SQL Server does not support the Oracle START WITH…CONNECT BY clause. You can replace this in SQL Server by creating a stored procedure that performs the same task.

The Oracle INTERSECT and MINUS set operators are not supported by SQL Server. The SQL Server EXISTS and NOT EXISTS clauses can be used to accomplish the same result.

The following example uses the INTERSECT operator to find the course code and course name for all classes that have students. Notice how the EXISTS operator replaces the use of the INTERSECT operator. The data that is returned is identical.

This example uses the MINUS operator to find those classes that do not have any students.

INSERT statements

The INSERT statement syntax used by Oracle and Microsoft SQL Server is similar.

The Transact-SQL language supports inserts into tables and views, but does not support INSERT operations into SELECT statements. If your Oracle application code performs inserts into SELECT statements, this must be changed.

The Transact-SQL values_list parameter offers the SQL-92 standard keyword DEFAULT, which is not supported by Oracle. This keyword specifies that the default value for the column be used when an insert is performed. If a default value does not exist for the specified column, a NULL is inserted. If the column does not allow NULLs, an error message is returned. If the column is defined as a timestamp data type, the next sequential value is inserted.

The DEFAULT keyword cannot be used with an identity column. To generate the next sequential number, columns with the IDENTITY property must not be listed in the column_list or values_clause. You do not have to use the DEFAULT keyword to obtain the default value for a column. As in Oracle, if the column is not referenced in the column_list and it has a default value, the default value is placed in the column. This is the most compatible approach to use when performing the migration.

One useful Transact-SQL option (EXECute procedure_name) is to execute a procedure and pipe its output into a target table or view. Oracle does not allow you to do this.

UPDATE statements

Because Transact-SQL supports most of the syntax used by the Oracle UPDATE command, minimal revision is required.

The Transact-SQL UPDATE statement does not support update operations against SELECT statements. If your Oracle application code performs updates against SELECT statements, you can turn the SELECT statement into a view, and then use the view name in the SQL Server UPDATE statement. See the example shown previously in "INSERT Statements."

The Oracle UPDATE command can use only program variables from within a PL/SQL block. The Transact-SQL language does not require the use of blocks to use variables.

The keyword DEFAULT can be used to set a column to its default value in SQL Server. You cannot set a column to a default value with the Oracle UPDATE command.

Transact-SQL and Oracle SQL support the use of subqueries in an UPDATE statement. However, the Transact-SQL FROM clause can be used to create an UPDATE based on a join. This capability makes your UPDATE syntax more readable and in some cases can improve performance.

DELETE statements

In most cases, you do not need to modify DELETE statements. If you perform deletes against SELECT statements in Oracle, you must modify the syntax for SQL Server because this functionality is not supported by Transact-SQL.

Transact-SQL supports the use of subqueries in the WHERE clause, as well as joins in the FROM clause. The latter can produce more efficient statements. See the example shown previously in "UPDATE Statements."

TRUNCATE TABLE statement

The TRUNCATE TABLE syntax used by Oracle and Microsoft SQL Server is similar. TRUNCATE TABLE is used to remove all of the rows from a table and cannot be rolled back. The table structure and all of its indexes continue to exist. DELETE triggers are not executed. If a table is referenced by a FOREIGN KEY constraint, it cannot be truncated.

In SQL Server, this statement can be issued only by the table owner. In Oracle, this command can be issued if you are the table owner or have the DELETE TABLE system privilege.

The Oracle TRUNCATE TABLE command can optionally release the storage space occupied by the rows in the table. The SQL Server TRUNCATE TABLE statement always reclaims space occupied by the table data and its associated indexes.

Manipulating data in identity and timestamp columns

Oracle sequences are database objects that are not directly related to any given table or column. The relationship between a column and a sequence is implemented in the application, by assigning the sequence value to a column programmatically. Therefore, Oracle does not enforce any rules when it works with sequences. However, in Microsoft SQL Server identity columns, values cannot be updated and the DEFAULT keyword cannot be used.

By default, data cannot be inserted directly into an identity column. The identity column automatically generates a unique, sequential number for each new row inserted in the table. This default can be overridden using the following SET statement:

SET IDENTITY_INSERT table_name ON

With IDENTITY_INSERT set to ON, the user can insert any value into the identity column of a new row. To prevent the entry of duplicate numbers, a unique index must be created against the column. The purpose of this statement is to allow a user to re-create a value for a row that has been deleted accidentally. The @@IDENTITY function can be used to obtain the last identity value.

The TRUNCATE TABLE statement resets an identity column to its original SEED value. If you do not want to reset the identity value for a column, use the DELETE statement without a WHERE clause instead of the TRUNCATE TABLE statement. You will have to evaluate how this affects your Oracle migration, because ORACLE SEQUENCES are not reset following the TRUNCATE TABLE command.

You can perform only inserts or deletes when working with timestamp columns. If you attempt to update a timestamp column, you receive this error message:

Msg 272, Level 16, State 1 Can't update a TIMESTAMP column.

Locking Requested Rows

Oracle uses the FOR UPDATE clause to lock rows specified in the SELECT command. You do not need to use the equivalent clause in Microsoft SQL Server because this is the default behavior.

Row aggregates and the compute clause

The SQL Server COMPUTE clause is used to generate row aggregate functions (SUM, AVG, MIN, MAX, and COUNT), which appear as additional rows in the query results. It allows you to see detail and summary rows in one set of results. You can calculate summary values for subgroups, and you can calculate more than one aggregate function for the same group.

The Oracle SELECT command syntax does not support the COMPUTE clause. Nevertheless, the SQL Server COMPUTE clause works just like the COMPUTE command found in the Oracle SQL*Plus query tool.

Join clauses

Microsoft SQL Server 7.0 allows up to 256 tables to be joined in a join clause, including both temporary and permanent tables. There is no join limit in Oracle.

When using outer joins in Oracle, the outer join operator (+) is placed typically next to the child (foreign key) column in the join. The (+) identifies the column with fewer unique values. This always occurs unless the foreign key allows null values, in which case (+) can be placed on the parent (PRIMARY KEY or UNIQUE constraint) column. You cannot place the (+) on both sides of the equal sign (=).

With SQL Server, you can use the *= and =* outer join operators. The * is used to identify the column that has more unique values. If the child (foreign key) column does not allow null values, the * is placed on the parent (PRIMARY KEY or UNIQUE constraint) column side of the equal sign. The placement of the * is essentially reversed in Oracle. You cannot place the * on both sides of the equal sign (=).

The *= and =* are considered legacy join operators. SQL Server also supports the SQL-92 standard join operators listed below. It is recommended that you use this syntax. The SQL-92 standard syntax is more powerful and has fewer restrictions than the * operators.

The following code examples return lists of classes taken by all students. Outer joins are defined between the student and grade tables that allow all students to appear, even those who are not enrolled in any classes. Outer joins are also added to the class table in order to return the class names. If outer joins are not added to the class tables, those students who are not enrolled in any classes are not returned because they have null course codes (CCODE).

Using SELECT statements as table names

Microsoft SQL Server and Oracle support the use of SELECT statements as the source of tables when performing queries. SQL Server requires an alias; the use of an alias is optional with Oracle.

Reading and modifying BLOBs

Microsoft SQL Server implements binary large objects (BLOBs) with text and image columns. Oracle implements BLOBs with LONG and LONG RAW columns. In Oracle, a SELECT command can query the values in LONG and LONG RAW columns.

In SQL Server, you can use a standard Transact-SQL statement or the specialized READTEXT statement to read data in text and image columns. The READTEXT statement allows you to read partial sections of a text or image column. Oracle does not provide an equivalent statement for working with LONG and LONG RAW columns.

The READTEXT statement makes use of a text_pointer, which can be obtained using the TEXTPTR function. The TEXTPTR function returns a pointer to the text or image column in the specified row or to the text or image column in the last row returned by the query if more than one row is returned. Because the TEXTPTR function returns a 16-byte binary string, it is best to declare a local variable to hold the text pointer, and then use the variable with READTEXT.

The READTEXT statement specifies how many bytes to return. The value in the @@TEXTSIZE function, which is the limit on the number of characters or bytes to be returned, supersedes the size specified by the READTEXT statement if it is less than the specified size for READTEXT.

The SET statement can be used with the TEXTSIZE parameter to specify the size, in bytes, of text data to be returned with a SELECT statement. If you specify a TEXTSIZE of 0, the size is reset to the default (4 KB). Setting the TEXTSIZE parameter affects the @@TEXTSIZE function. The SQL Server ODBC driver automatically sets the TEXTSIZE parameter when the SQL_MAX_LENGTH statement option is changed.

In Oracle, UPDATE and INSERT commands are used to change values in LONG and LONG RAW columns. In SQL Server, you can use standard UPDATE and INSERT statements, or you can use the UPDATETEXT and WRITETEXT statements. Both UPDATETEXT and WRITETEXT allow a nonlogged option, and UPDATETEXT allows for partial updating of a text or image column.

The UPDATETEXT statement can be used to replace existing data, delete existing data, or insert new data. Newly inserted data can be a constant value, table name, column name, or text pointer.

The WRITETEXT statement completely overwrites any existing data in the column it affects. Use WRITETEXT to replace text data and UPDATETEXT to modify text data. The UPDATETEXT statement is more flexible because it changes only a portion of a text of image value rather than the entire value.

For more information, see SQL Server Books Online.

Functions

The tables in this section show the relationship between Oracle and SQL Server scalar-valued and aggregate functions. Although the names appear to be the same, it is important to note that functions vary in numbers and types of arguments. Also, functions that are supplied only by Microsoft SQL Server are not mentioned in this list as this chapter is limited to easing migration from existing Oracle applications. Examples of functions not supported by Oracle are: degrees (DEGREES), PI (PI), and random number (RAND).

Number/mathematical functions

The following are number/mathematical functions supported by Oracle and their Microsoft SQL Server equivalents.

Character functions

The following are character functions supported by Oracle and their Microsoft SQL Server equivalents.

Date functions

The following are date functions supported by Oracle and their Microsoft SQL Server equivalents.

Conversion functions

The following are conversion functions supported by Oracle and their Microsoft SQL Server equivalents.

Other row-level functions

The following are other row-level functions supported by Oracle and their Microsoft SQL Server equivalents.

Aggregate functions

The following are aggregate functions supported by Oracle and their SQL Server equivalents.

Conditional tests

Both the Oracle DECODE statement and the Microsoft SQL Server CASE expression perform conditional tests. When the value in test_value matches any following expression, the related value is returned. If no match is found, the default_value is returned. If no default_value is specified, both DECODE and CASE return NULL if there is no match. The table shows the syntax as well as an example of a converted DECODE command.

The CASE expression can support the use of SELECT statements for performing Boolean tests, something the DECODE command does not allow. For more information about the CASE expression, see SQL Server Books Online.

Converting values to different data types

The Microsoft SQL Server CONVERT and CAST functions are multiple purpose conversion functions. They provide similar functionality, converting an expression of one data type to another data type, and supporting a variety of special date formats:

• CAST(expression AS data_type)
  
  
• CONVERT (data type[(length)], expression [, style])

CAST is a SQL-92 standard function. These functions perform the same operations as the Oracle TO_CHAR, TO_NUMBER, TO_DATE, HEXTORAW, and RAWTOHEX functions.

The data type is any system data type into which the expression is to be converted. User-defined data types cannot be used. The length parameter is optional and is used with char, varchar, binary, and varbinary data types. The maximum allowable length is 8000.

Notice how character strings are converted to dates. In Oracle, the default date format model is "DD-MON-YY." If you use any other format, you must provide an appropriate date format model. The CONVERT function automatically converts standard date formats without the need for a format model.

When converting from a date to a character string, the default output for the CONVERT function is "dd mon yyyy hh:mm:ss:mmm(24h)". A numeric style code is used to format the output to other types of date format models. For more information about the CONVERT function, see SQL Server Books Online.

The following table shows the default output for Microsoft SQL Server dates.

User-defined functions

Oracle PL/SQL functions can be used in Oracle SQL statements. This functionality can often be achieved in other ways with Microsoft SQL Server.

In the following example, the Oracle user-defined function GET_SUM_MAJOR is used to obtain a sum of tuition paid by major. It can be replaced in SQL Server by using a query as a table.

Comparison Operators

Oracle and Microsoft SQL Server comparison operators are nearly identical.

Pattern matches

The SQL Server LIKE keyword offers useful wildcard search options that are not supported by Oracle. In addition to supporting the % and _ wildcard characters common to both RDBMSs, the [ ] and [^] characters are also supported by SQL Server.

The [ ] character set is used to search for any single character within a specified range. For example, if you search for the characters a through f in a single character position, you can specify this with LIKE '[a-f]' or LIKE '[abcdef]'. The usefulness of these additional wildcard characters is shown in this table.

The [^] wildcard character set is used to specify characters NOT in the specified range. For example, if any character except a through f is acceptable, you use LIKE '[^a - f]' or  LIKE '[^abcdef]'.

For more information about the LIKE keyword, see SQL Server Books Online.

Using NULL in comparisons

Although Microsoft SQL Server traditionally has supported the SQL-92–standard as well as some nonstandard NULL behaviors, it supports the use of NULL in Oracle.

SET ANSI_NULLS should be set to ON for executing distributed queries.

The SQL Server ODBC driver and OLE DB Provider for SQL Server automatically SET ANSI_NULLS to ON when connecting. This setting can be configured in ODBC data sources, in ODBC connection attributes, or in OLE DB connection properties that are set in the application before connecting to SQL Server. SET ANSI_NULLS defaults to OFF for connections from DB-Library applications.

When SET ANSI_DEFAULTS is ON, SET ANSI_NULLS is enabled.

For more information about the use of NULL, see SQL Server Books Online.

String concatenation

Oracle uses two pipe symbols (||) as the string concatenation operator, and SQL Server uses the plus sign (+). This difference requires minor revision in your application program code.

Control-of-Flow Language

The control-of-flow language controls the flow of execution of SQL statements, statement blocks, and stored procedures. PL/SQL and Transact-SQL provide many of the same constructs, although there are some syntax differences.

Keywords

These are the keywords supported by each RDBMS.

Declaring variables

Transact-SQL and PL/SQL variables are created with the DECLARE keyword. Transact-SQL variables are identified with @) and, like PL/SQL variables, are initialized to a null value when they are first created.

Transact-SQL does not support the %TYPE and %ROWTYPE variable data type definitions. A Transact-SQL variable cannot be initialized in the DECLARE command. The Oracle NOT NULL and CONSTANT keywords cannot be used in Microsoft SQL Server data type definitions.

Like Oracle LONG and LONG RAW data types, text and image data types cannot be used for variable declarations. Additionally, the PL/SQL style record and table definitions are not supported.

Assigning variables

Oracle and Microsoft SQL Server offer these ways to assign values to local variables.

Here are some syntax examples.

Statement blocks

Oracle PL/SQL and Microsoft SQL Server Transact-SQL support the use of BEGIN…END terminology to specify statement blocks. Transact-SQL does not require the use of a statement block following the DECLARE statement. The BEGIN…END statement blocks are required in Microsoft SQL Server for IF statements and WHILE loops if more than one statement is executed.

Conditional processing

The Microsoft SQL Server Transact-SQL conditional statement includes IF and ELSE rather than the ELSIF statement in Oracle PL/SQL. Multiple IF statements can be nested to achieve the same effect. For extensive conditional tests, the CASE expression may be easier to read.

Repeated statement execution (looping)

Oracle PL/SQL provides the unconditional LOOP and FOR LOOP. Transact-SQL offers the WHILE loop and the GOTO statement for looping purposes.

WHILE Boolean_expression
   {sql_statement | statement_block}
   [BREAK] [CONTINUE]

The WHILE loop tests a Boolean expression for the repeated execution of one or more statements. The statement(s) are executed repeatedly as long as the specified expression evaluates to TRUE. If multiple statements are to be executed, they must be placed within a BEGIN…END block.

Statement execution can be controlled from inside the loop with the BREAK and CONTINUE keywords. The BREAK keyword causes an unconditional exit from the WHILE loop, and the CONTINUE keyword causes the WHILE loop to restart, skipping any statements that follow. The BREAK keyword is equivalent to the Oracle PL/SQL EXIT keyword. Oracle does not have an equivalent to CONTINUE.

GOTO statement

Both Oracle and Microsoft SQL Server have GOTO statements, with different syntax. The GOTO statement causes the execution of a Transact-SQL batch to jump to a label. None of the statements between the GOTO statement and the label are executed.

PRINT statement

The Transact-SQL PRINT statement performs the same operation as the PL/SQL RDBMS_OUTPUT.put_line procedure. It is used for printing user-specified messages.

The message limit for the PRINT statement is 8,000 characters. Variables that are defined using the char or varchar data type can be embedded in the printed statement. If any other data type is used, the CONVERT or CAST function must be used. Local variables, global variables, and text can be printed. Both single and double quotation marks can be used to enclose text.

Returning from stored procedures

Both Microsoft SQL Server and Oracle have RETURN statements. RETURN lets your program exit unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements following RETURN are not executed.

Raising program errors

The Transact-SQL RAISERROR statement returns a user-defined error message and sets a system flag to record that an error has occurred. It is similar in function to the PL/SQL raise_application_error exception handler.

The RAISERROR statement allows the client to retrieve an entry from the sysmessages table or build a message dynamically with user-specified severity and state information. When defined, this message is sent back to the client as a server error message.

RAISERROR ({msg_id | msg_str}, severity, state
   [, argument1 [, argument2]])
   [WITH options]

When converting your PL/SQL programs, it may not be necessary to use the RAISERROR statement. In the following code example, the PL/SQL program uses the raise_application_error exception handler, while the Transact-SQL program uses nothing. The raise_application_error exception handler has been included to prevent the PL/SQL program from possibly returning an ambiguous unhandled exception error message. Instead, it always returns the Oracle error message (SQLERRM) when an unanticipated problem occurs.

When a Transact-SQL program fails, it always returns a detailed error message to the client program. Therefore, unless some specialized error handling is required, the RAISERROR statement is not always needed.

Implementing Cursors

Oracle always requires that cursors be used with SELECT statements, regardless of the number of rows requested from the database. In Microsoft SQL Server, a SELECT statement that is not enclosed within a cursor returns rows to the client as a default result set. This is an efficient way to return data to a client application.

SQL Server provides two interfaces for cursor functions. When cursors are used in Transact-SQL batches or stored procedures, SQL statements can be used to declare, open, and fetch from cursors as well as positioned updates and deletes. When cursors from a DB-Library, ODBC, or OLEDB program are used, the SQL Server client libraries transparently call built-in server functions to handle cursors more efficiently.

When porting a PL/SQL procedure from Oracle, first determine whether cursors are needed to do the same function in Transact-SQL. If the cursor returns only a set of rows to the client application, use a noncursor SELECT statement in Transact-SQL to return a default result set. If the cursor is used to load data a row at a time into local procedure variables, you must use cursors in Transact-SQL.

Syntax

The table shows the syntax for using cursors.

Declaring a Cursor

Although the Transact-SQL DECLARE CURSOR statement does not support the use of cursor arguments, it does support local variables. The values of these local variables are used in the cursor when it is opened. Microsoft SQL Server offers many additional capabilities in its DECLARE CURSOR statement.

The INSENSITIVE option is used to define a cursor that makes a temporary copy of the data to be used by that cursor. All of the requests to the cursor are answered by this temporary table. Consequently, modifications made to base tables are not reflected in the data returned by fetches made to this cursor. Data accessed by this type of cursor cannot be modified.

Applications can request a cursor type and then execute a Transact-SQL statement that is not supported by server cursors of the type requested. SQL Server returns an error that indicates the cursor type has changed, or given a set of factors, implicitly converts a cursor. For a complete list of factors that trigger SQL Server 7.0 to implicitly convert a cursor from one type to another, see SQL Server Books Online.

The SCROLL option allows backward, absolute, and relative fetches in addition to forward fetches. A scroll cursor uses a keyset cursor model in which committed deletes and updates made to the underlying tables by any user are reflected in subsequent fetches. This is true only if the cursor is not declared with the INSENSITIVE option.

If the READ ONLY option is chosen, updates are prevented from occurring against any row within the cursor. This option overrides the default capability of a cursor to be updated.

The UPDATE [OF column_list] statement is used to define updatable columns within the cursor. If [OF column_list] is supplied, only the columns listed allow modifications. If no list is supplied, all of the columns can be updated unless the cursor has been defined as READ ONLY.

It is important to note that the name scope for a SQL Server cursor is the connection itself. This is different from the name scope of a local variable. A second cursor with the same name as an existing cursor on the same user connection cannot be declared until the first cursor is deallocated.

Opening a Cursor

Transact-SQL does not support the passing of arguments to a cursor when it is opened, unlike PL/SQL. When a Transact-SQL cursor is opened, the result set membership and ordering are fixed. Updates and deletes that have been committed against the base tables of the cursor by other users are reflected in fetches made against all cursors defined without the INSENSITIVE option. In the case of an INSENSITIVE cursor, a temporary table is generated.

Fetching Data

Oracle cursors can move in a forward direction only—there is no backward or relative scrolling capability. SQL Server cursors can scroll forward and backward with the fetch options shown in the following table. These fetch options can be used only when the cursor is declared with the SCROLL option.

The Transact-SQL FETCH statement does not require the INTO clause. If return variables are not specified, the row is automatically returned to the client as a single-row result set. However, if your procedure must get the rows to the client, a noncursor SELECT statement is much more efficient.

The @@FETCH_STATUS function is updated following each FETCH. It is similar in use to the CURSOR_NAME%FOUND and CURSOR_NAME%NOTFOUND variables used in PL/SQL. The @@FETCH_STATUS function is set to the value of 0 following a successful fetch. If the fetch tries to read beyond the end of the cursor, a value of –1 is returned. If the requested row has been deleted from the table after the cursor was opened, the @@FETCH_STATUS function returns –2. The value of –2 usually occurs only in a cursor declared with the SCROLL option. This variable must be checked following each fetch to ensure the validity of the data.

SQL Server does not support Oracle's cursor FOR loop syntax.

CURRENT OF Clause

The CURRENT OF clause syntax and function for updates and deletes is the same in both PL/SQL and Transact-SQL. A positioned UPDATE or DELETE is performed against the current row within the specified cursor.

Closing a Cursor

The Transact-SQL CLOSE CURSOR statement closes the cursor but leaves the data structures accessible for reopening. The PL/SQL CLOSE CURSOR statement closes and releases all data structures.

Transact-SQL requires the use of the DEALLOCATE CURSOR statement to remove the cursor data structures. The DEALLOCATE CURSOR statement is different from CLOSE CURSOR in that a closed cursor can be reopened. The DEALLOCATE CURSOR statement releases all data structures associated with the cursor and removes the definition of the cursor.

Cursor Example

The example below shows equivalent cursor statements in PL/SQL and Transact-SQL.

Tuning SQL Statements

This section provides information about several SQL Server tools you can use to tune Transact-SQL statements. For more information about tuning your SQL Server database, see "Performance Tuning" earlier in this volume.

SQL Server Query Analyzer

You can use the graphical showplan feature of SQL Server Query Analyzer to learn more about how the optimizer will process your statement.

SQL Server Profiler

This graphical tool captures a continuous record of server activity in real-time. SQL Server Profiler monitors many different server events and event categories, filters these events with user-specified criteria, and outputs a trace to the screen, a file, or another SQL Server.

SQL Server Profiler can be used to:

• Monitor the performance of SQL Server.
  
  
• Debug Transact-SQL statements and stored procedures.
  
  
• Identify slow-executing queries.
  
  
• Troubleshoot problems in SQL Server by capturing all the events that lead up to a particular problem, and then replaying the events on a test system to replicate and isolate the problem.
  
  
• Test SQL statements and stored procedures in the development phase of a project by single-stepping through statements, one line at a time, to confirm that the code works as expected.
  
  
• Capture events on a production system and replay those captured events on a test system, thereby re-creating what happened in the production environment for testing or debugging purposes. Replaying captured events on a separate system allows the users to continue using the production system without interference.

SQL Server Profiler provides a graphical user interface to a set of extended stored procedures. You can also use these extended stored procedures directly. Therefore, it is possible to create your own application that uses the SQL Server Profiler extended stored procedures to monitor SQL Server.

SET Statement

The SET statement can set SQL Server query-processing options for the duration of your work session, or for the duration of a running trigger or a stored procedure.

The SET FORCEPLAN ON statement forces the optimizer to process joins in the same order as the tables appear in the FROM clause, similar to the ORDERED hint used with the Oracle optimizer.

The SET SHOWPLAN_ALL and SET SHOWPLAN_TEXT statements return only query or statement execution plan information and do not execute the query or statement. To execute the query or statement, set the appropriate showplan statement OFF. The query or statement will then execute. The SHOWPLAN option provides results similar to the Oracle EXPLAIN PLAN tool.

With SET STATISTICS PROFILE ON, each executed query returns its regular result set, followed by an additional result set that shows a profile of the query execution. Other options include SET STATISTICS IO and SET STATISTICS TIME.

Transact-SQL statement processing consists of two phases, compilation and execution. The NOEXEC option compiles each query but does not execute it. After NOEXEC is set ON, no subsequent statements are executed (including other SET statements) until NOEXEC is set OFF.

SET SHOWPLAN ON
SET NOEXEC ON
go
SELECT * FROM DEPT_ADMIN.DEPT,
   STUDENT_ADMIN.STUDENT
WHERE MAJOR = DEPT
go
STEP 1
The type of query is SETON
STEP 1
The type of query is SETON
STEP 1
The type of query is SELECT
FROM TABLE
DEPT_ADMIN.DEPT
Nested iteration
Table Scan
FROM TABLE
STUDENT_ADMIN.STUDENT
Nested iteration
Table Scan

Query Optimization

Oracle requires the use of hints to influence the operation and performance of its cost-based optimizer. The Microsoft SQL Server cost-based optimizer does not require the use of hints to assist in its query evaluation process. They are offered, however, as some situations do warrant their use.

The INDEX = {index_name | index_id} hint specifies the index name or ID to use for that table. An index_id of 0 forces a table scan, while an index_id of 1 forces the use of a clustered index, if it exists. This is similar to the index hints used in Oracle.

The SQL Server FASTFIRSTROW hint directs the optimizer to use a nonclustered index if its column order matches the ORDER BY clause. This hint operates in a similar fashion to the Oracle FIRST_ROWS hint.

Using ODBC

This section provides information about the ways Oracle and SQL Server use ODBC and information about developing or migrating applications with ODBC.

Recommended Conversion Strategy

Use the following process when you convert your application code from Oracle to SQL Server:

1. Consider converting your application to ODBC if it is written using Oracle Pro*C or the Oracle Call Interface (OCI).
   
   
2. Understand SQL Server default result sets and cursor options, and choose the fetching strategy that is most efficient for your application.
   
   
3. Remap Oracle ODBC SQL data types to SQL Server ODBC SQL data types where appropriate.
   
   
4. Use the ODBC Extended SQL extensions to create generic SQL statements.
   
   
5. Determine if manual commit mode is required for the SQL Server application.
   
   
6. Test the performance of your application(s) and modify the program(s) as necessary.

ODBC Architecture

Microsoft provides both 16-bit and 32-bit versions of its the ODBC SQL Server driver. The 32-bit ODBC SQL Server driver is thread-safe. The driver serializes shared access by multiple threads to shared statement handles (hstmt), connection handles (hdbc), and environment handles (henv). However, the ODBC program is still responsible for keeping operations within statements and connection spaces in the proper sequence, even when the program uses multiple threads.

Because the ODBC driver for Oracle can be supplied by one of many possible vendors, there are many possible scenarios regarding architecture and operation.You must contact the vendor to ensure that the ODBC driver meets your application's requirements.

In most cases, the ODBC driver for Oracle uses SQL*Net to connect to the Oracle RDBMS. SQL*Net may not be used, however, when connecting to Personal Oracle.

The illustration shows the application/driver architecture for 32-bit environments.

The term thunking means intercepting a function call, doing a special processing to translate between 16-bit and 32-bit code, and then transferring control to a target function. Note how the ODBC Cursor Library optionally resides between the driver manager and its driver. This library provides scrollable cursor services on top of drivers that support only forward-only cursors.

Forward-Only Cursors

Oracle and SQL Server treat result sets and cursors differently. Understanding these differences is essential for successfully moving a client application from Oracle to SQL Server and having it perform optimally.

In Oracle, any result set from a SELECT command is treated as a forward-only cursor when fetched in the client application. This is true whether you are using ODBC, OCI, or Embedded SQL as your development tool.

By default, each Oracle FETCH command issued by the client program (for example, SQLFetch in ODBC) causes a round-trip across the network to the server to return one row. If a client application wants to fetch more than one row at a time across the network, it must set up an array in its program and perform an array fetch.

Between fetches, no locks are held at the server for a read-only cursor because of Oracle's multiversioning concurrency model. When the program specifies an updatable cursor with the FOR UPDATE clause, all of the requested rows in the SELECT command are locked when the statement is opened. These row-level locks remain in place until the program issues a COMMIT or ROLLBACK request.

In SQL Server, a SELECT statement is not always associated with a cursor at the server. By default, SQL Server simply streams all the result set rows from a SELECT statement back to the client. This streaming starts as soon as the SELECT is executed. Result set streams can also be returned by SELECT statements within stored procedures. Additionally, a single stored procedure or batch of commands can stream back multiple result sets in response to a single EXECUTE statement.

The SQL Server client is responsible for fetching these default result sets as soon as they are available. For default result sets, fetches at the client do not result in round-trips to the server. Instead, fetches from a default result set pull data from local network buffers into program variables. This default result set model creates an efficient mechanism to return multiple rows of data to the client in a single round-trip across this network. Minimizing network round-trips is usually the most important factor in client/server application performance.

Compared to Oracle's cursors, default result sets put some additional responsibilities on the SQL Server client application. The SQL Server client application must immediately fetch all the result set rows returned by an EXECUTE statement. If the application needs to present rows incrementally to other parts of the program, it must buffer the rows to an internal array. If it fails to fetch all result set rows, the connection to SQL Server remains busy.

If this occurs, no other work (such as UPDATE statements) can be executed on that connection until the entire result set rows are fetched or the client cancels the request. Moreover, the server continues to hold share locks on table data pages until the fetch has completed. The fact that these share locks are held until a fetch is complete make it mandatory that you fetch all rows as quickly as possible. This technique is in direct contrast to the incremental style of fetch that is commonly found in Oracle applications.

Server Cursors

Microsoft SQL Server offers server cursors to address the need for incremental fetching of result sets across the network. Server cursors can be requested in an application by simply calling SQLSetStmtOption to set the SQL_CURSOR_TYPE option.

When a SELECT statement is executed as a server cursor, only a cursor identifier is returned by the EXECUTE statement. Subsequent fetch requests pass the cursor identifier back to the server along with a parameter specifying the number of rows to fetch at once. The server returns the number of rows requested.

Between fetch requests, the connection remains free to issue other commands, including other cursor OPEN or FETCH requests. In ODBC terms, this means that server cursors allow the SQL Server driver to support multiple active statements on a single connection.

Furthermore, server cursors do not usually hold locks between fetch requests, so you are free to pause between fetches for user input without affecting other users. Server cursors can be updated in place using either optimistic conflict detection or pessimistic scroll locking concurrency options.

While these features make programming with server cursors more familiar to Oracle developers than using default result sets, they are not free. Compared to default result sets:

• Server cursors are more expensive in terms of server resources, because temporary storage space is used to maintain cursor state information at the server.
  
  
• Server cursors are more expensive to retrieve a given result set of data with, because the EXECUTE statement and each fetch request in a server cursor requires a separate round-trip to the server.
  
  
• Server cursors are less flexible in terms of the kind of batches and stored procedures they support. This is because a server cursor can execute only one SELECT statement at a time, whereas default result sets can be used for batches and stored procedures that return multiple result sets or include statements other than SELECT statements.

For these reasons, it is wise to limit the use of server cursors to those parts of your application that need their features. An example that illustrates the use of server cursors can be found in the LIST_STUDENTS function in the Ssdemo.cpp sample SQL Server ODBC program file.

Scrollable Cursors

The Oracle RDBMS supports only forward-scrolling cursors. Each row is fetched to the application in the order that it was specified in the query. Oracle does not accept requests to move backward to a previously fetched row. The only way to move backward is to close the cursor and reopen it. Unfortunately, you are repositioned back to the first row in the active query set.

Because SQL Server supports scrollable cursors, you can position a SQL Server cursor at any row location. You can scroll both forward and backward. For many applications involving a user interface, scrollability is a useful feature. With scrollable cursors, your application can fetch a screen full of rows at a time, and only fetch additional rows as the user asks for them.

Although Oracle does not directly support scrollable cursors, this limitation can be minimized by using one of several ODBC options. For example, some Oracle ODBC drivers, such as the one that ships with the Microsoft Developer Studio visual development system, offer client-based scrollable cursors in the driver itself.

Alternatively, the ODBC Cursor Library supports block scrollable cursors for any ODBC driver that complies with the Level One conformance level. Both of these client cursor options support scrolling by using the RDBMS for forward-only fetching, and by caching result set data in memory or on disk. When data is requested, the driver retrieves it from the RDBMS or its local cache as needed.

Client-based cursors also support positioned UPDATE and DELETE statements for the result sets generated by SELECT statements. The cursor library constructs an UPDATE or DELETE statement with a WHERE clause that specifies the cached value for each column in a row.

If you need scrollable cursors and are trying to maintain the same source code for both Oracle and SQL Server implementations, the ODBC Cursor Library is a useful option. For more information about the ODBC Cursor Library, see your ODBC documentation.

Strategies for Using SQL Server Default Result Sets and Server Cursors

With all of the options that SQL Server offers for fetching data, it is sometimes difficult to decide what to use and when. Here are some useful guidelines:

• Default result sets are always the fastest way to get an entire set of data from SQL Server to the client. Look for opportunities in your application where you can use this to your advantage. Batch report generation, for example, generally processes an entire result set to completion, with no user interaction and no updates in the middle of processing.
  
  
• If your program requires updatable cursors, use server cursors. Default result sets are never updatable when using positioned UPDATE or DELETE statements. Additionally, server cursors are better at updating than client-based cursors, which have to simulate a positioned UPDATE or DELETE by constructing an equivalent searched UPDATE or DELETE statement.
  
  
• If your program needs scrollable, read-only cursors, both the ODBC Cursor Library and server cursors are good choices. The ODBC Cursor Library gives you compatible behavior across SQL Server and Oracle, and server cursors give you more flexibility as to how much data to fetch across the network at one time.
  
  
• When you use default result sets or ODBC Cursor Library cursors built on top of default result sets, be sure to fetch to the end of a result set as quickly as possible to avoid holding share locks at the server.
  
  
• When you use server cursors, be sure to use SQLExtendedFetch to fetch in blocks of rows rather than a single row at a time. This is the same as array-type fetching in Oracle applications. Every fetch request on a server cursor requires a round-trip from the application to the RDBMS on the network.

  Grocery shopping provides an analogy. Assume you purchase 10 bags of groceries at the grocery store, load one bag into your car, drive home, drop it off, and return to the grocery store for the next bag. This is an unlikely scenario, but this is what you do to SQL Server and your program by making single-row fetches from a server cursor.

• If your program requires only forward-only, read-only cursors but depends on multiple open cursors on the same connection, use default result sets when you know you can fetch the entire result set immediately into program variables. Use server cursors when you do not know if you can fetch all of the rows immediately.

This strategy is not as difficult as it sounds. Most programmers know when they are issuing a singleton select that can return a maximum of one row. For singleton fetches, using a default result set is more efficient than using a server cursor.

For an example of this technique, see the LIST_STUDENTS function in the Ssdemo.cpp sample SQL Server ODBC program file. Note how a server cursor is requested only if the SELECT statement may return more than one row. Following the execute step, the rowset size is set to a reasonable batch size. This allows the same SQLExtendedFetch loop to work efficiently in either the default result set or the server cursor case.

For more information about cursor implementations, see SQL Server Books Online.

Multiple Active Statements (hstmt) per Connection

The ODBC driver uses a statement handle (hstmt) to track each active SQL statement within the program. The statement handle is always associated with a RDBMS connection handle (hdbc). The ODBC driver manager uses the connection handle to send the requested SQL statement to the specified RDBMS. Most ODBC drivers for Oracle allow multiple statement handles per connection. However, the SQL Server ODBC driver allows only one active statement handle per connection when using default result sets. The SQLGetInfo function of this SQL Server driver returns the value 1 when queried with the SQL_ACTIVE_STATEMENTS option. When statement options are set in a way that uses server cursors, multiple active statements per connection handle are supported.

For more information about setting statement options to request server cursors, see SQL Server Books Online.

Data Type Mappings

The SQL Server ODBC driver offers a richer set of data type mappings than any available Oracle ODBC driver.

The timestamp data type is converted to the SQL_BINARY data type. This is because the values in timestamp columns are not datetime data, but rather binary(8) data. They are used to indicate the sequence of SQL Server activity on the row.

The Oracle data type mappings for the Microsoft ODBC driver for Oracle are shown in this table.

Oracle ODBC drivers from other vendors can have alternative data type mappings.

ODBC Extended SQL

The ODBC Extended SQL standard provides SQL extensions to ODBC that support the advanced nonstandard SQL feature set offered in both Oracle and SQL Server. This standard allows the ODBC driver to convert generic SQL statements to Oracle- and SQL Server–native SQL syntax.

This standard addresses outer joins, such as predicate escape characters, scalar functions, date/time/timestamp values, and stored programs. This syntax is used to identify these extensions:

--(*vendor(Microsoft), product(ODBC) extension *)--
OR
{extension}

The conversion takes place at run time and does not require the revision of any program code. In most application development scenarios, the best approach is to write one program and allow ODBC to perform the RDBMS conversion process when the program is run.

Outer Joins

Oracle and SQL Server do not have compatible outer join syntax. This can be solved by using the ODBC extended SQL outer join syntax. The Microsoft SQL Server syntax is the same as the ODBC Extended SQL/SQL-92 syntax. The only difference is the {oj    } container.

Date, Time, and Timestamp Values

ODBC provides three escape clauses for date, time, and timestamp values.

The format of dates has more of an impact on Oracle applications than on SQL Server applications. Oracle expects the date format to be "DD-MON-YY". In any other case, the TO_CHAR or TO_DATE functions are used with a date format model to perform a format conversion.

Microsoft SQL Server automatically converts most common date formats, and also provides the CONVERT function when an automatic conversion cannot be performed.

As shown in the table, ODBC Extended SQL works with both databases. SQL Server does not require a conversion function. Nevertheless, the ODBC shorthand syntax can be generically applied to both Oracle and SQL Server.

Calling Stored Procedures

The ODBC shorthand syntax for calling stored programs supports Microsoft SQL Server stored procedures, and Oracle stored procedures, functions, and packages. The optional "?=" captures the return value for an Oracle function or a SQL Server procedure. The parameter syntax is used to pass and return values to and from the called program. In most situations, the same syntax can be generically applied to Oracle and SQL Server applications.

In the following example, the SHOW_RELUCTANT_STUDENTS function is part of the Oracle package P1. This function must exist in a package because it returns multiple rows from a PL/SQL cursor. When you call a function or procedure that exists in a package, the package name must be placed in front of the program name.

The SHOW_RELUCTANT_STUDENTS function in the package P1 uses a package cursor to retrieve multiple rows of data. Each row must be requested with a call to this function. If there are no more rows to retrieve, the function returns the value of 0, indicating that there are no more rows to retrieve. The resulting performance of this sample Oracle package and its function might be less than satisfactory. SQL Server procedures are more efficient with this type of operation.

Native SQL Translation

Because of the variety of ODBC drivers for both Oracle and SQL Server, you may not always get the same conversion string for the extended SQL functions. To assist with application debugging issues, you might want to consider using the SQLNativeSql function. This function returns the SQL string as translated by the driver.

The following are possible results for the following input SQL string that contains the scalar function CONVERT. The column SSN is defined as the type CHAR(9), and is converted to a numeric value.

The Common.cpp Sample Program

The Common.cpp sample program does not take advantage of the ODBC Extended SQL syntax. Rather, it employs a series of views and procedures to hide statements and functions that are not common to both Oracle and SQL Server. This program, although written using ODBC, is intended to show how an application programmer can easily overcome any apparent hurdles when trying to write one common program.

These techniques and strategies are best employed in a non-ODBC development environment. If you are using ODBC, consider using the ODBC Extended SQL syntax to overcome any syntactical differences between Oracle and SQL Server.

Manual Commit Mode

Oracle automatically enters the transaction mode whenever a user modifies data. This must be followed by an explicit COMMIT to write the changes to the database. If a user wants to undo the changes, the user can issue the ROLLBACK statement.

By default, SQL Server automatically commits each change as it occurs. This is called autocommit mode in ODBC. If you do not want this to occur, you can use the BEGIN TRANSACTION statement to signal the start of a block of statements comprising a transaction. After this statement is issued, it is followed by an explicit COMMIT TRANSACTION or ROLLBACK TRANSACTION statement.

To ensure compatibility with your Oracle application, it is recommended that you use the SQLConnectOption function to place your SQL Server application in implicit transaction mode. The SQL_AUTOCOMMIT option must be set to SQL_AUTOCOMMIT_OFF in order to accomplish this. This code excerpt from the sample programs demonstrates this concept:

SQLSetConnectOption(hdbc1, SQL_AUTOCOMMIT,-sql_AUTOCOMMIT_OFF);

The SQL_AUTOCOMMIT_OFF option instructs the driver to use implicit transactions. The default option SQL_AUTOCOMMIT_ON instructs the driver to use autocommit mode, in which each statement is committed immediately after it is executed. Changing from manual commit mode to autocommit mode commits any open transactions on the connection.

If the SQL_AUTOCOMMIT_OFF option is set, the application must commit or roll back transactions explicitly with the SQLTransact function. This function requests a commit or rollback operation for all active operations on all statement handles associated with a connection handle. It can also request that a commit or rollback operation be performed for all connections associated with the environment handle.

SQLTransact(henv1, hdbc1, SQL_ROLLBACK);
(SQLTransact(henv1, hdbc1, SQL_COMMIT);

When autocommit mode is off, the driver issues SET IMPLICIT_TRANSACTIONS ON statement to the server. Starting with SQL Server 6.5, DDL statements are supported in this mode.

To commit or roll back a transaction in manual commit mode, the application must call SQLTransact. The SQL Server driver sends a COMMIT TRANSACTION statement to commit a transaction, and a ROLLBACK TRANSACTION statement to roll back a transaction.

Be aware that manual commit mode can adversely affect the performance of your SQL Server application. Every commit request requires a separate round-trip to the server to send the COMMIT TRANSACTION string.

If you have single atomic transactions (a single INSERT, UPDATE, or DELETE immediately followed by a COMMIT), use the autocommit mode.

In the sample programs, the manual commit mode has been turned on, even for atomic transactions, to demonstrate how easily a SQL Server application can be developed that closely mimics the operation of a similar application designed for the Oracle RDBMS.

Developing and Administering Database Replication

This section explains the differences between Oracle and Microsoft SQL Server replication support.

As its name implies, SQL Server snapshot replication takes a picture, or snapshot, of the published data in the database at a moment in time. Snapshot replication requires less constant processor overhead than transactional replication because it does not require continuous monitoring of data changes on source servers. Instead of copying INSERT, UPDATE, and DELETE statements (characteristic of transactional replication), or data modifications (characteristic of merge replication), Subscribers are updated by a total refresh of the data set. Hence, snapshot replication sends all the data to the Subscriber instead of sending only the changes.

SQL Server also offers transactional replication, a type of replication that marks selected transactions in the Publisher's database transaction log for replication and then distributes them asynchronously to Subscribers as incremental changes, while maintaining transactional consistency.

SQL Server merge replication allows sites to make autonomous changes to replicated data, and at a later time, merge changes made at all sites. Like Oracle, merge replication supports both column-level and row-level conflict detection. That is, you can define a "conflict" to be any change to the same row at two locations, or only when changes to the same column(s) at two locations.

SQL Server offers heterogeneous replication, which is the simplest way to publish data to a heterogeneous Subscriber by using ODBC or OLE/DB and creating a push subscription from the Publisher to the ODBC Subscriber. As an alternative, however, you can create a publication and then create an application with an embedded distribution control. The embedded control implements the pull subscription from the Subscriber to the Publisher. For ODBC Subscribers, the subscribing database has no administrative capabilities regarding the replication being performed.

The chart below compares conflict resolution mechanisms for Oracle and SQL Server:

ODBC, OLE/DB, and Replication

With Microsoft SQL Server, a distribution server connects to all subscription servers as an ODBC or OLE/DB client. Replication requires that the ODBC 32-bit driver be installed on all distribution servers. The SQL Server Setup program automatically installs the necessary driver on Windows NT–based computers.

You do not have to preconfigure ODBC Data Sources for SQL Server subscription servers because the distribution process simply uses the subscriber's network name to establish the connection.

SQL Server also includes an ODBC driver that supports Oracle subscriptions to SQL Server. The driver exists only for Intel-based computers. To replicate to Oracle ODBC subscribers, you must also obtain the appropriate Oracle SQL*Net driver from Oracle or from your software vendor.

If a password is provided in the Windows NT registry, the Oracle ODBC driver connects to Oracle without requesting a password. If a password is not provided in the Windows NT registry, you must enter a username and a password for the Oracle ODBC data source when specifying the DSN in the New ODBC Subscriber dialog box of SQL Server Enterprise Manager.

The following restrictions apply when replicating to an Oracle ODBC subscriber:

• The datetime data type is mapped to DATE. The range for the Oracle DATE data type is between 4712 B.C. and 4712 A.D. If you are replicating to Oracle, verify that SQL Server datetime entries in a replicated column are within this range.
  
  
• A replicated table can have only one text or image column.
  
  
• The datetime data type is mapped to the Oracle CHAR data type .
  
  
• The SQL Server ranges for float and real data types are different from the Oracle ranges.

Drivers for other ODBC subscriber types must conform to the SQL Server replication requirements for generic ODBC subscribers. The ODBC driver:

• Must be ODBC Level 1 compliant.
  
  
• Must be 32-bit and thread-safe for the processor architecture that the distribution process runs on.
  
  
• Must be transaction capable.
  
  
• Must support the data definition language (DDL).
  
  
• Cannot be read-only.

Migrating Your Data and Applications

This section presents various methods for migrating data from an Oracle database to a Microsoft SQL Server database.

Data Migration Using Data Transformation Services

The simplest method of migrating between Oracle and SQL Server is to use the Data Transformation Services (DTS) feature in Microsoft SQL Server 7.0. The DTS Wizard guides you through moving the data to SQL Server.

Oracle Call Interface (OCI)

If you have applications that were written by using the Oracle Call Interface (OCI), you may want to consider rewriting them by using ODBC. The OCI is specific to the Oracle RDBMS and cannot be used with Microsoft SQL Server or any other database.

In most cases, you can replace OCI functions with the appropriate ODBC functions, followed by relevant changes to the supporting program code. The remaining non-OCI program code should require minimal modification. The example shows a comparison of the OCI and ODBC statements required for establishing a connection to an Oracle database.

The table suggests conversions between Oracle OCI function calls and ODBC functions. These suggested conversions are approximate. There may not be an exact match in the conversion process. Your program code might require additional revision to obtain similar functionality.

Embedded SQL

Many applications are written using the Oracle Programmatic Interfaces (Pro*C, Pro*Cobol, and so on). These interfaces support the use of SQL-92 standard embedded SQL. They also include nonstandard Oracle programmatic extensions.

Oracle embedded SQL applications can be migrated to SQL Server by using the Microsoft Embedded SQL (ESQL) for C development environment. This environment provides adequate but less than optimal control over the performance and the use of SQL Server features compared to an ODBC application.

Some of the Oracle Pro*C features are not supported in Microsoft's ESQL precompiler. If your Oracle application makes extensive use of these features, a rewrite to ODBC is probably a better migration choice. These features include:

• Host array variables.
  
  
• VAR and TYPE statements for data type equivalencing.
  
  
• Support for embedded SQL in C++ modules.
  
  
• Support for embedded PL/SQL or Transact-SQL blocks.
  
  
• Cursor variables.
  
  
• Multithreaded application support.
  
  
• Support for the Oracle Communication Area (ORACA).

If your Oracle application has been developed in Cobol, it can be moved to Embedded SQL for Cobol from Micro Focus. You may run into some of the same limitations in Cobol as with the Microsoft ESQL for C precompiler.

You can convert your Oracle embedded SQL application to the ODBC environment. This migration process is quite easy and offers many advantages. ODBC does not require the use of a precompiler, as does embedded SQL. Consequently, much of the overhead associated with program development is eliminated.

The table shows the approximate relationship between Embedded SQL statements and ODBC functions.

The most significant change when converting embedded SQL programs to ODBC involves the handling of SQL statement errors. The MODE = ORACLE option is often used when developing embedded SQL programs. When this option is used, the SQL Communications Area (SQLCA) is typically used for error handling operations.

The SQLCA structure provides:

• Oracle error codes.
  
  
• Oracle error messages.
  
  
• Warning flags.
  
  
• Information regarding program events.
  
  
• The number of rows processed by the most recent SQL statement.

In most cases, you should check the value in the sqlca.sqlcode variable following the execution of each SQL statement. If the value is less than zero, an error has occurred. If the value is greater than zero, the requested statement executed with warnings. The Oracle error message text can be retrieved from the sqlca.sqlerrm.sqlerrmc variable.

In ODBC, a function returns a numeric status code that indicates its success or failure following the requested operation. The status codes are defined as string literals, and include SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, SQL_ERROR, and others. It is your responsibility to check these return values following each function call.

An associated SQLSTATE value can be obtained by calling the SQLError function. This function returns the SQLSTATE error code, the native error code (specific to the data source), and the error message text.

An application typically calls this function when a previous call to an ODBC function returns SQL_ERROR or SQL_SUCCESS_WITH_INFO. However, any ODBC function can post zero or more errors each time it is called, so an application may call SQLError after every ODBC function call.

Here are examples of error handling for each environment.

Developer 2000 and Third-Party Applications

If you have developed an application using Oracle Developer 2000 and want to use it with SQL Server, consider converting it to Microsoft Visual Basic. Visual Basic is a powerful development system that works well with both databases. You might also consider other development tools in Microsoft Visual Studio, or PowerBuilder, SQL Windows, and others.

If you are unable to immediately migrate from Developer 2000, consider the Oracle Gateway to SQL Server. It can be used as an intermediate step when migrating from Oracle to SQL Server. This gateway allows the Oracle RDBMS to connect to SQL Server. All requests for SQL Server data are automatically translated by the gateway. From the perspective of the Developer 2000 application, this connection is transparent. SQL Server data appears as Oracle data. Very few changes need to be made to the application program code.

Another intermediate step is to use the Developer 2000 application directly with SQL Server. Developer 2000 can directly access SQL Server using the Oracle Open Client Adapter (OCA). The OCA is ODBC Level 1 compliant and has limited support for ODBC Level 2 functions.

The OCA establishes a connection with the SQL Server ODBC driver. When connecting the Developer 2000 tools to SQL Server, you must specify an ODBC data source name as part of the database connection string. When you exit the Developer 2000 application, the OCA connection to the ODBC data source is disconnected.

The syntax for the logon connect string is demonstrated in the following example. In this example, the user logs on to the SQL Server STUDENT_ADMIN account. The name of the SQL Server ODBC data source is STUDENT_DATA:

STUDENT_ADMIN/STUDENT_ADMIN@ODBC:STUDENT_DATA

Using an ODBC driver does not ensure that a Developer 2000 application will work correctly with SQL Server. The application program code must be modified to work with a non-Oracle data source. For example, the column security property is Oracle-specific and does not work with SQL Server.

You must change the key mode that is used to identify each row of data. When using Oracle as the data source, a ROWID is used to identify each row. When using SQL Server, you must work with unique primary key values to ensure unique row values.

The locking mode also must be changed. When using Oracle, Developer 2000 attempts to lock a row of data immediately following any change to that row. When using SQL Server, the locking mode should be set to delayed so that the record is locked only when it is written to the database.

They are many other issues that must be resolved, including the potential for a deadlock situation if multiple inserts on a table access the same page of data in a PL/SQL program block. For more information, see "Transactions, Locking, and Concurrency" earlier in this article.

Internet Applications

Microsoft SQL Server includes the SQL Server Web Assistant, a wizard that generates standard HTML files from SQL Server data. The wizard can configure your Web page so that it is static, updated periodically, or updated when the data is updated. A wizard walks you through the process of creating the Web page.

Database Examples

A sample university RDBMS application has been created to support the sample program applications and code referenced throughout this chapter. This application was created specifically to illustrate many of the points, issues, and tricks involved in converting an Oracle 7 application to a SQL Server 7.0 application.

Sample University Application

This sample application uses four tables to track all university activity. The DEPT table is used to track department offerings within the university. The CLASS table is used to track class offerings within each department. The STUDENT table is used to track each student within the university. The GRADE table is used to track the enrollments of each student within each class.

In this sample application, social security number (ssn) is used as the primary key for the STUDENT table. The DEPT table uses department code (dept) as the primary key, and course code (ccode) is used as the primary key for the CLASS table. The combination of social security number (ssn) and course code (ccode) makes up the primary key for the GRADE table.

The column major is defined as a foreign key in the STUDENT table. When selecting a major, the student must choose a valid department code (dept) from the DEPT table. The department (dept) column in the CLASS table is also defined as a foreign key. When a course is inserted into this table, it must be associated with a valid department (dept) from the DEPT table.

The GRADE table has two foreign keys. When enrolling a student in a class, the social security number (ssn) must exist in the STUDENT table, and the course code (ccode) must exist in the CLASS table. This ensures that students are not enrolled in nonexistent classes, and that classes are not filled with nonexistent students.

Sample Application and Code References

These sample applications are referenced throughout this chapter.

• Orademo.cpp

  Oracle ODBC application that accesses the sample university tables on an Oracle 7.3 database. This program is the starting point for the conversion process. It allows users to perform data entry and produce reports against the sample university application.

• Ssdemo.cpp

  SQL Server application written using ODBC. This program is the ending point for the conversion process. All of the Oracle SQL commands, procedures, packages, and functions have been converted to SQL Server Transact-SQL statements and procedures. Many of the advantages associated with SQL Server 7.0 are demonstrated in this program.

• Common.cpp

  ODBC application that works with both Oracle and SQL Server. To connect to Oracle or SQL Server, the user simply provides the ODBC Data Source Name (DSN). The program then logs on to the requested RDBMS. The program contains excellent examples of programming techniques that can be used when developing a multi-RDBMS program.

• Orauser.sql

  Creates the database user accounts and database roles required for the sample Oracle program.

• Oratable.sql

  Creates the tables and views required for the sample Oracle program.

• Oraproc.sql

  Creates the Oracle stored procedure, functions, and packages required for the sample Oracle program.

• Oracommn.sql

  Creates all of the additional Oracle database objects that are required to support the Common.cpp program.

• Oradata.sql

  Loads sample application data into the tables required for the sample Oracle program.

• Ssuser.sql

  Creates the SQL Server user accounts and database roles required for the sample SQL Server program.

• Sstable.sql

  Creates the SQL Server tables and views required for the sample SQL Server program.

• Ssproc.sql

  Creates the stored procedures required for the sample SQL Server program.

• Sscommon.sql

  Creates all of the additional SQL Server database objects that are required to support the Common.cpp application.

• Ssdata.sql

  Loads sample application data into the tables required for the sample SQL Server program.

Running the Supplied Scripts

The sample scripts must be run in the following sequence to create the sample applications on the target RDBMS platforms.

Prior to running these scripts for the SQL Server database, you must create an application database (named USER_DB) for these scripts and the sample SQL Server program to work. This database can be created using SQL Server Enterprise Manager or the Transact-SQL CREATE DATABASE statement. After the database has been created, log in to SQL Server 7.0 as a system administrator (the sa SQL Server user, or a member of the sysadmin fixed server role), use the SQL Server Query Analyzer, and run these scripts in the indicated order.

Prior to running these scripts for the Oracle 7.3 database, the sample scripts assume that the USER_DATA and TEMPORARY_DATA tablespaces exist. They are usually created during a default Oracle 7.3 database installation. If these tablespaces do not exist, you must either add them or modify the supplied sample scripts to use other tablespaces.

After verifying that these tablespaces exist, log on to SQL*Plus using the SYSTEM account. If the password is not the default value of MANAGER, change the password in the Oracle SQL scripts.

RDBMS User Accounts

Three user accounts are created for this application:

• STUDENT_ADMIN

  This account is the administrative owner of the STUDENT and GRADE tables.

• DEPT_ADMIN

  This account is the administrative owner of the DEPT and CLASS tables.

• ENDUSER1

  This account is a query-only account that can access the STUDENT, GRADE, DEPT, and CLASS tables.