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] |
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.
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.
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.
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.
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.
| Fill Factor | Using Nonclustered Indexes |
| Indexes | Using Clustered Indexes |