Chapter 11 - Migrating from Other Products to SQL Server

If you currently use another database management system (DBMS), you can migrate to Microsoft SQL Server. Migrating from another DBMS to SQL Server involves transferring not only the data stored in the DBMS, but the databases and tables that hold the data, and other objects that interact with the data as well.

While most DBMSs work in similar ways, the architecture and terminology used can vary. For instance, there is not always a one-to-one correlation between an object in SQL Server and an object in another DBMS. Also, each DBMS creates its own extensions to the SQL-92 standard; the particular features associated with a common SQL statement can vary between SQL Server and another DBMS. Part of the migration task is making adjustments to database objects to account for these differences while making the best use of new features found in SQL Server version 7.0.

These steps are recommended when migrating to SQL Server:

1. Back up your existing database. 

2. Install SQL Server 7.0. 

3. Create databases and database objects in SQL Server. 

   If you have an existing object creation script, you can modify it to SQL Server specifications. If you do not have an existing script, you can either create one or use SQL Server Enterprise Manager to create your database objects. 

4. Alter or create applications to work with SQL Server. 

   Modify existing SQL syntax and functions to meet SQL Server specifications and to take advantage of SQL Server features. 

5. Transfer data to SQL Server. 

   Use one of several tools for importing data into SQL Server. 

6. Remove the old database after testing the new SQL Server installation. You may want to keep a copy or a backup for future reference. 

Specific information is included for migrating to SQL Server from Oracle and Microsoft Access. This material describes many of the issues you may encounter in migrating existing Oracle and Access databases.

See Also 

In This Volume 

Copying Data using the Data Transformation Services Wizard

Importing and Exporting Data

Migrating from Oracle

There are many architectural differences between Oracle and Microsoft SQL Server. The words and terminology used to describe Oracle architecture often have different meanings in SQL Server. Additionally, both Oracle and SQL Server have made numerous proprietary extensions to the SQL-92 standard.

Before you begin the migration process, familiarize yourself with SQL Server so that you are prepared to reconcile the differences between it and Oracle.

Oracle and SQL Server Terminology

This table compares key terms in Oracle and Microsoft SQL Server.

Oracle	SQL Server
archived redo log	backup transaction log
control file	master database
data block	page
data dictionary	database catalog
data dictionary view	system table
database verification utility	database consistency checker
export file	data file
library cache	procedure cache
online redo log	transaction log
redo buffer	log buffer
redo log file	backup file
sequence	identity column
system global area (SGA)	memory buffers
tablespace	filegroup

This table compares replication terms in Oracle and SQL Server.

Oracle	SQL Server
master database	publication database
master definition site	Publisher
master site	Distributor
read-only snapshot	read-only replica
snapshot site	Subscriber

Migrating Database Objects from Oracle to SQL Server

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 modify it to conform to your Microsoft SQL Server syntax.

If you do not have a script or program to start with, you can either write a script to create your Microsoft SQL Server database objects or use SQL Server Enterprise Manager.

Basic Statistics for Oracle and SQL Server

This table compares basic statistics for Oracle and SQL Server.

Category	Oracle	SQL Server
Number of columns	1,000	1,024
Row size	Unlimited (only one long or long raw allowed per row)	8,060 bytes (this does not include text or image columns)
Maximum number of rows	Unlimited	Unlimited
Blob type storage	Any number of LOB datatypes per row, only locator is stored with the row One long or long raw per table, must be at end of row, data stored on same block(s) with row	Any number of image or text columns per row, only 16-byte pointer stored with row, data stored on other data pages
Clustered table indexes	0	1 per table
Nonclustered table indexes	Unlimited	249 per table
Maximum number of indexed columns in a single index	32	16
Table naming convention	[schema.]table_name	[[[server.][database].][owner].]table_name
View naming convention	[schema.]view_name	[[[server.][database].][owner].]view_name
Identifier length	1 through 30 characters in length	1 through 128 characters in length
Allowable identifier characters	Identifier names must begin with an alphabetical character and contain alphanumeric characters or the characters _, $, and #	Identifier names must begin with a letter as defined by the Unicode standard or a character _ (underscore), @ (at sign), or # (number sign), and contain letters as defined by the Unicode standard, decimal numbers from either Basic Latin or other national scripts, or the characters _, @, $ (dollar sign), and #
Unique identifiers	Tablespace names must be unique	Database names must be unique within a server
	Index names must be unique within a user schema	Index names must be unique within a database

See Also 

In Other Volumes 

"Maximum Capacity Specifications" in Microsoft SQL Server Introduction 

"Using Identifiers" in Microsoft SQL Server Database Developer's Companion 

Referencing Objects When Migrating from Oracle

Qualifying Objects 

When referencing an object that exists in your Oracle user account, the object can be referenced by its unqualified name (for example, SELECT * FROM STUDENT). When referencing objects that exist in other schemas, the schema name must be prefixed to the object name with a single period (SELECT * FROM STUDENT_ADMIN.STUDENT).

For Microsoft SQL Server tables, views, and stored procedures, the complete name of the object is comprised of four identifiers: server name, database name, owner name, and object name in the format:

[[[server.][database].][owner].]object

The server, database, and owner names are known as the qualifiers of the object name. When referencing a table, view, or stored procedure, you can specify any, all, or none of the qualifiers.

Case Sensitivity 

When referring to objects in Oracle, the use of case is not important. In SQL Server, object names can be case-sensitive, depending on the character sort order that is installed. The sort order is chosen in SQL Server Setup during installation. The default sort order in SQL Server is dictionary order, case-insensitive.

If your SQL Server installation is case-sensitive, it is recommended that you capitalize all table and column names in both Oracle and SQL Server to avoid any problems.

Using Synonyms 

In Oracle, a public or private synonym is often used to eliminate the need to specify a username when requesting a table in another schema. SQL Server does not provide public or private synonyms.

See Also 

In This Volume 

Sort Order

In Other Volumes 

"Object Visibility and Qualification Rules" in Microsoft SQL Server Database Developer's Companion 

"Using Identifiers as Object Names" in Microsoft SQL Server Database Developer's Companion 

Migrating Tables from Oracle to SQL Server

CREATE TABLE syntax 

The CREATE TABLE syntax for Oracle and Microsoft SQL Server is similar. Both Oracle and SQL Server support SQL-92 standard naming conventions for identifying database management system (DBMS) objects.

Oracle	SQL Server
CREATE TABLE [schema.]table_name ( { {column_name data_type} [DEFAULT constant_expression] [ column_reference ] [ column constraint ] [...n] | table_reference | table_constraint } [,...n] ) [Oracle Specific Data Storage Parameters]	CREATE TABLE [database_name.[owner]. | owner.] table_name ( { {column_name data_type} [ [DEFAULT constant_expression] | [IDENTITY [(seed, increment) [NOT FOR REPLICATION] ] ] [ROWGUIDCOL] [ column constraint ] [...n] | column_name AS computed_expression | table_constraint } [,...n] ) [ON {filegroup | DEFAULT} ] [TEXTIMAGE_ON {filegroup | DEFAULT }]

Modifying Data Storage Parameters 

While segments are familiar to Oracle administrators and are useful in some high-end configurations, they are not needed for most SQL Server installations. Using segments introduces administrative complexity and the possibility for error. In most cases, hardware-based RAID or Microsoft Windows NT software-based RAID solutions are recommended for use with SQL Server.

Using RAID simplifies the table-definition and index-definition processes. The SQL Server table uses RAID to control its placement.

Oracle

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) ) PCTFREE 10 PCTUSED 40 TABLESPACE USER_DATA STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS UNLIMITED FREELISTS 1)

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 Using SELECT Statements 

When creating tables in SQL Server using SELECT statements, it is recommended that you use a SELECT...INTO statement.

Oracle	SQL Server
CREATE TABLE student_backup as SELECT * FROM student_admin.student	SELECT * INTO student_backup FROM student_admin.student

Note A SELECT...INTO statement does not work unless the database in which this is done has the select into/bulkcopy option set to true. Only members of the db_owner and sysadmin roles can set this option using SQL Server Enterprise Manager or the sp_dboption system stored procedure.

The sp_helpdb system stored procedure can be used to check the status of the database. If select into/bulkcopy is not set to true, you can still use a SELECT statement to copy into a temporary table:

SELECT * INTO #student_backup FROM user_db.student_admin.student

As in Oracle, when a table is copied in SQL Server, the referential integrity definitions are not copied to the new table.

If you need to copy data into tables using a SELECT statement, consider creating the table first, and then using the INSERT INTO...SELECT statement to load the table. This method is compatible between Oracle and SQL Server, and does not require that any database option be set. This method is slower in SQL Server because each row is logged.

See Also 

In Other Volumes 

"Creating and Modifying a Table" in Microsoft SQL Server Database Developer's Companion 

"RAID" in Microsoft SQL Server Diagnostics 

"Setting Database Options" in Microsoft SQL Server Database Developer's Companion 

"sp_helpdb" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"CREATE TABLE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"sp_dboption" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"SELECT INTO" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Migrating Constraints from Oracle to SQL Server

Naming Constraints 

Regardless of the database management system (DBMS) you are using, it is recommended that you always name your constraints. If you do not name your constraints, both Oracle and Microsoft SQL Server name them for you. Because Oracle and SQL Server use different default naming conventions, the differences can unnecessarily complicate your migration process. The discrepancy would appear when dropping or disabling constraints because they must be dropped by name.

PRIMARY KEY and UNIQUE Constraints 

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

A SQL Server clustered index is created by default for a primary key. A nonclustered index can also be requested. The Oracle index 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 DBMS, alternate keys can be defined using a UNIQUE constraint. Multiple UNIQUE constraints can be defined on any table. UNIQUE constraint columns can be nullable. In SQL Server, a nonclustered index is created by default, unless specified otherwise.

Note SQL Server allows only one row to contain the value NULL for the complete unique key (single or multiple column index), while Oracle allows any number of rows to contain the value NULL for the complete unique key.

This table shows the constraint syntax.

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

DEFAULTS and CHECK Constraints 

Oracle treats a default as a column property, while 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.

Because you cannot apply a constraint name to the Oracle DEFAULT column property, it is recommended that you define DEFAULT constraints at the column level in SQL Server and do not apply constraint names to them. 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. Unlike Oracle, SQL Server can define only one column-level CHECK constraint per column per CREATE TABLE statement (although each column constraint can have multiple conditions).

Converting the DBMS-specific syntax is the fundamental issue in migrating these constraints.

Oracle

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

NULL and NOT NULL 

An Oracle table column always defaults to NULL unless specified in the CREATE TABLE or ALTER TABLE statements. In SQL Server, database and session settings can influence and possibly override the nullability of the data type used in a column definition. For more information, see "CREATE TABLE" in Microsoft SQL Server Transact-SQL and Utilities Reference.

To avoid possible confusion when migrating, it is recommended that all of your SQL scripts (whether Oracle or SQL Server) explicitly define both NULL and NOT NULL.

Foreign Keys 

The rules for defining foreign keys are similar in Oracle and SQL Server. The number of columns and data types 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.

The SQL Server constraints can reference only tables within the same database. Table-based triggers must be used to simulate referential integrity across databases.

Both Oracle and SQL Server support self-referenced tables. These are tables in which a reference (foreign key) can be placed against one or more columns on the same table.

Regardless of DBMS, foreign key constraints do not create an index. For performance reasons, it is recommended that you index all of your foreign keys. This allows for faster execution times when a referenced key is modified or used for join purposes.

The Oracle CASCADE DELETE references option is not available with SQL Server. This option is used in situations where both the parent and child values are deleted when a parent row is deleted. In SQL Server, this option must be enforced with table triggers. For more information, see "Nested Triggers" in Microsoft SQL Server Database Developer's Companion.

See Also 

In Other Volumes 

"Foreign Key Constraints" in Microsoft SQL Server Database Developer's Companion 

Adding and Removing Constraints 

Disabling constraints improves performance and streamlines the data replication processes. 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 entered into the table. If your Oracle application currently disables and enables constraints (except for PRIMARY KEY and UNIQUE), this process can be duplicated in SQL Server using the CHECK and WITH NOCHECK options with the ALTER TABLE statement.

You can defer all constraints for the table, including the CHECK and FOREIGN KEY constraints, by using the ALL keyword. However, you cannot defer PRIMARY KEY and UNIQUE constraints because these constraints must be dropped.

If your Oracle application disables or drops PRIMARY KEY or UNIQUE constraints using the CASCADE option, you may need to rewrite some of your program code. This is 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

SQL Server does not support this cascading capability. The SQL Server application must be modified to first drop the child constraints followed by 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 is used to add and drop constraints is almost identical for Oracle and SQL Server.

Migrating Indexes from Oracle to SQL Server

This table shows the CREATE INDEX syntax.

Oracle	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]	CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON [[database.]owner.]table_name (column_name [,column_name]...) [WITH [PAD_INDEX] [[,] FILLFACTOR = fillfactor] [[,] IGNORE_DUP_KEY] [[,] DROP_EXISTING] [[,] STATISTICS_NORECOMPUTE] [ON filegroup]

Nonclustered Indexes 

A nonclustered index is an index that is physically separated from a table. Each is physically separated from each other, and each is considered a separate database object. Because these objects are separate, the physical order of the table rows is not the same as their indexed order. Nonclustered indexes resemble Oracle indexes.

Clustered Indexes 

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 type of architecture permits only one clustered index per table.

Note A Microsoft SQL Server clustered index is not 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 that is similar to an Oracle cluster.

Fill Factor 

The FILLFACTOR option functions in much the same way as the PCTFREE variable functions 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 PAD_INDEX option specifies that the fill factor setting be applied to the decision node pages as well as to the data pages in the index.

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

If you set the PCTFREE parameter to 0 for 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 

The default operation of a unique index in Oracle and SQL Server is almost identical. You cannot insert duplicate values for a uniquely indexed column or columns.

This default operation can be changed by using the IGNORE_DUP_KEY option when the index is created. In this case, when an INSERT or UPDATE attempts to duplicate a currently existing indexed value, the statement is ignored and no error message is returned.

The purpose of IGNORE_DUP_KEY is to allow a transaction to proceed although duplicates are present. In order to ensure maximum compatibility with your Oracle applications, do not set these options when you create indexes.

Other Index Considerations 

Oracle and SQL Server allow up to 16 columns to be defined in an index. However, the sum of the lengths of the columns that make up a SQL Server composite index cannot exceed 900 bytes (versus approximately one-half the block size in Oracle).

In Oracle, an index name must be unique within a user account. In SQL Server, an index name must be unique within a table name; however, it does not have to be unique within a user account or database.

See Also 

In Other Volumes 

"Fill Factor" in Microsoft SQL Server Database Developer's Companion 

"Indexes" in Microsoft SQL Server Database Developer's Companion 

"Using Nonclustered Indexes" in Microsoft SQL Server Database Developer's Companion 

"Using Clustered Indexes" in Microsoft SQL Server Database Developer's Companion 

Data Types in Oracle and SQL Server

Microsoft SQL Server has a larger selection of data types than Oracle. There are many possible conversions between the Oracle and SQL Server data types.

Oracle	SQL Server
CHAR	char is recommended. char type columns are accessed faster than varchar columns because they use a fixed storage length.
VARCHAR2 and VARCHAR	varchar.
LONG	varchar or text. (If the length of the data values in your Oracle column is 8,000 bytes or less, use varchar; otherwise, use text.)
CLOB	text. Oracle CLOB columns can store up to 4GB of data. SQL Server text columns are limited to 2GB.
NCHAR	nchar is recommended. SQL Server nchar columns contain values from the Unicode standard, not a particular national character set.
NVARCHAR2	nvarchar is recommended. SQL Server nvarchar columns contain values from the Unicode standard, not a particular national character set.
NCLOB	ntext. Oracle NCLOB columns can store up to 4GB of data. SQL Server ntext columns are limited to 2GB.
RAW	varbinary.
LONG RAW	varbinary or image. (If the length of the data values in your Oracle column is 8,000 bytes or less, use varbinary; otherwise, use image.)
BLOB	image. Oracle BLOB columns can store up to 4GB of data. SQL Server image columns are limited to 2GB.
BFILE	SQL Server does not support the use of external data sources in the same way as an Oracle BFILE column. External data sources can be accessed by SQL Server but are not mapped to a column.
NUMBER	If the integer is from 1 through 255, use tinyint. If the integer is from -32768 through 32767, use smallint. If the integer is from -2,147,483,648 through 2,147,483,647 use int. If you require a number with decimal places, use decimal. Do not use float or real, because rounding may occur (Oracle NUMBER and SQL Server decimal do not round). If you are not sure, use decimal; it most closely resembles Oracle NUMBER data type.
DATE	datetime or timestamp.
ROWID	Use the identity column type.
MLSLABEL	MLSLABEL is used only for backward compatibility with earlier versions of Oracle using Trusted Oracle. MLSLABEL columns are not needed in SQL Server.

Timestamp Columns 

A timestamp column enables BROWSE-mode updates and makes cursor update operations more efficient. A timestamp column is updated automatically every time a row is inserted or updated.

Datetime Columns 

Use the function GETDATE() in place of the Oracle SYSDATE to get the current system date and time.

Sequences and the IDENTITY Property 

If your Oracle application currently uses sequences to generate sequential numeric values, it can be altered to take advantage of the SQL Server IDENTITY property. The primary difference between SQL Server and Oracle is that the IDENTITY property is actually part of the column, while a sequence is independent of any tables or columns.

See Also 

In Other Volumes 

"Data Types" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"timestamp" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"IDENTITY (Property)" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Migrating Views from Oracle to SQL Server

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

Oracle	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]

The Oracle FORCE option creates a view whether or not the view's base tables exist or the owner of the schema containing the view has privileges on them. 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.

By default, data modification statements on views are not checked to determine if the rows affected are within the scope of the view. If all modifications should be checked, use the WITH CHECK OPTION. Oracle defines the WITH CHECK OPTION as a constraint; SQL Server does not. Otherwise, it functions the same in both.

SQL Server does not offer the WITH READ ONLY option when defining views. The same result can be obtained by granting only SELECT permission to all users of the view.

If you define a SQL Server view with an outer join and then query the view with a qualification on a column from the inner table of the outer join, the results can differ from what is expected. All rows from the inner table are returned. Rows that do not meet the qualification show a null value in the appropriate columns for those rows.

Migrating Temporary Tables from Oracle to SQL Server

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

This is not the case in Microsoft SQL Server. SQL Server allows temporary tables to be created. Regardless of the user, these tables are created in the tempdb database. The naming convention used with these tables controls how long they reside within the tempdb database.

Table name	Description
#table_name	Local temporary table, which exists only for the duration of a user session or the procedure that created it. It is dropped automatically when the user logs off or when the procedure that created the table completes. This table cannot be shared among multiple users. No other database users can access this table. Permissions cannot be granted or revoked on this table.
##table_name	Global temporary table, which exists for the duration of a user session or procedure that created it. It is dropped automatically when the last user session referencing it disconnects. This table can be shared among multiple users. All other database users can access this table. Permissions cannot be granted or revoked on this table.
tempdb..table_name	This table continues to exist until it is dropped or until SQL Server is restarted. Permissions can be granted and revoked on this table. To create this table, the database user must have CREATE TABLE permission in the tempdb database.

Indexes can be defined for temporary tables. Views can be defined only on tables created explicitly 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 dropped automatically:

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)

To ensure complete compatibility with an Oracle application, you may not want to use temporary tables. However, you may find that the benefits associated with using them justify a slight revision in program code.

Object-level Permissions

The syntax of the GRANT statement in Oracle and Microsoft SQL Server is similar.

Oracle	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_list} {[column_list] ON {table | view} | ON {table | view} [(column_list)] | ON {stored_procedure | extended_procedure} } TO security_account_list [WITH GRANT OPTION] [AS {group | role}]

Transactions, Locking, and Concurrency in Oracle and SQL Server

This section explains how transactions are executed in both Oracle and Microsoft SQL Server and discusses the difference between the locking process and concurrency issues in both database types.

Transactions in Oracle and SQL Server

In Oracle, a transaction is started automatically whenever an INSERT, UPDATE, or DELETE operation is performed. An application must issue a COMMIT to save all changes to data. If a COMMIT is not performed, all changes are rolled back or undone automatically.

You can start transactions in Microsoft SQL Server as explicit, autocommit, or implicit transactions.

Explicit transactions

Explicitly start a transaction by issuing a BEGIN TRANSACTION statement.

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.

The SQL Server SAVE TRANSACTION statement functions in the same way as the Oracle SAVEPOINT command. It sets a savepoint in the transaction, allowing partial rollbacks.

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

See Also 

In Other Volumes 

"Autocommit Transactions" in Microsoft SQL Server Database Developer's Companion 

"Implicit Transactions" in Microsoft SQL Server Database Developer's Companion 

"SET IMPLICIT_TRANSACTIONS" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Explicit Transactions" in Microsoft SQL Server Database Developer's Companion 

"SAVE TRANSACTION" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Transactions" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Locking and Transaction Isolation in Oracle and SQL Server

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

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

Data writers in Oracle perform 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.

Changing Default Locking Behavior in Oracle and SQL Server

Both Microsoft SQL Server and Oracle allow the developer to request non-default locking and isolation behavior. In Oracle, the most common mechanisms for this are the FOR UPDATE clause on a SELECT statement, the SET TRANSACTION READ ONLY statement, and the explicit LOCK TABLE command.

Because their overall 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, the options that SQL Server provides for changing its default behavior must be examined.

In SQL Server, the most common mechanisms are the SET TRANSACTION ISOLATION LEVEL statement and the locking hints that are supported in SELECT and UPDATE statements. The SQL Server SET TRANSACTION ISOLATION LEVEL statement is used to set 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 default isolation level for SQL Server is READ COMMITTED (SET TRANSACTION ISOLATION LEVEL READ COMMITTED). When using this option, your application cannot read data that has not yet been committed by other transactions. In this mode, shared locks are released as soon as the data has been read from a page. If the application goes to reread the same data range within the same transaction, it may see other users' changes.

To prevent this from occurring in your application, set the transaction isolation level to SERIALIZABLE. This option directs SQL Server to hold all shared locks until the end of a transaction. You can achieve the same effect on a more granular level by using the HOLDLOCK hint after the table name in SELECT statement. Both of these options represent a tradeoff of concurrency for strict consistency, and should be used only when necessary.

The READ UNCOMMITTED option (SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED) implements dirty reads, which means that no shared locks are issued and no exclusive locks are honored by readers. With this option set, SQL Server readers are nonblocking, as in Oracle. When this option is set, an application can read data that has not yet been committed by another user. For some applications, such as statistical reporting summaries, this may not be a problem. For other applications, such as a database queuing table, this may be a problem.

Use READ UNCOMMITTED isolation only after you thoroughly analyze how it may affect the correctness of your application.

READ ONLY Transactions 

SQL Server does not directly support the READ ONLY transaction level offered by Oracle. If some transactions in an application require 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 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 obtaining an update lock on the data as well. When using ODBC, you can also achieve a similar effect using SQLSETSTMTOPTION (...,SQL_CONCURRENCY)= SQL_CONCUR_LOCK. Both of these options reduce concurrency.

Table-level Locks in Oracle and SQL Server

Microsoft SQL Server can be directed to lock an entire table using the SELECT...table_name (TABLOCK) statement. This performs the same operation as the Oracle LOCK TABLE...IN SHARE MODE statement. This lock allows others to read a table, but prevents them from updating it. By default, this lock is held until the end of the statement. If you also add the keyword HOLDLOCK (SELECT...table_name (TABLOCK HOLDLOCK)), the table lock is held until the end of the transaction.

An exclusive lock can be placed on a SQL Server table using the SELECT...table_name (TABLOCKX) statement. This statement requests an exclusive lock on a table. It is used to prevent others from reading or updating the table and is held until the end of the command or transaction. It is similar in function to the Oracle LOCK TABLE...IN EXCLUSIVE MODE statement.

SQL Server does not offer a NOWAIT option for any of its explicit lock requests.

Handling Deadlocks in Oracle and SQL Server

A deadlock occurs when one process locks a page or a table while another process needs it, and the second process has a lock that the first process needs. A deadlock is also known as a deadly embrace. Microsoft SQL Server detects and resolves deadlocks automatically. 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 error message number 1205, which indicates a deadlock situation. If this error message number is returned, a deadlock has occurred and the transaction was rolled back. In this situation, your application must restart the transaction.

Distributed Transactions in Oracle and SQL Server

Remote Transactions 

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

Distributed Transactions 

Oracle initiates a distributed transaction automatically 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.

Two-phase Commit Processing 

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

If all resource managers can prepare successfully, 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 rolled back. MS DTC keeps a sequential log so that its commit or roll back decisions are durable. If a resource manager or transaction manager fails, they reconcile in-doubt transactions when they reconnect.

Migrating SQL Language Support from Oracle to SQL Server

Recommended Conversion Strategy 

Use the following checklist when migrating your Oracle DML statements and PL/SQL programs to Transact-SQL:

1. Verify that the syntax for of all SELECT, INSERT, UPDATE, and DELETE statements is valid. Make any required modifications. 

2. Modify all outer joins to SQL-92 standard outer join syntax. 

3. Replace Oracle functions with the appropriate SQL Server functions. 

4. Verify all comparison operators.

5. Replace the "||" string concatenation operators with the "+" string concatenation operators. 

6. Modify all PL/SQL cursors to either noncursor SELECT statements or Transact-SQL cursors. 

7. Replace or convert PL/SQL procedures, functions, and packages with Transact-SQL procedures. 

8. Convert PL/SQL triggers to Transact-SQL triggers. 

Migrating INSERT Statements from Oracle to SQL Server

The INSERT statements for Oracle and Microsoft SQL Server are similar.

Oracle	SQL Server
INSERT INTO {table_name | view_name | select_statement } [(column_list)] {values_list | select_statement}	INSERT INTO {table_name | view_name} [(column_list)] {values_list | select_statement | execute_statement }

See Also 

In Other Volumes 

"INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Migrating UPDATE Statements from Oracle to SQL Server

Because Transact-SQL supports most of the syntax used with the Oracle UPDATE statement, minimum revision is required.

Oracle	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 | view_name} SET [column_name(s) = {constant_value | expression | select_statement | column_list | default | variable_list ] [FROM { table_name | view_name }[( optimizer_hints )}] [where_statement}

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 Microsoft SQL Server UPDATE statement.

See Also 

In Other Volumes 

"UPDATE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Migrating DELETE Statements from Oracle to SQL Server

In most cases, you do not need to modify DELETE statements.

Oracle	SQL Server
DELETE [FROM] {table_name | view_name | select_statement } [WHERE clause]	DELETE [FROM] {table_name | view_name} [FROM { table_name | view_name }[( optimizer_hints )}] [WHERE clause]

See Also 

In Other Volumes 

"DELETE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Migrating TRUNCATE TABLE Statements from Oracle to SQL Server

The TRUNCATE TABLE statement is similar between Oracle and Microsoft SQL Server. It is used to remove all of the rows from a table. The table structure and all of its indexes will continue to exist; it cannot be rolled back. Delete triggers will not be executed. A table cannot be truncated if it is referenced by a FOREIGN KEY constraint.

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

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

The Oracle TRUNCATE TABLE command can be directed to release or to keep 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.

See Also 

In Other Volumes 

"TRUNCATE TABLE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Manipulating Data in Identity and timestamp Columns

Oracle sequences are not related to any given table directly. Therefore, Oracle does not enforce any rules when working with sequences. However, Microsoft SQL Server is strict when using identity columns: the DEFAULT keyword cannot be used when working with identity columns. Additionally, values in identity columns cannot be updated.

By default, data cannot be inserted directly into an identity column. The identity column generates a unique, sequential number automatically 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 is allowed to insert any value into a new row with an identity column. 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 global variable can be used to obtain the last identity value.

The TRUNCATE TABLE statement resets an identity column to its original SEED value. This can affect any type of synchronization that exists with Oracle tables using sequences because sequences are not reset following the TRUNCATE TABLE command.

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 can only perform 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.

Migrating text, ntext, and image Columns from Oracle to SQL Server

Modifying text, ntext and image Columns 

When migrating to Microsoft SQL Server, you use text, ntext and image columns in place of LONG, CLOB, NCLOB, LONG RAW and BLOB columns. In Oracle, you use UPDATE and INSERT statements to change values in these 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, ntext or image column.

Reading text, ntext and image Columns 

In Oracle, you use a SELECT statement to query the values in LONG and LONG RAW columns. Because the LONG, CLOB, NCLOB, LONG RAW or BLOB column is part of a queried row, no specialized functions are required to access this information.

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

See Also 

In Other Volumes 

"Modifying ntext, text or image Values" in Microsoft SQL Server Database Developer's Companion 

"READTEXT" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"WRITETEXT" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Retrieving ntext, text or image Values" in Microsoft SQL Server Database Developer's Companion 

"UPDATETEXT" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Migrating SELECT Statements from Oracle to SQL Server

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

Oracle	SQL Server
SELECT [/*+ optimizer_hints*/]  [ALL | DISTINCT] select_list [FROM {table_name | view_name | select_statement}] [WHERE clause] [GROUP BY clause] [HAVING clause] [START WITH ... CONNECT BY] [{UNION | UNION ALL | INTERSECT | MINUS} SELECT ...] [ORDER BY clause] [FOR UPDATE]	SELECT [ALL | DISTINCT] select_list  [INTO [ new_table_name ]]  [FROM {table_name | view_name | select_statement} [JOIN { table_name | view_name | select_statement } ON search_conditions ] [( optimizer_hints )]  [WHERE clause] [GROUP BY clause] [HAVING clause] [{UNION | UNION ALL} SELECT ...] [ORDER BY clause] [COMPUTE clause]  [FOR BROWSE]  [OPTION ( query_hints )]

Optimizer Hints 

Oracle-specific, cost-based optimizer hints are not supported by SQL Server. They must be removed from your statement code. The recommended technique is to assume that the default SQL Server cost-based optimization will work well.

Exploring Hierarchies

SQL Server does not support the Oracle START WITH...CONNECT BY clause. If your application currently takes advantage of this hierarchical reporting capability, those statements that include this clause must be rewritten. You can replace this in SQL Server with a user-written procedure that performs the same task.

Replacing INTERSECT with EXISTS 

The INTERSECT set operator is not supported by SQL Server; however, the EXISTS clause can be used to obtain the same result. This example uses the INTERSECT operator to find the course code and course name for all classes that have students. The EXISTS operator replaces the use of the INTERSECT operator. The data that is returned is identical, regardless of query.

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

Replacing MINUS with NOT EXISTS 

The MINUS set operator is not supported by SQL Server, but the NOT EXISTS clause can be used to obtain the same result. This example uses the MINUS operator to find those classes that do not have any students. The EXISTS operator replaces the use of the INTERSECT operator. The data that is returned is identical, regardless of query.

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

Locking Requested Rows 

Oracle uses the FOR UPDATE clause to lock rows specified in the SELECT statement. Usually you do not need to use the equivalent clause in SQL Server. Do not confuse the FOR UPDATE and the FOR BROWSE clauses. The FOR BROWSE clause is a specialized facility for use in client application programs that need additional metadata at run time.

Row Aggregates and the Compute Clause 

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

Note The COMPUTE clause works just like the COMPUTE command found in the Oracle SQL*Plus query tool.

Migrating Join Clauses from Oracle to SQL Server

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

When using outer joins in Oracle, the outer join operator (+) is typically placed 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 nulls, in which case the outer join operator (+) may be placed on the parent (PRIMARY KEY or UNIQUE constraint) column. You cannot place the outer join operator (+) on both sides of the equal sign (=).

It is recommended that Oracle out join clauses using the += and =+ operators be changed to use the following SQL-92 join syntax.

Join operation	Description
CROSS JOIN	Specifies that the cross product of two tables be returned. This 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	Specifies that all inner rows be returned. Any unmatched rows are discarded. This is identical to a standard Oracle table join.
LEFT [OUTER]	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]	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]	Specifies the row be included in the result set and its output columns that correspond to the other table be set to NULL if a row from either table does not match the selection criteria. This would be the same as placing the Oracle outer join operator on both sides of the equal sign (for example, col1(+) = col2(+)), which is not allowed.

The code examples return listings 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). The syntax differs between Oracle and SQL Server.

Oracle	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

See Also 

In Other Volumes 

"Join Fundamentals" in Microsoft SQL Server Database Developer's Companion 

Using SELECT Statements as Table Names

Microsoft SQL Server and Oracle support the use of SELECT statements as table names when performing queries. However, one important difference is that an alias must be provided with the SQL Server SELECT statement, while an alias is optional with Oracle.

Oracle	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

Migrating Functions from Oracle to SQL Server

The following tables show the relationship between Oracle and Microsoft SQL Server functions.

Note Although the names may appear to be the same, functions may vary in numbers and types of arguments.

See Also 

In Other Volumes 

"Functions" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Migrating Number/Mathematical Functions from Oracle to SQL Server

This table shows the equivalent Microsoft SQL Server function for each Oracle function.

Function	Oracle	SQL Server
Absolute value	ABS	ABS
Arc cosine	ACOS	ACOS
Arc sine	ASIN	ASIN
Arc tangent of n	ATAN	ATAN
Arc tangent of n and m	ATAN2	ATN2
Smallest integer >= value	CEIL	CEILING
Cosine	COS	COS
Hyperbolic cosine	COSH	COT
Exponential value	EXP	EXP
Largest integer <= value	FLOOR	FLOOR
Natural logarithm (base 2)	LOG(2, n )	LOG( n )
Logarithm, base 10	LOG(10, n )	LOG10( n )
Logarithm, other base	LOG( m , n )	N/A
Modulus (remainder)	MOD	% (modulus operator)
Power	POWER	POWER
Round	ROUND	ROUND
Sign of number	SIGN	SIGN
Sine	SIN	SIN
Hyperbolic sine	SINH	N/A
Square root	SQRT	SQRT
Tangent	TAN	TAN
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

Migrating Character Functions from Oracle to SQL Server

This table shows the equivalent Microsoft SQL Server function for each Oracle function.

Function	Oracle	SQL Server
Convert character to ASCII	ASCII	ASCII
String concatenate	CONCAT( expression1 , espression2 )	( expression1 + expression2 )
Convert ASCII to character	CHR	CHAR
Greatest character string in list	GREATEST	N/A
Capitalize first letter of each word in string	INITCAP	N/A
Returns starting point of character in character string (from left)	INSTR	CHARINDEX
Starting point of pattern in character string	INSTR	PATINDEX
Returns starting point, in bytes, of character or pattern in character string	INSTRB	N/A
Least character string in list	LEAST	N/A
Length of character string	LENGTH	DATALENGTH
Length of character string in bytes	LENGTHB	N/A
Convert characters to lowercase	LOWER	LOWER
Pad left side of character string	LPAD	N/A
Remove leading blanks	LTRIM(char)	LTRIM(char)
Remove other leading characters	LTRIM( char , set )	N/A
Capitalize first letter of each word in an NLS string	NLS_INITCAP	N/A
Convert characters to lowercase in an NLS string	NLS_LOWER	N/A
Convert characters to lowercase in an NLS string	NLS_UPPER	N/A
Returns the string of bytes used to sort a character	NLS_SORT	N/A
Convert string if NULL	NVL	ISNULL
Replace characters	REPLACE	STUFF
Pad right side of character string	RPAD	N/A
Remove trailing blanks	RTRIM( char )	RTRIM
Remove other trailing characters	RTRIM( char , set )	N/A
Phonetic representation of character string	SOUNDEX	SOUNDEX
Character data converted from numeric data	TO_CHAR	STR
Returns a substring of a character string	SUBSTR	SUBSTRING
Returns a substring of a character string, the range to search specified in bytes	SUBSTRB	N/A
Translate character string	TRANSLATE	N/A
Convert characters to uppercase	UPPER	UPPER

Migrating Date Functions from Oracle to SQL Server

This table shows the equivalent Microsoft SQL Server function for each Oracle function.

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

Migrating Conversion Functions from Oracle to SQL Server

The Microsoft SQL Server CAST and CONVERT functions are multiple purpose conversion functions. They convert an expression of one data type to another data type. In addition, CONVERT supports a variety of special date formats.

CAST (expression AS datatype)

CONVERT (data_type[(length)], expression [, style])

They perform the same operations as a number of Oracle functions.

Conversion	Oracle	SQL Server
Character to number	TO_NUMBER(expression)	CAST(expression AS decimal) CAST(expression AS integer)
Number to character	TO_CHAR(expression)	CAST(expression AS char) CAST(expression AS varchar) CAST(expression AS nvarchar)
Character to date	TO_DATE('04-JUL-97') TO_DATE('04-JUL-1997', 'dd-mon-yyyy') TO_DATE('July 4, 1997', 'Month dd, yyyy')	CAST(expression AS datetime)
Date to character	TO_CHAR(expression) TO_CHAR(expression, 'dd mon yyyy') TO_CHAR(expression, 'mm/dd/yyyy')	CONVERT(char, expression) CONVERT(char, expression, 106) CONVERT(char, expression, 101)
Hex to binary	HEXTORAW(expression)	CAST(expression AS binary)
Binary to hex	RAWTOHEX(expression)	CONVERT(binary_expression AS char)
Character to ROWID	CHARTOROWID	N/A
ROWID to character	ROWIDTOCHAR	N/A
Convert one character set to another	CONVERT	N/A
Single-byte characters to multibyte	TO_MULTI_BYTE	N/A
Multibyte characters to single-byte	TO_SINGLE_BYTE	N/A
Character set to national character set or vice versa	TRANSLATE USING	N/A

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 CAST and CONVERT functions convert standard date formats automatically, without the need for a format model.

When converting from a date to a character string, the default output for the CONVERT function is dd mon yyyy hh:mm:ss:mmm(24h). A numeric style code is used to format the output to other types of date format models.

See Also 

In Other Volumes 

"CAST and CONVERT" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Conversion Functions" in Microsoft SQL Server Database Developer's Companion 

Migrating Other Row-level Functions from Oracle to SQL Server

This table shows the equivalent Microsoft SQL Server function for each Oracle function.

Function	Oracle	SQL Server
Return first nonnull 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	USER	USER
User environment (audit trail)	USERENV	N/A
Level in CONNECT BY clause	LEVEL	N/A

Migrating Aggregate Functions from Oracle to SQL Server

This table shows the equivalent Microsoft SQL Server function for each Oracle function.

Function	Oracle	SQL Server
Average	AVG	AVG
Count	COUNT	COUNT
Maximum	MAX	MAX
Minimum	MIN	MIN
Standard deviation	STDDEV	N/A
Summation	SUM	SUM
Variance	VARIANCE	N/A

Migrating DECODE Functions from Oracle to SQL Server

Use the CASE expression to replace the DECODE statement in your application code. This table shows the syntax for each statement.

Oracle	SQL Server
DECODE (test_value, expression1, value1 [[,expression2, value2] [...]] [,default_value] )	CASE test_value WHEN expression1 THEN value1 [[WHEN expression2 THEN value2] [...]] [ELSE default_value] END

Both the DECODE statement and the 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 following table shows an example of a converted DECODE statement.

Oracle	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

The CASE expression supports the use of SELECT statements for performing Boolean tests, which the DECODE statement does not allow.

See Also 

In Other Volumes 

"CASE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Migrating User-defined Functions from Oracle to SQL Server

Microsoft SQL Server does not support the use of user-defined PL/SQL functions that can be called from SQL statements. This functionality can often be achieved in other ways.

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

Oracle	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;	No CREATE FUNCTION syntax is required; use CREATE PROCEDURE syntax.

Migrating Operators from Oracle to SQL Server

Oracle and Microsoft SQL Server operators are nearly identical.

Arithmetic Operators 

Operator	Oracle	SQL Server
Positive	+	+
Negative	-	-
Addition	+	+
Subtraction	-	-
Multiplication	*	*
Division	/	/

Character Operators 

Operator	Oracle	SQL Server
Concatenation	||	+

Comparison Operators 

Operator	Oracle	SQL Server
Equal to	=	=
Greater than	>	>
Less than	<	<
Greater than or equal to	>=	>=
Less than or equal to	<=	<=
Not equal to	!=, ^=, < >	!=, <>
In any member in set	IN	IN
Not in any member in set	NOT IN	NOT IN
Any value in set	ANY, SOME	ANY, SOME
All values in set	ALL	ALL
Like pattern	LIKE	LIKE
Not like pattern	NOT LIKE	NOT LIKE
Value between	BETWEEN	BETWEEN
Value not between	NOT BETWEEN	NOT BETWEEN
Value exists	EXISTS	EXISTS
Value does not exist	NOT EXISTS	NOT EXISTS
Value {is | is not} NULL	IS NULL, IS NOT NULL	IS NULL, IS NOT NULL

Logical Operators 

Operator	Oracle	SQL Server
Negation	NOT	NOT
Conjunction	AND	AND
Disjunction	OR	OR

Set Operators 

Operator	Oracle	SQL Server
Return multiple result sets	UNION	UNION
Return multiple result sets, including duplicates	UNION ALL	UNION ALL
Return intersection of two result sets	INTERSECT	EXISTS
Return difference between two results sets	MINUS	NOT EXISTS

Other Operators 

Operator	Oracle	SQL Server
Outer join	(+)	SQL-92 join syntax
Determine direction of hierarchical query (used with CONNECT BY)	PRIOR	N/A

See Also 

In This Volume 

Migrating Join Clauses from Oracle to SQL Server

In Other Volumes 

"Comparison Operators" in Microsoft SQL Server Database Developer's Companion 

"Using EXISTS and NOT EXISTS to Find Intersection and Difference" in Microsoft SQL Server Database Developer's Companion 

Migrating Control-of-Flow Language from Oracle to SQL Server

The control-of-flow language is similar between PL/SQL and Transact-SQL. The control-of-flow language controls the flow of execution of SQL statements, statement blocks, and stored procedures.

See Also 

In Other Volumes 

"Control-of-Flow" in Microsoft SQL Server Database Developer's Companion 

Migrating Control-of-Flow Keywords from Oracle to SQL Server

These are the keywords supported by Oracle and Microsoft SQL Server.

Statement	Oracle	SQL Server
Declare variables	DECLARE	DECLARE
Statement block	BEGIN...END;	BEGIN...END
Conditional processing	IF...THEN, ELSIF...THEN, ELSE ENDIF;	IF...[BEGIN...END] ELSE [BEGIN...END] CASE expression
Unconditional exit	RETURN	RETURN
Wait for a specified interval	N/A	WAITFOR
Loop control	WHILE...END LOOP; LABEL...GOTO LABEL; FOR...END LOOP; LOOP...END LOOP;	WHILE... LABEL...GOTO LABEL
Program comments	/* ... */, --	/* ... */, --
Print output	DBMS_OUTPUT.PUT_LINE	PRINT
Raise program error	RAISE_APPLICATION_ERROR	RAISERROR
Execute program	EXECUTE	EXECUTE
Statement terminator	Semicolon (;)	N/A

Declaring Variables in Oracle and SQL Server

Transact-SQL and PL/SQL variables are created using the DECLARE keyword. Transact-SQL variables must be identified with the at sign (@) and, like PL/SQL variables, are initialized to a null value when they are created.

Oracle	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 statement. The NOT NULL and CONSTANT keywords cannot be used.

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.

See Also 

In Other Volumes 

"DECLARE @local_variable" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Transact-SQL Variables" in Microsoft SQL Server Database Developer's Companion 

Assigning Variables in Oracle and SQL Server

Assigning values to variables in PL/SQL and Transact-SQL is similar.

Oracle	SQL Server
variable_name := value	SET @variable_name = value
SELECT expression[, ...n] INTO variable_name[, ...n] FROM...	SELECT {@variable_name = expression}[, ...n] FROM...
FETCH cursor_name INTO variable_name[, ...n]	FETCH [fetch option] FROM cursor_name INTO variable_name[, ...n]

As in PL/SQL, the SELECT statement in Transact-SQL can be used to assign literal values, functions, or column values from a single row.

In PL/SQL, the FETCH...INTO statement always returns the next row in the cursor. In Transact-SQL, you can move back and forth through the cursor, or jump to a particular position in the cursor by including a fetch option. The default is FETCH NEXT, which works like the PL/SQL FETCH statement.

Microsoft SQL Server does not offer a constant declaration, and you cannot assign variables in the DECLARE statement.

See Also 

In Other Volumes 

"FETCH" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"SET @local_variable" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"SELECT @local_variable" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Transact-SQL Variables" in Microsoft SQL Server Database Developer's Companion 

Migrating Statement Blocks from Oracle to SQL Server

PL/SQL and Transact-SQL support the use of the 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 for IF statements and WHILE loops if more than one statement is executed.

Oracle	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 OPTIONAL SYNTAX

See Also 

In Other Volumes 

"BEGIN...END" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Using BEGIN...END" in Microsoft SQL Server Database Developer's Companion 

Conditional Processing in Oracle and SQL Server

The Transact-SQL conditional statement includes the IF and ELSE terminology. There is no ELSIF as in PL/SQL; however, multiple IF statements can be embedded within each other to achieve the same effect. For extensive conditional tests, it is recommend that you use the CASE statement.

Oracle	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

See Also 

In Other Volumes 

"CASE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Using CASE" in Microsoft SQL Server Database Developer's Companion 

"IF...ELSE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Using IF...ELSE" in Microsoft SQL Server Database Developer's Companion 

Repeated Statement Execution (Looping) in Oracle and SQL Server

Transact-SQL offers the WHILE loop. The PL/SQL unconditional LOOP and FOR LOOP statements are not supported. The GOTO statement can also be used 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	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. The CONTINUE keyword causes the WHILE loop to restart, skipping any statements that follow.

See Also 

In Other Volumes 

"BREAK" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Using WHILE...BREAK or CONTINUE" in Microsoft SQL Server Database Developer's Companion 

"CONTINUE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"WHILE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Printing Messages in Oracle and SQL Server

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

See Also 

In Other Volumes 

"PRINT" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Using PRINT" in Microsoft SQL Server Database Developer's Companion 

Raising Program Errors in Oracle and SQL Server

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 very similar in function to the PL/SQL raise_application_error exception handler:

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

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. After it is defined, this message is sent back to the client as a server error message.

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

Migrating Cursors from Oracle to SQL Server

Oracle requires using cursors with SELECT statements, regardless of the number of rows requested from the database. In Microsoft SQL Server, a SELECT statement that is not disclosed on 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 using cursors in Transact-SQL batches or stored procedures, SQL-92 standard SQL syntax has been added for declaring, opening, and fetching from cursors as well as positioned updates and deletes. When using cursors from a DB-Library or ODBC program, 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 is used only to return a set of rows to the client application, use a noncursor SELECT statement in Transact-SQL to a return 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.

The following table shows the syntax for using cursors.

Operation	Oracle	SQL Server
Declaring a cursor	CURSOR cursor_name [( cursor_parameter ( s ))] IS select_statement ;	DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement [FOR {READ ONLY | UPDATE [OF column_list]}]
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

The Transact-SQL DECLARE CURSOR statement is more robust than the PL/SQL statement, and has many additional capabilities. Although it does not support the use of cursor arguments, it can include local variables. The values of these local variables are used in the cursor at open time.

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. Microsoft SQL Server cursors can scroll forward and backward using various fetch options when the cursors are declared using the SCROLL option.

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

SQL Server does not support the Oracle cursor FOR loop syntax.

CURRENT OF Clause

The syntax for updates and deletes using the CURRENT OF clause is the same in both PL/SQL and Transact-SQL. This clause works the same way regardless of which database management system (DBMS) you are using. 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

This example compares the cursor statements required to achieve the same program result in both PL/SQL and Transact-SQL.

Oracle	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

Migrating from Access

There are many architectural differences between Microsoft Access and Microsoft SQL Server. Access is designed to handle a relatively small number of users accessing a relatively small amount of data. SQL Server is designed to handle anything from the smallest desktop database to the largest enterprise situation. SQL Server supports a greater number of features than Access supports.

Before you begin the migration process, familiarize yourself with SQL Server so you are prepared to reconcile the differences between it and Access.

Current backups of your data are always important, but they are essential when you make changes to your database. The migrating process does not delete any data; however, you can expect to delete the local Access tables in the .mdb file after the process is complete and has been tested. Also, you may need to consult a working copy of your original Access application in the future.

The size of your database will not change significantly when it is transferred to SQL Server. It might be smaller than your Access .mdb file because you are moving the tables only, not forms, reports, or Microsoft Visual Basic for Application modules.

Migrating Database Objects from Access to SQL Server

In Microsoft Access, most database objects are created interactively using a design wizard. When migrating to Microsoft SQL Server, these objects must be re-created in SQL Server by writing an object creation script or working interactively with SQL Server Enterprise Manager.

If you created any Access database objects using a data definition query, the query can be modified to meet SQL Server specifications. Run the modified script through SQL Server Query Analyzer to create the new database objects.

Referencing Objects in Access and SQL Server

Review the names of your tables and columns. Objects in Microsoft Access can contain spaces, for example, January Orders. Objects in Microsoft SQL Server cannot contain spaces unless it is a delimited identifier. You may want to replace the space with an underscore character or use capitalization to separate the portions of the names, for example, JanuaryOrders.

Access	SQL Server
Identifier names can include any combination of letters, numbers, and special characters except: a period (.), an exclamation point (!), an accent grave (`), and brackets ([ ]).	Identifier names must begin with a letter as defined by the Unicode standard or a character _ (underscore), @ (at sign), or # (number sign), and contain letters as defined by the Unicode standard, decimal numbers from either Basic Latin or other national scripts, or the characters _, @, $ (dollar sign), and #.
Identifier names can be from 1 through 64 characters in length.	Identifier names can be from 1 through 128 characters in length.

In Access, object names are case-sensitive. In SQL Server, object names can be case-sensitive, depending on the character sort order that is installed. The sort order is chosen in SQL Server Setup during installation. The default sort order in SQL Server is dictionary order, case-insensitive.

See Also 

In Other Volumes 

"Using Identifiers" in Microsoft SQL Server Database Developer's Companion 

Altering Tables

In Microsoft Access, you can make changes to the table design after it has been implemented. In many cases, Access does not modify existing tables. Instead, it creates new tables according to your specifications, and then moves data into it. SQL Server makes changes to tables in the same way; however, in some cases you must guide it through the steps.

See Also 

In Other Volumes 

"Creating and Modifying a Table" in Microsoft SQL Server Database Developer's Companion 

Table Relationships

The rules for defining foreign keys are similar in Microsoft Access and Microsoft SQL Server. The number of columns and data types 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.

The Access cascading updates and deletes are not available with SQL Server. This option is used in situations where both the parent and child values are deleted when a parent row is deleted. In SQL Server, this option must be enforced with table triggers. For more information, see "Nested Triggers" in Microsoft SQL Server Database Developer's Companion.

See Also 

In Other Volumes 

"Foreign Key Constraints" in Microsoft SQL Server Database Developer's Companion 

Migrating Business Rules from Access to SQL Server

All of your business rules should be moved to the server. Primary and foreign key constraints are enforced through Declarative Referential Integrity or triggers. Unique constraints are enforced by a unique index. In Microsoft Access, set the required property of a column to Yes to prevent a null value. In Microsoft SQL Server, define a field as NOT NULL when the table is created by or uses a trigger.

The concepts of rules and defaults are the same in SQL Server as in Access, but are implemented differently. In SQL Server, rules and defaults are created apart from the table and then bound to it. One rule or default can be bound to many columns and many tables. In Access, rules and defaults do not have to be placed in the Access table. You can place them on fields in forms or perform more complex validations in code modules. However, if the rule is in the form, a user who enters a value into the table directly, without using the form, bypasses the validation rule and can enter invalid data. Make sure you identify all of the rules and defaults that should be migrated to SQL Server.

Migrating Validation Rules from Access to SQL Server

Validation rules are checked in almost all cases. If you have a validation rule (a rule in Microsoft SQL Server) that says the Qty field must be greater than 5 or the DeliveryDate field must be later than the OrderDate field, you should keep it. Use either a trigger or a CHECK constraint in SQL Server to enforce field-level validation. The trigger offers the benefit of customizable error messages for violating the validation rules.

See Also 

In Other Volumes 

"CHECK Constraints" in Microsoft SQL Server Database Developer's Companion 

Migrating Indexes from Access to SQL Server

Nonclustered Indexes 

A nonclustered index is an index that is physically separated from a table. Each is considered a separate database object. Because these objects are separate, the physical order of the table rows is not the same as their indexed order. Nonclustered indexes resemble Microsoft Access indexes.

Clustered Indexes 

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 type of architecture permits only one clustered index per table.

As a rule, you should always have a clustered index defined on a table. Doing so improves Microsoft SQL Server performance and space management. If you do not know the query or update patterns for a given table, you can default to using the clustered index on the primary key. Later analysis of application bottlenecks may lead you to change the clustered index on specific tables to support frequently used range queries better.

Compacting the Database 

The Access utility for compacting databases does not exist for SQL Server. Instead, SQL Server rebuilds indexes. As records are deleted from a table in SQL Server, gaps in the table and its indexes remain untouched. When an entire datapage is emptied, it is reassigned. However, as long as one record remains, the datapage remains untouched, even if it is 90 percent empty. When you rebuild an index, these data pages are rewritten and filled as specified by the fill factor. (If no clustered index exists on the table, create one, and then drop it to achieve the same effect.) To compact a database fully, rebuild every index on every table. Of course, tables that experience little activity will benefit only slightly. Although it requires some work to set up such a maintenance plan, this system is easy to customize so that you can rebuild only selected tables.

See Also 

In Other Volumes 

"Indexes" in Microsoft SQL Server Database Developer's Companion 

"Using Nonclustered Indexes" in Microsoft SQL Server Database Developer's Companion 

"Using Clustered Indexes" in Microsoft SQL Server Database Developer's Companion 

Data Types in Access and SQL Server

The available data types are different in Microsoft Access and Microsoft SQL Server. SQL Server bases its data types on the C programming language; Access bases its data types on Microsoft Visual Basic. The data types are converted as described in the following table.

Access	SQL Server
Text	Varchar
Memo	Text
Byte	Smallint
Integer	Smallint
Long Integer	Int
Single	Real
Double	Float
Replication ID	Varbinary
Date/Time	Datetime
Currency	Money
Autonumber (Long Integer)	Int (Identity)
Yes/No	Bit
OLE Object	Image

Be careful when selecting which data types to use in SQL Server. You can change a field's data type in Access; however, it is not possible to change a data type in SQL Server. A new table must be created before the data can be transferred to that table.

Timestamps 

The SQL Server timestamp data type has no counterpart in Access. In spite of its name, it is neither a time nor a date, nor is it some encoded representation of a time or date. A timestamp is a binary number column that is updated automatically every time a row is inserted or updated. This permits the client program to confirm whether values have changed since it last checked the record. A timestamp column enables BROWSE-mode updates and makes cursor update operations more efficient.

The timestamp field indicates only that a record was changed, not when it was changed. You cannot set the timestamp column to any specific value. To record the times data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers to update the values automatically when any modification takes place.

AutoNumber and Identity Fields 

SQL Server has an automatically incrementing field, called an Identity field, similar to the Access AutoNumber field. When migrating data from an AutoNumber field to an Identity field, turn off the functionality of the field with the IDENTITY_INSERT option, copy the existing AutoNumber values into the table, and then turn the IDENTITY_INSERT option back on. This preserves the original AutoNumber values from your Access table and begins future records with the highest number in your table, incrementing by one.

Changing True from -1 to +1 

Access stores either a zero (0) or a negative one (-1) in the Yes/No data type. Access interprets 0 as 0, No, or False, and interprets any nonzero value as –1, Yes, or True.

A bit field stored in SQL Server that is selected in ISQL/w appears as either a zero (0) or a positive one (1), where 0 represents false and 1 represents true.

See Also 

In Other Volumes 

"Data Types" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"timestamp" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"IDENTITY (Property)" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Transactions, Locking, and Concurrency in Access and SQL Server

This topic explains how transactions are executed in both Microsoft Access and Microsoft SQL Server and discusses the difference between the locking process and concurrency issues in both database types.

Transactions in Access and SQL Server

Microsoft Access and Microsoft SQL Server share two types of transactions.

• Explicit transactions

  Explicitly start a transaction by issuing a BEGIN TRANSACTION statement. 

• Autocommit transactions

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

SQL Server also offers a third type of transaction.

• Implicit transactions

  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 you are issuing a script through SQL Server Query Analyzer or other query tools, you can either include the explicit BEGIN TRANSACTION statement, or start the script with the SET IMPLICIT_TRANSACTIONS ON statement.

SQL Server also offers the SAVE TRANSACTION statement. It sets a savepoint in the transaction, allowing partial rollbacks.

See Also 

In Other Volumes 

"Autocommit Transactions" in Microsoft SQL Server Database Developer's Companion 

"Implicit Transactions" in Microsoft SQL Server Database Developer's Companion 

"SET IMPLICIT_TRANSACTIONS" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Explicit Transactions" in Microsoft SQL Server Database Developer's Companion 

"SAVE TRANSACTION" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Transactions" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Multiuser Locking

Microsoft Access offers two levels of locking: locked or unlocked. It uses the operating system to lock portions of the data to allow two or more users to have simultaneous locks to different areas of the file. However, the Access default is to open the .mdb file for exclusive use. In contrast, Microsoft SQL Server expects many simultaneous users and has more sophisticated locking strategies.

See Also 

In Other Volumes 

"Locking" in Microsoft SQL Server Database Developer's Companion 

Using the Transaction Log in SQL Server

Microsoft SQL Server has a feature common to most production scale databases that does not exist in Microsoft Access. The* *transaction log is a chronological record of all activity that occurs on the server. When a record is added to a table, a copy of the record is written to the transaction log. When a record is deleted, the transaction log records the data that was removed. Every time an existing record is changed, the transaction log records both the old value and the new value. The same details concerning the creation and deletion of tables, views, stored procedures, rules, defaults, and so on, are also stored. Every multistep transaction is recorded, along with whether it was committed or rolled back. Every entry to the transaction log contains a timestamp, an incrementing number to order all entries.

Truncate Log on Checkpoint 

Should you decide that you do not need a transaction log, you can set the trunc. log on chkpt. option. With trunc. log on chkpt., you can ignore maintenance of the transaction log, and your database behaves much like an Access database. SQL Server continues to use the transaction log and you continue to gain its benefits; however, less space and no maintenance are required.

Database Replication 

Database replication is also based on the transaction log. If you have marked a table for replication, it is recorded in the database transaction log. A special process called the log reader monitors the log and sends changes marked for replication to the distribution database.

See Also 

In Other Volumes 

"Transaction Logs" in Microsoft SQL Server Database Developer's Companion 

"Setting Database Options" in Microsoft SQL Server Database Developer's Companion 

Using Transactions in Access and SQL

Although the concept of transactions is the same in Microsoft Access and Microsoft SQL Server, the reason for using transactions varies. One reason to use transactions in Access is to keep a query plan in memory. If you want to make 10 updates to a table using the same format, after the first update is made, the subsequent nine updates wrapped in the same transaction use the same optimized query structure. In SQL Server, a stored procedure, even a temporary one, holds the query structure automatically in its procedure cache. More importantly, a single wrapped transaction can fail if two updates occur to the same record or on the same datapage.

Access and SQL Server also accommodate transactions to achieve atomicity, the linking together of two or more statements so that they either both succeed or both fail. The classic example uses a banking situation. If you are transferring money from a checking account to a savings account, you want both transactions to either succeed or fail. This avoids the problem of moving the money out of checking but failing to deposit it into savings.

Transaction Completion and Rollback 

With Access, if you experience a power failure, you may be left with an incomplete transaction upon restart. SQL Server's transaction log prevents this. Upon startup after a power failure, the transaction log rolls the database activity forward to a stable condition. Transactions, if completed in the log, are applied to the data pages. Incomplete transactions are rolled back. This is an advantage even if you do not use transactions in your code. Normal database activity produces transactions, such as page splits, that are required when a datapage is full. Such transactions are completed or rolled back, leaving your database in an internally consistent state.

Migrating SQL Language Support from Access to SQL Server

Browsing Tables in Access and SQL Server

It is common to browse entire tables in Microsoft Access. Due to the larger number of rows often found in a Microsoft SQL Server table, limit the result set returned from a query to those records you really need to see. For instance, instead of presenting a list of all your customers to your users, request the first few characters of the customer's name.

Batching Inserts

One of the most critical elements of a multiuser application is efficient use of the network. After you convert to client/server architecture, all processing is no longer performed on a local computer with Microsoft Access. The fastest client/server applications optimize for fewer network roundtrips.

Think of the network as a bottleneck that takes a performance hit every time it is used. Instead of updating record #1 and then updating record #2, send one message to the server that updates both records at the same time. For example, you need to change the discount rate of these three customers because of a new contract. Instead of:

UPDATE customer SET discount = 10 WHERE CustomerID = 5

and then

UPDATE customer SET discount = 10 WHERE CustomerID = 15

and then

UPDATE customer SET discount = 10 WHERE CustomerID = 72

look for opportunities to batch updates together. Use:

UPDATE customer SET discount = 10 WHERE CustomerID = 5 OR 15 OR 72

Now you have only one statement and one network hit. This query is three times faster than the first three queries.

If you always update two records as a set, batch them together. Instead of:

INSERT orders (ID, qty) VALUES ('AAA', 17)

and then

INSERT RunningTotal(ID,qty) VALUES ('AAA', qty + 17)

use a stored procedure to do both at the same time.

Use logic in the stored procedure on the server to update both tables. Here is a sample stored procedure:

CREATE PROC sp_NewOrder 
(@CustID Char(3), @QtyIn INT) 
AS 
INSERT orders (ID, qty) 
VALUES (@CustID, @QtyIn)

INSERT RunningTotal(ID,qty) 
VALUES (@CustID, @QtyIn)

When it is time to send the record, use a pass-through query:

sp_NewOrder('AAA',17)

Now you are sending 21 characters on one network trip instead of 79 characters on two network trips.

Outer Join Syntax

Microsoft Access supports right and left outer joins. Microsoft SQL Server supports these and other SQL-92 standard join operators.

Join operation	Description
CROSS JOIN	Specifies the cross product of two tables be returned. This returns the same rows as if no WHERE clause was specified in an old-style join. This type of join is called a Cartesian product in Access.
INNER	Specifies that all inner rows be returned. Any unmatched rows are discarded. This is identical to a standard Access table join.
LEFT [OUTER]	Specifies that all of the left table outer rows be returned, even if no column matches are found. This operates just like an Access left join.
RIGHT [OUTER]	Specifies that all of the right table outer rows be returned, even if no column matches are found. This operates just like an Access right join.
FULL [OUTER]	Specifies the row be included in the result set and its output columns that correspond to the other table be set to NULL if a row from either table does not match the selection criteria.

See Also 

In Other Volumes 

"Join Fundamentals" in Microsoft SQL Server Database Developer's Companion 

Migrating Top N Queries from Access to SQL Server

Microsoft SQL Server does not have the Microsoft Access syntax for a Top N or Top N Percent query. You can provide this functionality in SQL Server by ordering the output and returning the specified number of rows. The SET ROWCOUNT statement precedes the SELECT statement:

SET ROWCOUNT 5
SELECT qty, ord_date 
FROM sales
ORDER BY qty DESC

To calculate the percentage of the rows returned, declare a variable. Count the records, compute the number of records that you want, and then select the correct number of records. This query returns the top third of the records:

DECLARE @desiredrows int
SELECT @desiredrows = COUNT(*)/3 FROM sales
SET ROWCOUNT @desiredrows
SELECT qty, ord_date 
FROM sales
ORDER BY qty DESC

See Also 

In Other Volumes 

"SET ROWCOUNT" in Microsoft SQL Server Transact-SQL and Utilities Reference 

ANSI_DEFAULTS Setting in Access and SQL Server

In Microsoft SQL Server, when ANSI_DEFAULTS is set ON, these ANSI standards are activated:

• ANSI_NULLS 

• ANSI_NULL_DFLT_ON 

• CURSOR_CLOSE_ON_COMMIT 

• ANSI_PADDING 

• IMPLICIT_TRANSACTIONS 

• ANSI_WARNINGS 

• QUOTED_IDENTIFIER 

Queries written by Microsoft Access adhere to these standards. To maintain consistent behavior in queries after they have been migrated from Access to SQL Server, you may want to ensure that ANSI_DEFAULTS is set ON.

See Also 

In Other Volumes 

"SET ANSI_DEFAULTS" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Migrating Functions and Special Characters from Access to SQL Server

The tables in this section show the relationship between Microsoft Access and Microsoft SQL Server functions.

See Also 

In Other Volumes 

"Functions" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Migrating String Functions from Access to SQL Server

This table shows the Microsoft SQL Server equivalent function for each Microsoft Access function.

Access	SQL Server
chr$(x)	char(x)
asc(x)	ascii(x)
str$(x)	str(x)
space$( x)	space(x)
lcase$(x)	lower(x)
ucase$( x)	upper(x)
len(x)	datalength(x)
ltrim$( x)	ltrim(x)
rtrim$(x)	rtrim(x)
right$(x,y)	right(x,y)
mid$(x,y,z)	substring(x,y,z)

Migrating Conversion Functions from Access to SQL Server

This table shows the Microsoft SQL Server equivalent function for each Microsoft Access function.

Access	SQL Server
cint(x)	convert(smallint,x)
clng(x)	convert(int,x)
csng(x)	convert(real,x)
cdbl(x)	convert(float,x)
cstr(x)	convert(varchar,x)
ccur(x)	convert(money,x)
cvdate(x)	convert(datetime,x)

Migrating Date Functions from Access to SQL Server

This table shows the Microsoft SQL Server equivalent function for each Microsoft Access function.

Access	SQL Server
now(x)	getdate(x)
date(x )	convert(datetime,convert(varchar,getdate(x)))
year(x)	datepart(yy,x)
month(x)	datepart(mm,x)
day(x)	datepart(dd,x)
weekday(x)	datepart(dw,x)
hour(x)	datepart(hh,x)
minute(x)	datepart(mi,x)
second(x)	datepart(ss,x)
datepart("<Access datepart>", x)	datepart(<SQL Server datepart>, x)
dateadd("<Access datepart>", x, y)	dateadd(<SQL Server datepart>, x, y)
datediff("<Access datepart>", x, y)	datediff(<SQL Server datepart>, x, y)

Migrating Math Functions from Access to SQL Server

This table shows the Microsoft SQL Server equivalent function for each Microsoft Access function.

Access	SQL Server
int(x)	floor(x)
sgn(x)	sign(x)

Migrating Delimiters, Operators, Constants, and Wildcard Characters from Access to SQL Server

This table shows the Microsoft SQL Server equivalent for each Microsoft Access term.

Description	Access	SQL Server
Date delimiter	#	'
String delimiter	"	'
Mod operator	mod	%
Concatenation operator	&	+
Wildcard character	?	_
Wildcard character	*	%
Constant	Yes	1
Constant	On	1
Constant	True	1
Constant	No	0
Constant	Off	0
Constant	False	0