Migrating Oracle Databases to SQL Server 2000

Updated : July 19, 2001

This chapter is for developers of Oracle applications who want to convert their applications to Microsoft® SQL Server™ 2000. 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–based applications.

Note: This chapter was taken in its entirety from the SQL Server 2000 Resource Kit, which will be available soon.

Target Audience

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

• A strong background in Oracle RDBMS fundamentals.

• General database management knowledge.

• Familiarity with the Oracle SQL and PL/SQL languages.

• Membership in the sysadmin fixed server role.

For clarity and ease of presentation, the reference development and application platform is assumed to be the Microsoft Windows® 2000 operating system and SQL Server 2000. The Visigenic Software ODBC driver is used with Oracle, and the SQL Server ODBC driver is used with SQL Server 2000. SQL Server 2000 includes an OLE DB driver for Oracle, but that driver is not discussed extensively in this chapter.

On This Page

Overview
Architecture and Terminology
Defining Database Objects
Enforcing Data Integrity and Business Rules
Transactions, Locking, and Concurrency
SQL Language Support
Implementing Cursors
Tuning Transact-SQL Statements
Using XML
Using ODBC
Developing and Administering Database Replication
Migrating Your Data and Applications

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. The CASE expression in SQL Server is a SQL-92 extension beyond entry level and is not implemented in all database products.

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

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

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

ODBC

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

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

ODBC also defines a standardized call-level interface and uses standard escape sequences to specify SQL functions that perform common tasks but have different syntax in different databases. The ODBC drivers can automatically convert this ODBC syntax to either Oracle-native or 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 by itself does not provide 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 implement only core API functions mapped on top of other interface libraries. Other drivers, such as the SQL Server ODBC 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 SQL Server 2000. The ODBC interface cannot 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

SQL Server 2000 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 2000. Microsoft includes an OLE DB provider for Oracle 8 with SQL Server 2000.

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

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

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

Organization of This Chapter

To assist you in implementing a systematic migration from Oracle to SQL Server, each section includes an overview of the relevant differences between Oracle databases and Microsoft SQL Server 2000. The chapter also includes conversion considerations, SQL Server 2000 advantages, and multiple examples.

Where appropriate, the chapter provides references to external sources that describe the topic in more detail.

Architecture and Terminology

To start a successful migration, you should understand the basic architecture and terminology associated with SQL Server 2000.

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 SQL Server database provides a logical separation of data, applications, and security mechanisms. A SQL Server installation (an instance) can support multiple databases. Applications built using SQL Server can use databases to logically divide business functionality. There can be multiple instances of SQL Server on a single computer. Each instance of SQL Server can have multiple databases.

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

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, SQL Server 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 SQL Server 2000 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.

Similar to the SYSTEM tablespace in Oracle, the SQL Server master database must be available to access any other database. 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.

For information about a list of the system tables contained in the master and all other databases, see "System Tables" in SQL Server Books Online.

Physical and Logical Storage Structures

The Oracle RDBMS is comprised of tablespaces, which in turn are comprised of datafiles. Tablespace datafiles are formatted into internal units termed blocks. The block size is set by the DBA when the Oracle database is first created. When an object is created in an Oracle tablespace, the user can specify its space in units called extents (initial extent, next extent, min extents, and max extents). If an extent size is not defined explicitly, a default extent is created. An Oracle extent varies in size and must contain a chain of at least five contiguous blocks.

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 (64 KB). When a table or index is created in a SQL Server database, it is automatically allocated one page within an extent. As the table or index grows, it is automatically allocated space by SQL Server. This allows for more efficient storage of smaller tables and indexes when compared to allocating an entire extent as in Oracle. For more information, see "Physical Database Architecture" in SQL Server Books Online.

Striping Data

Oracle-type segments are not needed for most SQL Server installations. Instead, SQL Server can distribute, or stripe, data more efficiently with hardware-based RAID or with software–based RAID available through the Windows Disk Management utility or from third parties. With RAID, you can set up striped volumes (stripe sets in Windows NT 4.0) consisting of multiple disk drives that appear as one logical drive. If database files are created on this striped volume, 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. When migrating, the size of this drive should be at least as large as the sum of the size of the Oracle online redo logs and the Oracle 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 Microsoft Windows 2000 documentation, and the Microsoft Windows 2000 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.

SQL Server 2000 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 "Transactions, Locking, and Concurrency" 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 Create Database 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 Create Database 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 2000 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 Create Database 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 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 (set the recovery model to Simple), Transact-SQL (ALTER DATABASE), 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.

With 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 2000 CD-ROM includes all client Net-Libraries so that there is no need to acquire them separately.

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 2000, Windows NT 4.0, Windows 95, Windows 98, or Windows Millennium (WinMe) 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 migrate your Oracle applications to SQL Server 2000 adequately, you must understand how SQL Server implements database security and roles.

Database File Encryption

Microsoft Windows 2000 allows users to encrypt files using the Encrypting File System (EFS). SQL Server 2000 can use EFS. The database files can be encrypted, preventing other users from moving, copying, or viewing their contents. This encryption is done on the operating-system level, not the logical database level. After SQL Server opens the encrypted file, the data within the file appears as unencrypted.

Network Security

SQL Server 2000 supports the use of the Secure Sockets Layer (SSL) to encrypt network communications between itself and clients. This encryption applies to all inter-computer protocols supported by SQL Server 2000, and is either 40- or 128-bit depending on the version of the Windows operating system on which SQL Server is running. For more information, see "Net-Library Encryption" in SQL Server Books Online.

Login Accounts

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

A login account allows a user to administer or access data in an instance of SQL Server 2000. SQL Server 2000 uses two different methods to authenticate logins:

Windows Authentication

A DBA specifies which Windows login accounts can be used to connect to an instance of SQL Server 2000. Users logged in to Windows using these accounts can connect to SQL Server 2000 without having to specify a separate database login and password. When using Windows Authentication, SQL Server 2000 uses the security mechanisms of Windows NT 4.0 or Windows 2000 to validate login connections, and relies on a user's Windows security credentials. Users do not need to enter login IDs or passwords for SQL Server 2000 because their login information is taken directly from the trusted network connection. This functions like the IDENTIFIED EXTERNALLY option associated with Oracle user accounts.

SQL Server Authentication

A DBA defines a separate database login account. A user must specify this login account and its password when they attempt to connect to SQL Server 2000. The database login is not related to the user's Windows login. This functions like the IDENTIFIED BY PASSWORD option associated with Oracle user accounts.

Each instance of SQL Server 2000 is run in one of two authentication modes:

• Windows Authentication Mode (known as integrated security in earlier versions of SQL Server). In this mode, SQL Server 2000 allows only connections that use Windows Authentication.

• Mixed Mode. In this mode, connections can be made using either Windows Authentication or SQL Server Authentication.

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

Groups, Roles, and Permissions

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 easier 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 groups can also be added as SQL Server logins, as well as database users. Permissions can be granted to a Windows group or a Windows user.

A database can have any number of roles or Windows 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 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 group login can be mapped to a database role. Individual Windows 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 user name (optional), and up to one role name (optional). The database user name does not have to be the same as the user's login ID. If a database user name is not provided, the user's login ID and database user name 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 user account or group account can be granted access to a database, just as a SQL Server login can. When a Windows user who is a member in a group connects to the database, the user receives the permissions assigned to the Windows group. If a member of more than one Windows group that has been granted access to the database connects to the database, the user receives the combined rights of all of the groups to which he or she belongs.

sysadmin Role

Members of the SQL Server sysadmin fixed server role have similar permissions to that of an Oracle DBA. In SQL Server, the sa SQL Server Authentication 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 computer running Windows 2000. A member of the sysadmin role can add or remove Windows 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.

A member of the sysadmin fixed server role can access any database and all of the objects (including data) on a particular instance of SQL Server. Similar to an Oracle DBA, there are several commands and system procedures that only members of the sysadmin role can issue.

db_owner Role

Although a 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.

Defining Database Objects

Oracle database objects (tables, views, and indexes) can be migrated to SQL Server easily because each RDBMS closely follows the SQL-92 standard that regards object definitions.

For more information, see "Logical Database Components" in SQL Server Books Online.

Converting Oracle SQL table, index, and view definitions to SQL Server table, index, and view definitions requires relatively simple syntax changes. This table shows some differences in database objects between Oracle and SQL Server.

Category	Microsoft SQL Server	Oracle
Number of columns	1,024	1,000
Row size	8,060 bytes, including 16 bytes to point to each text or image column	Unlimited (only one long or long raw allowed per row)
Maximum number of rows	Unlimited	Unlimited
Blob type storage	16-byte pointer stored with row. Data stored on other data pages	One long or long raw per table, must be at end of row, data stored on same block(s) with row
Clustered table indexes	1 per table	1 per table (index-organized tables)
Nonclustered table indexes	249 per table	Unlimited
Maximum number of columns in single index	16	32
Maximum length of column values within of an index	900 bytes	40% of block
Table naming convention	[[[Server.]database.]owner.] table_name	[schema.]table_name
View naming convention	[[[Server.]database.]owner.] table_name	[schema.]table_name
Index naming convention	[[[Server.]database.]owner.] table_name	[schema.]table_name

It is assumed that you are starting with an Oracle SQL script or program that is used to create your database objects. Copy this script or program and make the following modifications. Each change is discussed throughout the rest of this section.

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. 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 (DTS) 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 or uniqueidentifier 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 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 table compares how Oracle and SQL Server handle object identifiers. In most cases, you do not need to change the names of objects when migrating to SQL Server.

Oracle	Microsoft SQL Server
1-30 characters in length. Database names: up to 8 characters long Database link names: up to 128 characters long	1-128 Unicode characters in length Temporary table names: up to 116 characters
Identifier names must begin with an alphabetic character and contain alphanumeric characters, or the characters _, $, and #.	Identifier names can begin with an alphanumeric character, or an _, and can contain virtually any character. If the identifier begins with a space, or contains characters other than _, @, #, or $, you must use [ ] (delimiters) around the identifier name. If an object begins with: @ it is a local variable. # it is a local temporary object. ## it is a global temporary object.
Tablespace names must be unique.	Database names must be unique.
Identifier names must be unique within user accounts (schemas).	Identifier names must be unique within database user accounts.
Column names must be unique within tables and views.	Column names must be unique within tables and views.
Index names must be unique within a user's schema.	Index names must be unique within database table names.

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.

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.

Accessing a table in…	Oracle	Microsoft SQL Server
Your user account	SELECT * FROM STUDENT	SELECT * FROM USER_DB.STUDENT_ ADMIN.STUDENT
Other schema	SELECT * FROM STUDENT_ADMIN.STUDENT	SELECT * FROM OTHER_DB.STUDENT_ ADMIN.STUDENT

Here are guidelines for naming SQL Server tables and views:

• Using the database name and user name 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 user name 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 may 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	Microsoft SQL Server
CREATE TABLE [schema.]table_name ( {col_name column_properties [default_expression] [constraint [constraint [...constraint]]]| [[,] constraint]} [[,] {next_col_name | next_constraint}...] ) [Oracle Specific Data Storage Parameters]	CREATE TABLE [server.][database.][owner.] table_name ( {col_name column_properties[constraint [constraint [...constraint]]]| [[,] constraint]} [[,] {next_col_name | next_constraint}...] ) [ON file group_name]

Oracle database object names are not case-sensitive. In 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.

Oracle	Microsoft SQL Server
CREATE TABLE DEPT_ADMIN.DEPT( DEPT VARCHAR(4) NOT NULL, DNAME VARCHAR(30) NOT NULL, CONSTRAINT dept_pk PRIMARY KEY (dept) , CONSTRAINT dept_dname_unique UNIQUE(dname) ) ORGANIZATION INDEX	CREATE TABLE USER_DB.DEPT_ADMIN.DEPT ( DEPT VARCHAR(4) NOT NULL, DNAME VARCHAR(30) NOT NULL, CONSTRAINT DEPT_DEPT_PK PRIMARY KEY CLUSTERED (DEPT), CONSTRAINT DEPT_DNAME_UNIQUE UNIQUE NONCLUSTERED (DNAME) )

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.

Oracle	Microsoft SQL Server
CREATE TABLE STUDENTBACKUP AS SELECT * FROM STUDENT	SELECT * INTO STUDENTBACKUP FROM STUDENT

The SELECT …INTO statement creates a new table and populates it with the results of the SELECT statement. Referential integrity definitions are not transferred to the new table. If the database recovery mode is set to FULL, then the SELECT …INTO statement is logged in the transaction log and a point-in-time recovery can take place.

For more information on database recovery models see "Selecting a Recovery Model" in SQL Server Books Online.

Views

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

Oracle	Microsoft SQL Server
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [schema.]view_name [(column_name [, column_name]...)] AS select_statement [WITH CHECK OPTION [CONSTRAINT name]] [WITH READ ONLY]	CREATE VIEW [owner.]view_name [(column_name [, column_name]...)] [WITH ENCRYPTION] AS select_statement [WITH CHECK OPTION]

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, and 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.

Oracle	Microsoft SQL Server
CREATE VIEW STUDENT_ADMIN.STUDENT_GPA (SSN, GPA) AS SELECT SSN, ROUND(AVG(DECODE(grade ,'A', 4 ,'A+', 4.3 ,'A-', 3.7 ,'B', 3 ,'B+', 3.3 ,'B-', 2.7 ,'C', 2 ,'C+', 2.3 ,'C-', 1.7 ,'D', 1 ,'D+', 1.3 ,'D-', 0.7 ,0)),2) FROM STUDENT_ADMIN.GRADE GROUP BY SSN	CREATE VIEW STUDENT_ADMIN.STUDENT_GPA (SSN, GPA) AS SELECT SSN, ROUND(AVG(CASE grade WHEN 'A' THEN 4 WHEN 'A+' THEN 4.3 WHEN 'A-' THEN 3.7 WHEN 'B' THEN 3 WHEN 'B+' THEN 3.3 WHEN 'B-' THEN 2.7 WHEN 'C' THEN 2 WHEN 'C+' THEN 2.3 WHEN 'C-' THEN 1.7 WHEN 'D' THEN 1 WHEN 'D+' THEN 1.3 WHEN 'D-' THEN 0.7 ELSE 0 END),2) FROM STUDENT_ADMIN.GRADE GROUP BY SSN

Indexed Views

Microsoft SQL Server 2000 Enterprise Edition introduces indexed views. Like Oracle's materialized views, indexed views are views that physically store their indexed result set on disk. Indexed views are automatically updated when their base data is updated. Indexed views are capable of providing a large boost to performance in decision-support systems in which large amounts of data must be aggregated, or in online transaction processing (OLTP) systems in which many joins are used to aggregate slowly changing data.

SQL Server indexed views cannot reference objects in remote databases. SQL Server has multiple internal replication schemes including both Snapshot and Transactional replication that provide more functionality in terms of moving data among servers.

In SQL Server, for a view to be eligible for indexing, it must be defined with the SCHEMABINDING option that locks the underlying table schemas that the view references. The view also cannot reference only nondeterministic functions. The first index created against the view must be a clustered unique index. This is required so SQL Server can quickly locate rows when the data in the base schema is modified.

For more information, see "Designing an Indexed View" in SQL Server Books Online.

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, such as:

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.

Oracle	Microsoft SQL Server
CREATE TABLE STUDENT_ADMIN.GRADE ( SSN CHAR(9) NOT NULL, CCODE VARCHAR2(4) NOT NULL, GRADE VARCHAR2(2) NULL, CONSTRAINT GRADE_SSN_CCODE_PK PRIMARY KEY (SSN, CCODE) CONSTRAINT GRADE_SSN_FK FOREIGN KEY (SSN) REFERENCES STUDENT_ADMIN.STUDENT (SSN), CONSTRAINT GRADE_CCODE_FK FOREIGN KEY (CCODE) REFERENCES DEPT_ADMIN.CLASS (CCODE) ) ORGANIZATION INDEX	CREATE TABLE STUDENT_ADMIN.GRADE ( SSN CHAR(9) NOT NULL, CCODE VARCHAR(4) NOT NULL, GRADE VARCHAR(2) NULL, CONSTRAINT GRADE_SSN_CCODE_PK PRIMARY KEY CLUSTERED (SSN, CCODE), CONSTRAINT GRADE_SSN_FK FOREIGN KEY (SSN) REFERENCES STUDENT_ADMIN.STUDENT (SSN), CONSTRAINT GRADE_CCODE_FK FOREIGN KEY (CCODE) REFERENCES DEPT_ADMIN.CLASS (CCODE) )

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.

Oracle	Microsoft SQL Server
CREATE INDEX STUDENT_ADMIN.STUDENT_MAJOR_IDX ON STUDENT_ADMIN.STUDENT (MAJOR) TABLESPACE USER_DATA PCTFREE 0 STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS UNLIMITED)	CREATE NONCLUSTERED INDEX STUDENT_MAJOR_IDX ON USER_DB.STUDENT_ADMIN.STUDENT (MAJOR)

Index Syntax and Naming

In Oracle, an index name is unique within a user account. In 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.

Oracle	Microsoft SQL Server
CREATE [UNIQUE] INDEX [schema].index_name ON [schema.]table_name (column_name [, column_name]...) [INITRANS n] [MAXTRANS n] [TABLESPACE tablespace_name] [STORAGE storage_parameters] [PCTFREE n] [NOSORT] DROP INDEX ABC;	CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON table (column [,…n]) [WITH [PAD_INDEX] [[,] FILLFACTOR = fillfactor] [[,] IGNORE_DUP_KEY] [[,] DROP_EXISTING] [[,] STATISTICS_NORECOMPUTE] ] [ON file group] DROP INDEX USER_DB.STUDENT.DEMO_IDX, USER_DB.GRADE.DEMO_IDX

Index Data Storage Parameters

The FILLFACTOR option in 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.

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

Indexes on Computed Columns

Oracle allows you to place an index directly on a function. Microsoft SQL Server allows for indexes to be placed on computed columns within a table. Within SQL Server, a table can consist of multiple computed columns but must have at least one noncomputed column. Computed columns can consist either of SQL Server functions or user-defined functions, but the functions must be deterministic in nature. That is, the function must return the same value each time it is called with identical parameters. For example, the SQL Server GETDATE() function is nondeterministic because it is always called with the same parameters and returns a different value each time.

For more information, see "Creating Indexes on Computed Columns" in 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.

Table name	Description
#table_name	This local temporary table only exists for the duration of a user session or the procedure that created it. It is automatically dropped when the user logs off or the procedure that created the table completes. These tables cannot be shared between multiple users. No other database users can access this table. Permissions cannot be granted or revoked on this table.
##table_name	This global temporary table also typically exists for the duration of a user session or procedure that created it. This table can be shared among multiple users. It is automatically dropped when the last user session referencing it disconnects. All other database users can access this table. Permissions cannot be granted or revoked on this table.

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

Although 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 Import/Export 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.

Oracle	Microsoft SQL Server
CHAR	char is recommended. char type columns are accessed somewhat faster than varchar columns because they use a fixed storage length.
VARCHAR2 and LONG	varchar or text. (If the length of the data values in your Oracle column is 8000 bytes or less, use varchar; otherwise, you must use text.)
RAW and LONG RAW	varbinary or image. (If the length of the data values in your Oracle column is 8000 bytes or less, use varbinary; otherwise, you must use image.)
NUMBER	If integer between 1 and 255, use tinyint. If integer between -32768 and 32767, use smallint. If integer between -2,147,483,648 and 2,147,483,647 use int. If integer between –2^63 and 2^63 use bigint. If you require a float type number, use numeric (has precision and scale). Note: Do not use float or real, because rounding may occur (Oracle NUMBER and SQL Server numeric do not round). If you are not sure, use numeric; it most closely resembles Oracle NUMBER data type.
DATE	datetime.
ROWID	Use the identity column type or the uniqueidentifier data type and the NEWID function.
CURRVAL, NEXTVAL	Use the identity column type, and @@IDENTITY global variable, IDENT_SEED() and IDENT_INCR() functions.
SYSDATE	GETDATE()
USER	USER

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.

SQL Server 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 Transact-SQL 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 Transact-SQL REVOKE statement removes a previously granted or denied permission from a user in the current database.

Oracle

Microsoft SQL Server

GRANT {ALL [PRIVILEGES][column_list] | permission_list [column_list]} ON {table_name [(column_list)] | view_name [(column_list)] | stored_procedure_name} TO {PUBLIC | name_list } [WITH GRANT OPTION]

GRANT {ALL [PRIVILEGES] | permission[,…n]} { [(column[,…n])] ON {table | view} | ON {table | view}[(column[,…n])] | ON {stored_procedure | extended_procedure} } TO security_account[,…n] [WITH GRANT OPTION] [AS {group | role}] REVOKE [GRANT OPTION FOR] {ALL [PRIVILEGES] | permission[,…n]} { [( column[,…n])] ON {table | view} | ON {table | view}[( column[,…n])] | {stored_procedure | extended_procedure} } {TO | FROM} security_account[,…n] [CASCADE] [AS {group | role}] DENY {ALL [PRIVILEGES] | permission[,…n]} { [(column[,…n])] ON {table | view} | ON {table | view}[(column[,…n])] | ON {stored_procedure | extended_procedure} } TO security_account[,…n] [CASCADE]

For more information on 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.

Integrity Type	How Enforced
Entity integrity	PRIMARY KEY constraint UNIQUE constraint IDENTITY property
Domain integrity	Domain DEFAULT definition FOREIGN KEY constraint CHECK constraint Nullability
Referential integrity	FOREIGN KEY constraint CHECK constraint
User-defined integrity	All column- and table-level constraints in CREATE TABLE Stored procedures Triggers

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.

Oracle	Microsoft SQL Server
CREATE TABLE DEPT_ADMIN.DEPT (DEPT VARCHAR2(4) NOT NULL, DNAME VARCHAR2(30) NOT NULL, CONSTRAINT DEPT_DEPT_PK PRIMARY KEY (DEPT) USING INDEX TABLESPACE USER_DATA PCTFREE 0 STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS UNLIMITED), CONSTRAINT DEPT_DNAME_UNIQUE UNIQUE (DNAME) USING INDEX TABLESPACE USER_DATA PCTFREE 0 STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS UNLIMITED) )	CREATE TABLE USER_DB.DEPT_ADMIN.DEPT (DEPT VARCHAR(4) NOT NULL, DNAME VARCHAR(30) NOT NULL, CONSTRAINT DEPT_DEPT_PK PRIMARY KEY CLUSTERED (DEPT), CONSTRAINT DEPT_DNAME_UNIQUE UNIQUE NONCLUSTERED (DNAME) )

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 Unique Values

A SQL Server table can have one column defined as an identity column, which is an auto-incrementing integer field. SQL Server automatically tracks inserts and adds the value for the identity column to the record. If your application uses Oracle SEQUENCE to generate unique integers for a column, then you can replace it with the identity field.

Category	Microsoft SQL Server IDENTITY
Syntax	CREATE TABLE new_employees ( Empid int IDENTITY (1,1), Employee_Name varchar(60), CONSTRAINT Emp_PK PRIMARY KEY (Empid) ) If increment interval is 5: CREATE TABLE new_employees ( Empid int IDENTITY (1,5), Employee_Name varchar(60), CONSTRAINT Emp_PK PRIMARY KEY (Empid) )
Identity columns per table	One
Null values allowed	No
Use of default constraints, values	Cannot be used.
Enforcing uniqueness	Yes
Querying for maximum current identity number after an INSERT, SELECT INTO, or bulk copy statement completes	@@IDENTITY (function)
Returns the seed value specified during the creation of an identity column	IDENT_SEED('table_name')
Returns the increment value specified during the creation of an identity column	IDENT_INCR('table_name')
SELECT syntax	The keyword IDENTITYCOL can be used in place of a column name when you reference a column that has the IDENTITY property, in SELECT, INSERT, UPDATE, and DELETE statements.

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.

If your application uses an Oracle SEQUENCE to generate a unique value that is then concatenated with another value to produce a unique string, you will have to create some custom code, either in a trigger or stored procedure that will generate the concatenated string for you.

For more information about creating and modifying uniqueidentifier 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 Query Analyzer in SQL Server, and parse only the syntax. The Results pane indicates any errors. For more information about constraint syntax, see SQL Server Books Online.

Oracle

Microsoft SQL Server

CREATE TABLE STUDENT_ADMIN.STUDENT ( SSN CHAR(9) NOT NULL, FNAME VARCHAR2(12) NULL, LNAME VARCHAR2(20) NOT NULL, GENDER CHAR(1) NOT NULL CONSTRAINT STUDENT_GENDER_CK CHECK (GENDER IN ('M','F')), MAJOR VARCHAR2(4) DEFAULT 'Undc' NOT NULL, BIRTH_DATE DATE NULL, TUITION_PAID NUMBER(12,2) NULL, TUITION_TOTAL NUMBER(12,2) NULL, START_DATE DATE NULL, GRAD_DATE DATE NULL, LOAN_AMOUNT NUMBER(12,2) NULL, DEGREE_PROGRAM CHAR(1) DEFAULT 'U' NOT NULL CONSTRAINT STUDENT_DEGREE_CK CHECK (DEGREE_PROGRAM IN ('U', 'M', 'P', 'D')), ...

CREATE TABLE USER_DB.STUDENT _ADMIN.STUDENT ( SSN CHAR(9) NOT NULL, FNAME VARCHAR(12) NULL, LNAME VARCHAR(20) NOT NULL, GENDER CHAR(1) NOT NULL CONSTRAINT STUDENT_GENDER_CK CHECK (GENDER IN ('M','F')), MAJOR VARCHAR(4) DEFAULT 'Undc' NOT NULL, BIRTH_DATE DATETIME NULL, TUITION_PAID NUMERIC(12,2) NULL, TUITION_TOTAL NUMERIC(12,2) NULL, START_DATE DATETIME NULL, GRAD_DATE DATETIME NULL, LOAN_AMOUNT NUMERIC(12,2) NULL, DEGREE_PROGRAM CHAR(1) DEFAULT 'U' NOT NULL CONSTRAINT STUDENT_DEGREE_CK CHECK (DEGREE_PROGRAM IN ('U', 'M', 'P', 'D')), ...

Note: 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. When not explicitly specified, column nullability follows these rules.

Null Setting	Description
Column is defined with a user-defined data type	SQL Server uses the nullability specified when the data type was created. Use the sp_help system stored procedure to get the data type's default nullability.
Column is defined with a system-supplied data type	If the system-supplied data type has only one option, it takes precedence. Currently, the bit data type can be defined only as NOT NULL. If any session settings are ON (set with the SET), then: If ANSI_NULL_DFLT_ON is ON, NULL is assigned. If ANSI_NULL_DFLT_OFF is ON, NOT NULL is assigned. If any database settings are configured (changed with the ALTER DATABASE statement), then: If ANSI NULL DEFAULT is TRUE, NULL is assigned. If ANSI NULL DEFAULT is FALSE, NOT NULL is assigned.
NULL/NOT NULL Not defined	When not explicitly defined (neither of the ANSI_NULL_DFLT options are set), the session has not been changed and the database is set to the default (ANSI NULL DEFAULT is FALSE), then SQL Server assigns it NOT NULL.

Referential Integrity

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

Constraint	Oracle	Microsoft SQL Server
PRIMARY KEY	[CONSTRAINT constraint_name] PRIMARY KEY (col_name [, col_name2 [..., col_name16]]) [USING INDEX storage_parameters]	[CONSTRAINT constraint_name] PRIMARY KEY [CLUSTERED | NONCLUSTERED] (col_name [, col_name2 [..., col_name16]]) [ON segment_name] [NOT FOR REPLICATION]
UNIQUE	[CONSTRAINT constraint_name] UNIQUE (col_name [, col_name2 [..., col_name16]]) [USING INDEX storage_parameters]	[CONSTRAINT constraint_name] UNIQUE [CLUSTERED | NONCLUSTERED](col_name [, col_name2 [..., col_name16]]) [ON segment_name] [NOT FOR REPLICATION]
FOREIGN KEY	[CONSTRAINT constraint_name] [FOREIGN KEY (col_name [, col_name2 [..., col_name16]])] REFERENCES [owner.]ref_table [(ref_col [, ref_col2 [..., ref_col16]])] [ON DELETE CASCADE]	[CONSTRAINT constraint_name] [FOREIGN KEY (col_name [, col_name2 [..., col_name16]])] REFERENCES [owner.]ref_table [(ref_col [, ref_col2 [..., ref_col16]])] [ON DELETE CASCADE | No Action] [ON UPDATE CASCADE | No Action] [NOT FOR REPLICATION]
DEFAULT	Column property, not a constraint DEFAULT (constant_expression)	[CONSTRAINT constraint_name] DEFAULT {constant_expression | niladic-function | NULL} [FOR col_name] [NOT FOR REPLICATION]
CHECK	[CONSTRAINT constraint_name] CHECK (expression)	[CONSTRAINT constraint_name] CHECK [NOT FOR REPLICATION] (expression)

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.

In SQL Server 2000, foreign keys have an ON DELETE clause that is used to define what action should be taken if a candidate key to which the foreign key is pointing is deleted. The NO ACTION option causes the delete to fail with an error. The CASCADE option cascades the delete to any rows that reference the data within the FOREIGN KEY constraint.

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.

Oracle	Microsoft SQL Server
CREATE OR REPLACE PROCEDURE [user.]procedure [(argument [IN | OUT] datatype [, argument [IN | OUT] datatype] {IS | AS} block	CREATE PROC[EDURE] procedure_name [;number] [ {@parameter data_type} [VARYING] [= default] [OUTPUT] ] [,…n] [WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} ] [FOR REPLICATION] AS sql_statement […n]

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 with 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 shows how a Transact-SQL stored procedure can be used to replace an Oracle PL/SQL packaged function. In this example, the Transact-SQL version is much simpler because of the ability of SQL Server to return result sets directly from SELECT statements in a stored procedure, without using a cursor.

Oracle

Microsoft SQL Server

CREATE OR REPLACE PACKAGE STUDENT_ADMIN.P1 AS ROWCOUNT NUMBER :=0; CURSOR C1 RETURN STUDENT%ROWTYPE; FUNCTION SHOW_RELUCTANT_STUDENTS (WORKVAR OUT VARCHAR2) RETURN NUMBER; END P1; / CREATE OR REPLACE PACKAGE BODY STUDENT_ADMIN.P1 AS CURSOR C1 RETURN STUDENT%ROWTYPE IS SELECT * FROM STUDENT_ADMIN.STUDENT WHERE NOT EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADE WHERE GRADE.SSN=STUDENT.SSN) ORDER BY SSN; FUNCTION SHOW_RELUCTANT_STUDENTS (WORKVAR OUT VARCHAR2) RETURN NUMBER IS WORKREC STUDENT%ROWTYPE; BEGIN IF NOT C1%ISOPEN THEN OPEN C1; ROWCOUNT :=0; ENDIF; FETCH C1 INTO WORKREC; IF (C1%NOTFOUND) THEN CLOSE C1; ROWCOUNT :=0; ELSE WORKVAR := WORKREC.FNAME||' '||WORKREC.LNAME|| ', social security number '||WORKREC.SSN||' is not enrolled in any classes!'; ROWCOUNT := ROWCOUNT + 1; ENDIF; RETURN(ROWCOUNT); EXCEPTION WHEN OTHERS THEN IF C1%ISOPEN THEN CLOSE C1; ROWCOUNT :=0; ENDIF; RAISE_APPLICATION_ERROR(-20001,SQLERRM); END SHOW_RELUCTANT_STUDENTS; END P1; /

CREATE PROCEDURE STUDENT_ADMIN.SHOW_RELUCTANT_STUDENTS AS SELECT FNAME+'' +LNAME+', social security number'+ SSN+' is not enrolled in any classes!' FROM STUDENT_ADMIN.STUDENT S WHERE NOT EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADE G WHERE G.SSN=S.SSN) ORDER BY SSN RETURN@@ROWCOUNT GO

SQL Server does not support constructs similar to Oracle packages, and does not support the CREATE OR REPLACE option for creating stored procedures. Instead, SQL Server supports either the CREATE or ALTER statements to create or modify 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.

Oracle	Microsoft SQL Server
Varname datatype DEFAULT <value>;	{@parameter data_type} [VARYING] [= default] [OUTPUT]

Triggers

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

Description	Oracle	Microsoft SQL Server
Number of triggers per table	Unlimited	Unlimited
Triggers executed before INSERT, UPDATE, DELETE	Yes	Yes. This functionality can be created with the INSTEAD OF option.
Triggers executed after INSERT, UPDATE, DELETE	Yes	Yes
Statement Level Triggers	Yes	Yes
Row Level Triggers	Yes	No
Constraints checked prior to execution	Yes, unless trigger is disabled.	Yes. In addition, this is an option in Data Transformation Services.
Referring to old or previous values in an UPDATE or DELETE trigger	:old	DELETED.column
Referring to new values in an INSERT trigger	:new	INSERTED.column
Disabling Triggers	ALTER TRIGGER	Option in Data Transformation Services

Triggers can be created to execute either after (AFTER trigger) the INSERT, UPDATE or DELETE statement or INSTEAD OF the statement. If you need the functionality of a BEFORE trigger from Oracle, you will have to add the INSERT, UPDATE or DELETE statement to the logic within the INSTEAD OF trigger.

The deleted and inserted tables 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.

GRADE	inserted	deleted
SSN CHAR(9) CCODE VARCHAR(4) GRADE VARCHAR(2)	SSN CHAR(9) CCODE VARCHAR(4) GRADE VARCHAR(2)	SSN CHAR(9) CCODE VARCHAR(4) GRADE VARCHAR(2)

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.

Oracle	Microsoft SQL Server
CREATE TRIGGER STUDENT_ADMIN.TRACK_GRADES AFTER INSERT OR UPDATE OR DELETE ON STUDENT_ADMIN.GRADE FOR EACH ROW BEGIN INSERT INTO GRADE_HISTORY( TABLE_USER, ACTION_DATE, OLD_SSN, OLD_CCODE, OLD_GRADE, NEW_SSN, NEW_CCODE, NEW_GRADE) VALUES (USER, SYSDATE, :OLD.SSN, :OLD.CCODE, :OLD.GRADE, :NEW.SSN, :NEW.CCODE, :NEW.GRADE), END;	CREATE TRIGGER STUDENT_ADMIN.TRACK_GRADES ON STUDENT_ADMIN.GRADE FORAFTER INSERT, UPDATE, DELETE AS INSERT INTO GRADE_HISTORY( TABLE_USER, ACTION_DATE, OLD_SSN, OLD_CCODE, OLD_GRADE NEW_SSN, NEW_CCODE, NEW_GRADE) SELECT USER, GETDATE(), OLD.SSN, OLD.CCODE, OLD.GRADE, NEW.SSN, NEW.CCODE, NEW.GRADE FROM INSERTED NEW FULL OUTER JOIN DELETED OLD ON NEW.SSN = OLD.SSN

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.

There can be multiple AFTER triggers defined for each data modification event for a table. However, there can be only one INSTEAD OF trigger defined for a table.

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. 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, 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 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 TRANSACTION {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.

Oracle	Microsoft SQL Server
INSERT INTO DEPT_ADMIN.DEPT (DEPT, DNAME) VALUES ('LIT', 'Literature') / UPDATE DEPT_ADMIN.CLASS SET MAJOR = 'LIT' WHERE MAJOR = 'ENG' / UPDATE STUDENT_ADMIN.STUDENT SET MAJOR = 'LIT' WHERE MAJOR = 'ENG' / DELETE FROM DEPT_ADMIN.DEPT WHERE DEPT = 'ENG' / COMMIT /	BEGIN TRANSACTION INSERT INTO DEPT_ADMIN.DEPT (DEPT, DNAME) VALUES ('LIT', 'Literature') UPDATE DEPT_ADMIN.CLASS SET DEPT = 'LIT' WHERE DEPT = 'ENG' UPDATE STUDENT_ADMIN.STUDENT SET MAJOR = 'LIT' WHERE MAJOR = 'ENG' DELETE FROM DEPT_ADMIN.DEPT WHERE DEPT = 'ENG' COMMIT TRANSACTION GO

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 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 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 re-creates 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.

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 UPDATE statements 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 Sever 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 Error! Bookmark not defined., but the data can be changed before the end of the transaction, resulting in Error! Bookmark not defined. or Error! Bookmark not defined. 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 SQL-92 standard transaction isolation levels; Oracle only implements READ COMMITTED (the default) and SERIALIZABLE.

SQL Server does not directly support the non–SQL-92 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 updatable 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.

Functionality	Oracle	Microsoft SQL Server
Lock an entire table—allows others to read a table, but prevent them from updating it. By default, the lock is held until the end of the statement.	LOCK TABLE…IN SHARE MODE	SELECT…table_name (TABLOCK)
Lock the table until the end of the transaction		SELECT…table_name (TABLOCK REPEATABLEREAD)
Exclusive lock -prevent others from reading or updating the table and is held until the end of the command or transaction	LOCK TABLE…IN EXCLUSIVE MODE	SELECT…table_name (TABLOCKX)
Specify the number of milliseconds a statement waits for a lock to be released.	NOWAIT works like "LOCK_TIMEOUT 0"	LOCK_TIMEOUT

Handling Deadlocks

A deadlock occurs when one process locks a resource needed by another process, and the second process locks a page the first process needs. 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, search for the Microsoft Knowledge Base article "Detecting and Avoiding Deadlocks in Microsoft SQL Server" at https://support.microsoft.com.

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 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 MS 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 terminated 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 terminated. MS DTC keeps a sequential log so that its commit or terminate 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 guidelines 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	Microsoft SQL Server
SELECT [/*+ optimizer_hints*/] [ALL | DISTINCT] select_list [FROM {table_name | view_name | select_statement}] [WHERE clause] [GROUP BY group_by_expression] [HAVING search_condition] [START WITH … CONNECT BY] [{UNION | UNION ALL | INTERSECT | MINUS} SELECT …] [ORDER BY clause] [FOR UPDATE]	SELECT select_list [INTO new_table_] FROM table_source [WHERE search_condition] [ GROUP BY [ALL] group_by_expression [,…n] [ WITH { CUBE | ROLLUP } ] [HAVING search_condition] [ORDER BY order_expression [ASC | DESC] ] In addition: UNION operator COMPUTE clause FOR BROWSE clause OPTION clause

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" 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. For more information, see "Expanding Hierarchies" in SQL Server Books Online, or search the Microsoft Knowledge Base for relevant articles at https://support.microsoft.com.

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.

Oracle	Microsoft SQL Server
SELECT CCODE, CNAME FROM DEPT_ADMIN.CLASS INTERSECT SELECT C.CCODE, C.CNAME FROM STUDENT_ADMIN.GRADE G, DEPT_ADMIN.CLASS C WHERE C.CCODE = G.CCODE	SELECT CCODE, CNAME FROM DEPT_ADMIN.CLASS C WHERE EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADE G WHERE C.CCODE = G.CCODE)

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

Oracle	Microsoft SQL Server
SELECT CCODE, CNAME FROM DEPT_ADMIN.CLASS MINUS SELECT C.CCODE, C.CNAME FROM STUDENT_ADMIN.GRADE G, DEPT_ADMIN.CLASS C WHERE C.CCODE = G.CCODE	SELECT CCODE, CNAME FROM DEPT_ADMIN.CLASSC WHERE NOT EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADE G WHERE C.CCODE = G.CCODE)

INSERT Statements

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

Oracle	Microsoft SQL Server
INSERT INTO {table_name | view_name | select_statement } [(column_list)] { values_list | select_statement}	INSERT [INTO] { table_name [ [AS] table_alias] WITH ( <table_hint_limited> […n]) | view_name [ [AS] table_alias] | rowset_function_limited } { [(column_list)] { VALUES ( { DEFAULT | NULL | expression }[,…n] ) | derived_table | execute_statement } } | DEFAULT VALUES

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.

Oracle	Microsoft SQL Server
INSERT INTO (SELECT SSN, CCODE, GRADE FROM GRADE) VALUES ('111111111', '1111',NULL)	INSERT INTO GRADE (SSN, CCODE, GRADE) VALUES ('111111111', '1111',NULL)

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

UPDATE Statements

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

Oracle

Microsoft SQL Server

UPDATE {table_name | view_name | select_statement } SET [column_name(s) = {constant_value | expression | select_statement | column_list | variable_list ] {where_statement}

UPDATE { table_name [ [AS] table_alias] WITH ( <table_hint_limited> […n]) | view_name [ [AS] table_alias] | rowset_function_limited } SET {column_name = {expression | DEFAULT | NULL} | @variable = expression | @variable = column = expression } [,…n] {{[FROM {<table_source>} [, …n] ] [WHERE <search_condition>] } | [WHERE CURRENT OF { { [GLOBAL] cursor_name } | cursor_variable_name} ] } [OPTION (<query_hint> [,…n] )]

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.

Oracle	Microsoft SQL Server
DECLARE VAR1 NUMBER(10,2); BEGIN VAR1 := 2500; UPDATE STUDENT_ADMIN.STUDENT SET TUITION_TOTAL = VAR1; END;	DECLARE @VAR1 NUMERIC(10,2) SELECT @VAR1 = 2500 UPDATE STUDENT_ADMIN.STUDENT SET TUITION_TOTAL=@VAR1

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.

Oracle	Microsoft SQL Server
UPDATE STUDENT_ADMIN.STUDENT S SET TUITION_TOTAL = 1500 WHERE SSN IN (SELECT SSN FROM GRADE G WHERE G.SSN = S.SSN AND G.CCODE = '1234')	Subquery: UPDATE STUDENT_ADMIN.STUDENT S SET TUITION_TOTAL = 1500 WHERE SSN IN (SELECT SSN FROM GRADE G WHERE G.SSN = S.SSN AND G.CCODE = '1234') FROM clause: UPDATE STUDENT_ADMIN.STUDENT S SET TUITION_TOTAL = 1500 FROM GRADE G WHERE S.SSN = G.SSN AND G.CCODE = '1234'

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 Transact-SQL does not support this functionality.

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

Oracle	Microsoft SQL Server
DELETE [FROM] {table_name | view_name | select_statement } [WHERE clause]	DELETE [FROM ] { table_name [ [AS] table_alias] WITH ( <table_hint_limited> […n]) | view_name [ [AS] table_alias] | rowset_function_limited } [ FROM {<table_source>} [, …n] ] [WHERE { <search_condition> | { [ CURRENT OF { { [ GLOBAL ] cursor_name } | cursor_variable_name } ] } ] [OPTION (<query_hint> [,…n])]

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. The table structure and all of its indexes continue to exist. Unlike the DELETE statement, which explicitly deletes each record of a table, TRUNCATE TABLE removes the data from the table by deallocating the data pages of the table and logging these deallocations in the transaction log. Because the rows are not explicitly deleted, DELETE triggers are not executed. If a table is referenced by a FOREIGN KEY constraint, it cannot be truncated.

Oracle	Microsoft SQL Server
TRUNCATE TABLE table_name [{DROP | REUSE} STORAGE]	TRUNCATE TABLE table_name

In SQL Server, only the table owner can issue this statement. 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. The COMPUTE clause 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 2000 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.

Join operation	Description
CROSS JOIN	This is the cross product of two tables. It returns the same rows as if no WHERE clause was specified in an old-style join. This type of join is called a Cartesian-join in Oracle.
INNER	This join specifies that all inner rows be returned. Any unmatched rows are discarded. This is identical to a standard Oracle table join.
LEFT [OUTER]	This type of join specifies that all of the left table outer rows be returned, even if no column matches are found. This operates just like an Oracle outer join (+).
RIGHT [OUTER]	This type of join specifies that all of the right table outer rows be returned, even if no column matches are found. This operates just like an Oracle outer join (+).
FULL [OUTER]	If a row from either table does not match the selection criteria, this specifies the row be included in the result set and its output columns that correspond to the other table be set to NULL. This would be the same as placing the Oracle outer join operator on both sides of the "=" sign (col1(+) = col2(+)), which is not allowed.

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

Oracle	Microsoft SQL Server
SELECT S.SSN AS SSN, FNAME, LNAME FROM STUDENT_ADMIN.STUDENT S, DEPT_ADMIN.CLASS C, STUDENT_ADMIN.GRADE G WHERE S.SSN = G.SSN(+) AND G.CCODE = C.CCODE(+)	SELECT S.SSN AS SSN, FNAME, LNAME FROM STUDENT_ADMIN.GRADE G RIGHT OUTER JOIN STUDENT_ADMIN.STUDENT S ON G.SSN = S.SSN LEFT OUTER JOIN DEPT_ADMIN.CLASS C ON G.CCODE = C.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.

Oracle	Microsoft SQL Server
SELECT SSN, LNAME, FNAME, TUITION_PAID, SUM_PAID FROM STUDENT_ADMIN.STUDENT, (SELECT SUM(TUITION_PAID) SUM_PAID FROM STUDENT_ADMIN.STUDENT)	SELECT SSN, LNAME, FNAME, TUITION_PAID, SUM_PAID FROM STUDENT_ADMIN.STUDENT, (SELECT SUM(TUITION_PAID) SUM_PAID FROM STUDENT_ADMIN.STUDENT) SUM_STUDENT

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, the functions vary in numbers and types of arguments. Also, functions that are supplied only by Microsoft SQL Server are not mentioned in this list because 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.

Function	Oracle	Microsoft SQL Server
Absolute value	ABS	Same
Arc cosine	ACOS	Same
Arc sine	ASIN	Same
Arc tangent of n	ATAN	Same
Arc tangent of n and m	ATAN2	ATN2
Smallest integer >= value	CEIL	CEILING
Cosine	COS	Same
Hyperbolic cosine	COSH	COT
Exponential value	EXP	Same
Largest integer <= value	FLOOR	Same
Natural logarithm	LN	LOG
Logarithm, any base	LOG(N)	N/A
Logarithm, base 10	LOG(10)	LOG10
Modulus (remainder)	MOD	USE MODULO (%) OPERATOR
Power	POWER	Same
Random number	N/A	RAND
Round	ROUND	Same
Sign of number	SIGN	Same
Sine	SIN	Same
Hyperbolic sine	SINH	N/A
Square root	SQRT	Same
Tangent	TAN	Same
Hyperbolic tangent	TANH	N/A
Truncate	TRUNC	N/A
Largest number in list	GREATEST	N/A
Smallest number in list	LEAST	N/A
Convert number if NULL	NVL	ISNULL

Character Functions

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

Function	Oracle	Microsoft SQL Server
Convert character to ASCII	ASCII	Same
String concatenate	CONCAT	(expression + expression)
Convert ASCII to character	CHR	CHAR
Return starting point of character in character string (from left)	INSTR	CHARINDEX
Convert characters to lowercase (LOWER)	LOWER	Same
Convert characters to uppercase (UPPER)	UPPER	Same
Pad left side of character string	LPAD	N/A
Remove leading blanks	LTRIM	Same
Remove trailing blanks	RTRIM	Same
Starting point of pattern in character string	INSTR	PATINDEX
Repeat character string multiple times	RPAD	REPLICATE
Phonetic representation of character string	SOUNDEX	Same
String of repeated spaces	RPAD	SPACE
Character data converted from numeric data	TO_CHAR	STR
Substring	SUBSTR	SUBSTRING
Replace characters	REPLACE	STUFF
Capitalize first letter of each word in string	INITCAP	N/A
Translate character string	TRANSLATE	N/A
Length of character string	LENGTH	DATALENGTH or LEN
Greatest character string in list	GREATEST	N/A
Least character string in list	LEAST	N/A
Convert string if NULL	NVL	ISNULL

Date Functions

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

Function	Oracle	Microsoft SQL Server
Date addition	(date column +/- value) or ADD_MONTHS	DATEADD
Difference between dates	(date column +/- value) or MONTHS_BETWEEN	DATEDIFF
Current date and time	SYSDATE	GETDATE()
Last day of month	LAST_DAY	N/A
Time zone conversion	NEW_TIME	N/A
First weekday after date	NEXT_DAY	N/A
Character string representation of date	TO_CHAR	DATENAME
Integer representation of date	TO_NUMBER(TO_CHAR))	DATEPART
Date round	ROUND	CONVERT
Date truncate	TRUNC	CONVERT
Character string to date	TO_DATE	CONVERT
Convert date if NULL	NVL	ISNULL

Conversion Functions

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

Function	Oracle	Microsoft SQL Server
Number to character	TO_CHAR	CONVERT
Character to number	TO_NUMBER	CONVERT
Date to character	TO_CHAR	CONVERT
Character to date	TO_DATE	CONVERT
Hex to binary	HEX_TO_RAW	CONVERT
Binary to hex	RAW_TO_HEX	CONVERT

Other Row-Level Functions

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

Function	Oracle	Microsoft SQL Server
Return first non-null expression	DECODE	COALESCE
Current sequence value	CURRVAL	N/A
Next sequence value	NEXTVAL	N/A
If exp1 = exp2, return null	DECODE	NULLIF
User's login ID number	UID	SUSER_ID
User's login name	USER	SUSER_NAME
User's database ID number	UID	USER_ID
User's database name	USER	USER_NAME
Current User	CURRENT_USER	Same
User environment (audit trail)	USERENV	N/A
Level in CONNECT BY clause	LEVEL	N/A

Aggregate Functions

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

Function	Oracle	Microsoft SQL Server
Average	AVG	Same
Count	COUNT	Same
Maximum	MAX	Same
Minimum	MIN	Same
Standard deviation	STDDEV	STDEV or STDEVP
Summation	SUM	Same
Variance	VARIANCE	VAR or VARP

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.

Oracle

Microsoft SQL Server

DECODE (test_value, expression1, value1 [[,expression2, value2] […]] [,default_value] ) CREATE VIEW STUDENT_ADMIN.STUDENT_GPA (SSN, GPA) AS SELECT SSN, ROUND(AVG(DECODE(grade ,'A', 4 ,'A+', 4.3 ,'A-', 3.7 ,'B', 3 ,'B+', 3.3 ,'B-', 2.7 ,'C', 2 ,'C+', 2.3 ,'C-', 1.7 ,'D', 1 ,'D+', 1.3 ,'D-', 0.7 ,0)),2) FROM STUDENT_ADMIN.GRADE GROUP BY SSN

CASE test_value WHEN expression1 THEN value1 [[WHEN expression2 THEN value2] [...]] [ELSE default_value] END CREATE VIEW STUDENT_ADMIN.STUDENT_GPA (SSN, GPA) AS SELECT SSN, ROUND(AVG(CASE grade WHEN 'A' THEN 4 WHEN 'A+' THEN 4.3 WHEN 'A-' THEN 3.7 WHEN 'B' THEN 3 WHEN 'B+' THEN 3.3 WHEN 'B-' THEN 2.7 WHEN 'C' THEN 2 WHEN 'C+' THEN 2.3 WHEN 'C-' THEN 1.7 WHEN 'D' THEN 1 WHEN 'D+' THEN 1.3 WHEN 'D-' THEN 0.7 ELSE 0 END),2) FROM STUDENT_ADMIN.GRADE GROUP BY SSN

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.

Conversion	Oracle	Microsoft SQL Server
Character to number	TO_NUMBER('10')	CONVERT(numeric, '10')
Number to character	TO_CHAR(10)	CONVERT(char, 10)
Character to date	TO_DATE('04-JUL-97') TO_DATE('04-JUL-1997', 'dd-mon-yyyy') TO_DATE('July 4, 1997', 'Month dd, yyyy')	CONVERT(datetime, '04-JUL-97') CONVERT (datetime, '04-JUL-1997') CONVERT (datetime, 'July 4, 1997')
Date to character	TO_CHAR(sysdate) TO_CHAR(sysdate, 'dd mon yyyy') TO_CHAR(sysdate, 'mm/dd/yyyy')	CONVERT(char, GETDATE()) CONVERT(char, GETDATE (), 106) CONVERT(char, GETDATE (), 101)
Hex to binary	HEXTORAW('1F')	CONVERT(binary, '1F')
Binary to hex	RAWTOHEX(binary_column)	CONVERT(char,binary_column)

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 you convert 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.

Without century	With century	Standard	Output
-	0 or 100 (*)	Default	mon dd yyyy hh:miAM (or PM)
1	101	USA	mm/dd/yy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yy
4	104	German	dd.mm.yy
5	105	Italian	dd-mm-yy
6	106	-	dd mon yy
7	107	-	mon dd, yy
8	108	-	hh:mm:ss
-	9 or 109 (*)	Default milliseconds	mon dd yyyy hh:mi:ss:mmm (AM or PM)
10	110	USA	mm-dd-yy
11	111	JAPAN	yy/mm/dd
12	112	ISO	yymmdd
-	13 or 113 (*)	Europe default	dd mon yyyy hh:mm:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)

User-Defined Functions

The syntax to create a user-defined function that returns a scalar data type is similar between Oracle and SQL Server 2000.

Oracle	Microsoft SQL Server
SELECT ssn, fname, lname, tuition_paid, tuition_paid/get_sum_major(major) as percent_major FROM student_admin.student	SELECT ssn, fname, lname, tuition_paid, tuition_paid/sum_major as percent_major FROM student_admin.student, (SELECT major, sum(tuition_paid) sum_major FROM student_admin.student GROUP BY major) sum_student WHERE student.major = sum_student.major
CREATE OR REPLACE FUNCTION get_sum_major (inmajor varchar2) RETURN NUMBER AS sum_paid number; BEGIN SELECT sum(tuition_paid) into sum_paid FROM student_admin.student WHERE major = inmajor; RETURN(sum_paid); END get_sum_major;	CREATE FUNCTION get_sum_major (@inmajor varchar(40)) RETURNS money AS BEGIN DECLARE @sum_paid money SELECT@sum_paid = sum(tuition_paid) FROM student_admin.student WHERE major = @inmajor RETURN @sum_paid END

SQL Server user-defined functions also can return a table data type. These functions can be either in-line functions, consisting of a simple SELECT statement or multistatement functions, consisting of many statements used to build a table. Both of these functions can accept parameters and can be accessed in the FROM clause of a Transact-SQL statement. These functions can provide a powerful alternative both to using stored procedures, because their result set can be accessed directly within a Transact-SQL statement, and to using views because they can accept parameters to narrow down a result set.

Here is the syntax to create the user-defined function types that return tables.

Syntax for an inline-table function

CREATE FUNCTION [ owner_name . ] function_name

( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ , ...n ] ] )

RETURNS TABLE

[ WITH <function_option > [ [,] ...n ] ]

[ AS ]

RETURN [ ( ] select-stmt [ ) ]

Syntax for a multistatement table function

CREATE FUNCTION [ owner_name . ] function_name

( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ , ...n ] ] )

RETURNS @return_variable TABLE < table_type_definition >

[ WITH < function_option > [ [,] ...n ] ]

[ AS ]

BEGIN

function_body
RETURN
END

Comparison Operators

Oracle and Microsoft SQL Server comparison operators are nearly identical.

Operator	Oracle	Microsoft SQL Server
Equal to	(=)	Same
Greater than	(>)	Same
Less than	(<)	Same
Greater than or equal to	(>=)	Same
Less than or equal to	(<=)	Same
Not equal to	(!=, <>,^=)	Same
Not greater than, not less than	N/A	!> , !<
In any member in set	IN	Same
Not in any member in set	NOT IN	Same
Any value in set	ANY, SOME	Same
Referring to all values in set.	!= ALL, <> ALL, < ALL, > ALL, <= ALL, >= ALL	Same
Like pattern	LIKE	Same
Not like pattern	NOT LIKE	Same
Value between x and y	BETWEEN x AND y	Same
Value not between	NOT BETWEEN	Same
Value exists	EXISTS	Same
Value does not exist	NOT EXISTS	Same
Value {is | is not} NULL	IS NULL, IS NOT NULL	Same. Also supports = NULL, != NULL for backward compatibility (not recommended).

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.

Oracle	Microsoft SQL Server
SELECT * FROM STUDENT_ADMIN.STUDENT WHERE LNAME LIKE 'A%' OR LNAME LIKE 'B%' OR LNAME LIKE 'C%'	SELECT * FROM STUDENT_ADMIN.STUDENT WHERE LNAME LIKE '[ABC]%'

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.

Oracle	Microsoft SQL Server
SELECT FNAME||' '||LNAME AS NAME FROM STUDENT_ADMIN.STUDENT	SELECT FNAME +' '+ LNAME AS NAME FROM STUDENT_ADMIN.STUDENT

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.

Statement	Oracle PL/SQL	Microsoft SQL Server Transact-SQL
Declare variables	DECLARE	DECLARE
Statement block	BEGIN...END;	BEGIN...END
Conditional processing	IF…THEN, ELSIF…THEN, ELSE ENDIF;	IF…[BEGIN…END] ELSE <condition> [BEGIN…END] ELSE IF <condition> CASE expression
Unconditional exit	RETURN	RETURN
Unconditional exit to the statement following the end of the current program block	EXIT	BREAK
Restarts a WHILE loop	N/A	CONTINUE
Wait for a specified interval	N/A (dbms_lock.sleep)	WAITFOR
Loop control	WHILE LOOP…END LOOP; LABEL…GOTO LABEL; FOR…END LOOP; LOOP…END LOOP;	WHILE <condition> BEGIN… END LABEL…GOTO LABEL
Program comments	/* … */, --	/* … */, --
Print output	RDBMS_OUTPUT.PUT_LINE	PRINT
Raise program error	RAISE_APPLICATION_ERROR	RAISERROR
Execute program	EXECUTE	EXECUTE
Statement terminator	Semicolon (;)	N/A

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.

Oracle	Microsoft SQL Server
DECLARE VSSN CHAR(9); VFNAME VARCHAR2(12); VLNAME VARCHAR2(20); VBIRTH_DATE DATE; VLOAN_AMOUNT NUMBER(12,2);	DECLARE @VSSN CHAR(9), @VFNAME VARCHAR2(12), @VLNAME VARCHAR2(20), @VBIRTH_DATE DATETIME, @VLOAN_AMOUNT NUMERIC(12,2)

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.

Oracle	Microsoft SQL Server
Assignment operator (:=)	SET @local_variable = value
SELECT...INTO syntax for selecting column values from a single row	SELECT @local_varialbe = expression [FROM…] for assigning a literal value, an expression involving other local variables, or a column value from a single row
FETCH…INTO syntax	FETCH…INTO syntax

Here are some syntax examples.

Oracle	Microsoft SQL Server
DECLARE VSSN CHAR(9); VFNAME VARCHAR2(12); VLNAME VARCHAR2(20); BEGIN VSSN := '123448887'; SELECT FNAME, LNAME INTO VFNAME, VLNAME FROM STUDENTS WHERE SSN=VSSN; END;	DECLARE @VSSN CHAR(9), @VFNAME VARCHAR(12), @VLNAME VARCHAR(20) SET @VSSN = '12355887' SELECT @VFNAME=FNAME, @VLNAME=LNAME FROM STUDENTS WHERE SSN = @VSSN

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.

Oracle	Microsoft SQL Server
DECLARE DECLARE VARIABLES ... BEGIN -- THIS IS REQUIRED SYNTAX PROGRAM_STATEMENTS ... IF ...THEN STATEMENT1; STATEMENT2; STATEMENTN; END IF; WHILE ... LOOP STATEMENT1; STATEMENT2; STATEMENTN; END LOOP; END; -- THIS IS REQUIRED SYNTAX	DECLARE DECLARE VARIABLES ... BEGIN -- THIS IS OPTIONAL SYNTAX PROGRAM_STATEMENTS ... IF ... BEGIN STATEMENT1 STATEMENT2 STATEMENTN END WHILE ... BEGIN STATEMENT1 STATEMENT2 STATEMENTN END END -- THIS IS REQUIRED SYNTAX

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.

Oracle	Microsoft SQL Server
DECLARE VDEGREE_PROGRAM CHAR(1); VDEGREE_PROGRAM_NAME VARCHAR2(20); BEGIN VDEGREE_PROGRAM := 'U'; IF VDEGREE_PROGRAM = 'U' THEN VDEGREE_PROGRAM_NAME := 'Undergraduate'; ELSIF VDEGREE_PROGRAM = 'M' THEN VDEGREE_PROGRAM_NAME := 'Masters'; ELSIF VDEGREE_PROGRAM = 'P' THEN VDEGREE_PROGRAM_NAME := 'PhD'; ELSE VDEGREE_PROGRAM_NAME := 'Unknown'; END IF; END;	DECLARE @VDEGREE_PROGRAM CHAR(1), @VDEGREE_PROGRAM_NAME VARCHAR(20) SELECT @VDEGREE_PROGRAM = 'U' SELECT @VDEGREE_PROGRAM_NAME = CASE @VDEGREE_PROGRAM WHEN 'U' THEN 'Undergraduate' WHEN 'M' THEN 'Masters' WHEN 'P' THEN 'PhD'. ELSE 'Unknown' END

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.

Oracle	Microsoft SQL Server
DECLARE COUNTER NUMBER; BEGIN COUNTER := 0 WHILE (COUNTER <5) LOOP COUNTER := COUNTER + 1; END LOOP; END;	DECLARE @COUNTER NUMERIC SELECT@COUNTER = 1 WHILE (@COUNTER <5) BEGIN SELECT @COUNTER = @COUNTER +1 END

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.

Oracle	Microsoft SQL Server
GOTO label; <<label name here>>	GOTO label :label

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.

Oracle	Microsoft SQL Server
RETURN expression:	RETURN integer_expression

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, and 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.

Oracle	Microsoft SQL Server
CREATE OR REPLACE FUNCTION DEPT_ADMIN.DELETE_DEPT (VDEPT IN VARCHAR2) RETURN NUMBER AS BEGIN DELETE FROM DEPT_ADMIN.DEPT WHERE DEPT = VDEPT; RETURN(SQL%ROWCOUNT); EXCEPTION WHEN OTHER THEN RAISE_APPLICATION_ERROR (-20001,SQLERRM); END DELETE_DEPT; /	CREATE PROCEDURE DEPT_ADMIN.DELETE_DEPT @VDEPT VARCHAR(4) AS DELETE FROM DEPT_DB.DBO.DEPT WHERE DEPT = @VDEPT RETURN @@ROWCOUNT GO

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 OLE DB 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.

Cursor Syntax

The table shows the syntax for using cursors.

Operation	Oracle	Microsoft SQL Server
Declaring a cursor	CURSOR cursor_name [(cursor_parameter(s))] IS select_statement;	DECLARE cursor_name CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] FOR select_statement [FOR UPDATE [OF column_name [,…n]]]
Opening a cursor	OPEN cursor_name [( cursor_parameter(s))];	OPEN cursor_name
Fetching from cursor	FETCH cursor_name INTO variable(s)	FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar} | RELATIVE {n | @nvar}] FROM] cursor_name [INTO @variable(s)]
Update fetched row	UPDATE table_name SET statement(s)… WHERE CURRENT OF cursor_name;	UPDATE table_name SET statement(s)… WHERE CURRENT OF cursor_name
Delete fetched row	DELETE FROM table_name WHERE CURRENT OF cursor_name;	DELETE FROM table_name WHERE CURRENT OF cursor_name
Closing cursor	CLOSE cursor_name;	CLOSE cursor_name
Remove cursor data structures	N/A	DEALLOCATE cursor_name

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

Scroll option	Description
NEXT	Returns the first row of the result set if this is the first fetch against the cursor; otherwise, it moves the cursor one row within the result set. NEXT is the primary method used to move through a result set. NEXT is the default cursor fetch.
PRIOR	Returns the previous row within the result set.
FIRST	Moves the cursor to the first row within the result set and returns the first row.
LAST	Moves the cursor to the last row within the result set and returns the last row.
ABSOLUTE n	Returns the nth row within the result set. If n is a negative value, the returned row is the nth row counting backward from the last row of the result set.
RELATIVE n	Returns the nth row after the currently fetched row. If n is a negative value, the returned row is the nth row counting backward from the relative position of the cursor.

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.

Oracle	Microsoft SQL Server
DECLARE VSSN CHAR(9); VFNAME VARCHAR(12); VLNAME VARCHAR(20); CURSOR CUR1 IS SELECT SSN, FNAME, LNAME FROM STUDENT ORDER BY LNAME; BEGIN OPEN CUR1; FETCH CUR1 INTO VSSN, VFNAME, VLNAME; WHILE (CUR1%FOUND) LOOP FETCH CUR1 INTO VSSN, VFNAME, VLNAME; END LOOP; CLOSE CUR1; END;	DECLARE @VSSN CHAR(9), @VFNAME VARCHAR(12), @VLNAME VARCHAR(20) DECLARE curl CURSOR FOR SELECT SSN, FNAME, LNAME FROM STUDENT ORDER BY SSN OPEN CUR1 FETCH NEXT FROM CUR1 INTO @VSSN, @VFNAME, @VLNAME WHILE (@@FETCH_STATUS <> -1) BEGIN FETCH NEXT FROM CUR1 INTO @VSSN, @VFNAME, @VLNAME END CLOSE CUR1 DEALLOCATE CUR1

For more information about cursors, see "Cursors" in SQL Server Books Online.

Tuning Transact-SQL Statements

This section provides information about several SQL Server tools you can use to tune Transact-SQL statements.

Microsoft SQL Server 2000 is a largely auto-configuring and self-tuning database server, dramatically reducing the burden of server configuration on the database administrator. In most cases, SQL Server runs best when these autotuning parameters are left at their default settings and when administrators allow SQL Server to handle the performance tuning. For the latest information about tuning your SQL Server database, see https://msdn2.microsoft.com/sqlserver/default.aspx.

SQL Query Analyzer

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

SQL Profiler

This graphical tool captures a continuous record of server activity in real-time. SQL 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 server running SQL Server.

SQL 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 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 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 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 XML

Microsoft SQL Server 2000 introduces new features to support XML functionality. Using XML, you can:

• SELECT, INSERT and UPDATE a SQL Server database.

• Use Xpath queries against XDR (XML Data Reduced schemas).

• Format the results of Transact-SQL statements in XML using FOR XML.

For more information about SQL Server XML support, see Chapter 31, "Exposing SQL Server Data to the Web with XML" in this book and "XML and Internet Support Overview" in SQL Server Books Online. You can also search the MSDN® Library for the articles "Duwamish Online SQL Server XML Catalog Browsing" and "SQL Server XML and Web Application Architecture" at https://msdn2.microsoft.com/xml/default.aspx.

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

Although 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 recommended that you limit the use of server cursors to those parts of your application that need their features.

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.

• Shopping provides an analogy. Assume you purchase several bags of goods, load one bag into your car, drive home, drop it off, and return 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 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 most available Oracle ODBC drivers.

Microsoft SQL Server data type	ODBC SQL data type
binary	SQL_BINARY
bit	SQL_BIT
char, character	SQL_CHAR
datetime	SQL_TIMESTAMP
decimal, dec	SQL_DECIMAL
float, double precision, float(n) for n = 8-15	SQL_FLOAT
image	SQL_LONGVARBINARY
int, integer	SQL_INTEGER
money	SQL_DECIMAL
nchar	SQL_WCHAR
ntext	SQL_WLONGVARCHAR
numeric	SQL_NUMERIC
nvarchar	SQL_WVARCHAR
real, float(n) for n = 1-7	SQL_REAL
smalldatetime	SQL_TIMESTAMP
smallint	SQL_SMALLINT
smallmoney	SQL_DECIMAL
sysname	SQL_VARCHAR
text	SQL_LONGVARCHAR
timestamp	SQL_BINARY
tinyint	SQL_TINYINT
uniqueidentifier	SQL_GUID
varbinary	SQL_VARBINARY
varchar	SQL_VARCHAR

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 SQL Server ODBC driver for Oracle are shown in this table.

Oracle data type	ODBC SQL data type
CHAR	SQL_CHAR
DATE	SQL_TIMESTAMP
LONG	SQL_LONGVARCHAR
LONG RAW	SQL_LONGVARBINARY
NUMBER	SQL_FLOAT
NUMBER(P)	SQL_DECIMAL
NUMBER(P,S)	SQL_DECIMAL
RAW	SQL_BINARY
VARCHAR2	SQL_VARCHAR

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

ODBC Extended SQL and SQL-92	Oracle	Microsoft SQL Server
SELECT STUDENT.SSN, FNAME, LNAME, CCODE, GRADE FROM {oj STUDENT LEFT OUTER JOIN GRADE ON STUDENT.SSN = GRADE.SSN}	SELECT STUDENT.SSN, FNAME, LNAME, CCODE, GRADE FROM STUDENT, GRADE WHERE STUDENT.SSN = GRADE.SSN(+)	SELECT STUDENT.SSN, FNAME, LNAME, CCODE, GRADE FROM STUDENT LEFT OUTER JOIN GRADE ON STUDENT.SSN = GRADE.SSN

Date, Time, and Timestamp Values

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

Category	Shorthand syntax	Format
Date	{d 'value'}	"yyyy-mm-dd"
Time	{t 'value'}	"hh:mm:ss"
Timestamp	{Ts 'value'}	"yyyy-mm-dd hh:mm:ss[.f…]"

The format of dates has more of an impact on Oracle applications than on SQL Server–based 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.

ODBC Extended SQL	Oracle	Microsoft SQL Server
SELECT SSN, FNAME, LNAME, BIRTH_DATE FROM STUDENT WHERE BIRTH_DATE < {D '1970-07-04'}	SELECT SSN, FNAME, LNAME, BIRTH_DATE FROM STUDENT WHERE BIRTH_DATE < TO_DATE('1970-07-04', 'YYYY-MM-DD')	SELECT SSN, FNAME, LNAME, BIRTH_DATE FROM STUDENT WHERE BIRTH_DATE < '1970-07-04'

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–based 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. In this example, the SQL Server procedure is more efficient.

Generic ODBC Extended SQL	Oracle	Microsoft SQL Server
{?=} call procedure_name[(parameter(s))]} SQLExecDirect(hstmt1,(SQLCHAR *)"{? = call owner.procedure(?)}", SQL_NTS);	SQLExecDirect(hstmt1, (SQLCHAR*)"{? = call STUDENT_ADMIN.P1. SHOW_RELUCTANT _STUDENTS(?)}", SQL_NTS);	SQLExecDirect(hstmt1, (SQLCHAR*)"{? = call STUDENT_ADMIN. SHOW_RELUCTANT _STUDENTS}", SQL_NTS);

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.

Original statement	Converted Oracle statement	Converted SQL Server statement
SELECT (fn CONVERT (SSN, SQL_INTEGER)) FROM STUDENT	SELECT TO_NUMBER(SSN) FROM STUDENT	SELECT CONVERT(INT,SSN) FROM STUDENT

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–based application in implicit transaction mode. The SQL_AUTOCOMMIT option must be set to SQL_AUTOCOMMIT_OFF in order to accomplish this. This code excerpt 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–based 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.

Developing and Administering Database Replication

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

Oracle	Microsoft SQL Server
Read-Only Snapshot Replication	Snapshot with immediate updating subscribers
Multimaster Replication, Updatable snapshot	Transactional replication with immediate updating subscribers— better for well-connected subscribers who are online when updating. Merge Replication—better for mobile disconnected. This supports default and custom conflict resolution.

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 table compares conflict-resolution mechanisms for Oracle and SQL Server:

Oracle	Microsoft SQL Server
Site priority or priority value resolvers programmed using PL/SQL	Priority-based resolution using COM or Transact-SQL.
Conflict resolution for column groups	Supported through custom resolvers.
Column-level and row-level conflict resolution	Support for both.

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 2000–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 registry, the Oracle ODBC driver connects to Oracle without requesting a password. If a password is not provided in the Windows 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 DTS

The simplest method of migrating between Oracle and SQL Server is to use the Data Transformation Services (DTS) feature in Microsoft SQL Server 2000. The DTS Import/Export 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.

Oracle Call Interface	Oracle ODBC
rcl = olog(&logon_data_area, &host_data_area, user_name, -1, (text*) 0, -1, (text) 0, -1, OCI_LM_DEF);	rcl = SQLConnect(hdbc1, (SQLCHAR*) ODBC_dsn, (SQLSMALLINT) SQL_NTS, (SQLCHAR*) user_name, (SQLSMALLINT) SQL_NTS, (SQLCHAR*) user_password, (SQLSMALLINT) SQL_NTS);

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.

OCI function	ODBC function
Obindps	SQLBindParameter
Obndra	SQLBindParameter
Obndrn	SQLBindParameter
Obndrv	SQLBindParameter
Obreak	SQLCancel
Ocan	SQLCancel, SQLFreeStmt
Oclose	SQLFreeStmt
Ocof	SQLSetConnectOption
Ocom	SQLTransact
Ocon	SQLSetConnectOption
Odefin	SQLBindCol
Odefinps	SQLBindCol
Odescr	SQLDescribeCol
Oerhms	SQLError
Oexec	SQLExecute, SQLExecDirect
Oexfet	SQLExecute, SQLExecDirect, and SQLFetch
Oexn	SQLExecute, SQLExecDirect
Ofen	SQLExtendedFetch
Ofetch	SQLFetch
Oflng	SQLGetData
Ogetpi	SQLGetData
Olog	SQLConnect
Ologof	SQLDisconnect
Oopen	SQLExecute, SQLExecDirect
Oparse	SQLPrepare
Orol	SQLTransact

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 Microsoft Visual 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.

Embedded SQL statement	ODBC function
CONNECT	SQLConnect
PREPARE	SQLPrepare
EXECUTE	SQLExecute
DECLARE CURSOR and OPEN CURSOR	SQLExecute
EXECUTE IMMEDIATE	SQLExecDirect
DESCRIBE SELECT LIST	SQLNumResultCols, SQLColAttributes, SQLDescribeCol
FETCH	SQLFetch
SQLCA.SQLERRD[2]	SQLRowCount
CLOSE	SQLFreeStmt
COMMIT WORK, ROLLBACK WORK	SQLTransact
COMMIT WORK RELEASE, ROLLBACK WORK RELEASE	SQLDisconnect
SQLCA, SQLSTATE	SQLError
ALTER, CREATE, DROP, GRANT, REVOKE	SQLExecute, SQLExecDirect

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.

Oracle Pro*C and EMBEDDED SQL	Oracle ODBC
EXEC SQL DECLARE CURSOR C1 CURSOR FOR SELECT SSN, FNAME, LNAME FROM STUDENT ORDER BY SSN; EXEC SQL OPEN C1; if (sqlca.sqlcode) != 0 { /* handle error condition, look at sqlca.sqlerrm.sqlerrmc for error description...*/}	if (SQLExecDirect(hstmtl, (SQLCHAR*)"SELECT SSN, FNAME, LNAME FROM STUDENT ORDER BY SSN", SQL_NTS) != SQL_SUCCESS) { /* handle error condition, use SQLError for SQLSTATE details regarding error...*/}

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 the Microsoft Visual Studio® development system, 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 chapter.

Internet Applications

Microsoft SQL Server includes the Web Assistant Wizard, which 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.