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

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.

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

This table compares replication terms in Oracle and 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.

This table compares basic statistics for Oracle and SQL Server.

See Also 

In Other Volumes 

"Maximum Capacity Specifications" in Microsoft SQL Server Introduction 

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

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 

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.

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.

Creating Tables Using SELECT Statements 

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

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 

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.

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.

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.

This table shows the CREATE INDEX syntax.

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 

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.

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 

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

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.

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.

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.

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

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.

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 

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.

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.

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.

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.

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.

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. 

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

See Also 

In Other Volumes 

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

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

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

See Also 

In Other Volumes 

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

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

See Also 

In Other Volumes 

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

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.

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 

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.

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 

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

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.

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.

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.

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.

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.

See Also 

In Other Volumes 

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

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.

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 

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

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

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

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.

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 

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

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

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

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.

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 

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 and Microsoft SQL Server operators are nearly identical.

Arithmetic Operators 

Character Operators 

Comparison Operators 

Logical Operators 

Set Operators 

Other Operators 

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 

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 

These are the keywords supported by Oracle and Microsoft 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.

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 values to variables in PL/SQL and Transact-SQL is similar.

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 

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.

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 

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.

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 

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.

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 

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 

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

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.

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.

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.

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.

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.

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

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.

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.

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.

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 

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 

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 

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.

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 

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 

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.

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 

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.

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 

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 

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 

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.

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.

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.

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

See Also 

In Other Volumes 

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

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 

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 

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 

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

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

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

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

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