Home

	Expand Search

Transact-SQL User's Guide

Chapter 11 Creating Indexes on Tables

Creating indexes

Creating indexes

The simplest form of the create index command is:

create index index_name
     on table_name (column_name) 

To create an index on the au_id column of the authors table, the command is:

create index au_id_ind 
on authors(au_id) 

The index name must conform to the rules for identifiers. The column and table name specify the column you want indexed and the table that contains it.

You cannot create indexes on columns with bit, text, or image datatypes.

You must be the owner of a table to create or drop an index. The owner of a table can create or drop an index at any time, whether or not there is data in the table. Indexes can be created on tables in another database by qualifying the table name.

create index syntax

The complete syntax of the create index command is:

create [unique] [clustered | nonclustered]
          index index_name 
     on [[database.]owner.] table_name (column_name          [, column_name]...) 
     [with {{fillfactor | max_rows_per_page} = x,
          ignore_dup_key, sorted_data,
          [ignore_dup_row | allow_dup_row]}] 
     [on segment_name] [with consumers = x]

The following sections explain the various options to this command.

The on segment _name extension to create index allows you to place your index on a segment that points to a specific database device or a collection of database devices. Before creating an index on a segment, see a System Administrator or the Database Owner for a list of segments that you can use. Certain segments may already be allocated to specific tables or indexes for performance reasons or for other considerations.

Indexing more than one column: composite indexes

You have to specify two or more column names to create a composite index on the combined values in all the specified columns.

Use composite indexes when two or more columns are best searched as a unit, for example, the first and last names of authors in the authors table. List the columns to be included in the composite index in sort-priority order, inside the parentheses after the table name, like this:

create index auth_name_ind
on authors(au_fname, au_lname)

The columns in a composite index do not have to be in the same order as the columns in the create table statement. For example, the order of au_lname and au_fname could be reversed in the preceding index creation statement.

You can specify up to 31 columns in a single composite index in Adaptive Server 11.9.2 and later. All the columns in a composite index must be in the same table. The maximum allowable size of the combined index values is 600 bytes. That is, the sum of the lengths of the columns that make up the composite index cannot exceed 600.

Using the unique option

A unique index permits no two rows to have the same index value, including NULL. The system checks for duplicate values when the index is created, if data already exists, and checks each time data is added or modified with an insert or update.

Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. For example, you would not want a unique index on a last_name column because there is likely to be more than one "Smith" or "Wong" in tables of even a few hundred rows.

On the other hand, a unique index on a column holding social security numbers is a good idea. Uniqueness is a characteristic of the data--each person has a different social security number. Furthermore, a unique index serves as an integrity check. For instance, a duplicate social security number probably reflects some kind of error in data entry or on the part of the government.

If you try to create a unique index on data that includes duplicate values, the command is aborted, and Adaptive Server displays an error message that gives the first duplicate. You cannot create a unique index on a column that contains null values in more than one row; these are treated as duplicate values for indexing purposes.

If you try to change data on which there is a unique index, the results depend on whether you have used the ignore_dup_key option. See "Using the ignore_dup_key option" for more information.

You can use the unique keyword on composite indexes.

Including IDENTITY columns in nonunique indexes

The identity in nonunique index database option automatically includes an IDENTITY column in a table's index keys so that all indexes created on the table are unique. This option makes logically nonunique indexes internally unique and allows them to process updatable cursors and isolation level 0 reads.

To enable identity in nonunique indexes, enter:

sp_dboption pubs2, "identity in nonunique index", true

The table must already have an IDENTITY column for the identity in nonunique index database option to work, either from a create table statement or by setting the auto identity database option to true before creating the table.

Use identity in nonunique index if you plan to use cursors and isolation level 0 reads on tables with nonunique indexes. A unique index ensures that the cursor is positioned at the correct row the next time a fetch is performed on that cursor.

For example, after setting identity in nonunique index and auto identity to true, suppose you create the following table, which has no indexes:

create table title_prices
(title varchar(80)  not null,
price  money          null)

sp_help shows that the table contains an IDENTITY column, SYB_IDENTITY_COL, which is automatically created by the auto identity database option. If you create an index on the title column, use sp_helpindex to verify that the index automatically includes the IDENTITY column.

Ascending and descending index-column values

You can use the asc (ascending) and desc (descending) keywords to assign a sort order to each column in an index. By default, sort order is ascending.

Creating indexes so that columns are in the same order specified in the order by clauses of queries eliminates sorting the columns during query processing. The following example creates an index on the Orders table. The index has two columns, the first is customer_ID, in ascending order, the second is date, in descending order, so that the most recent orders are listed first:

create index nonclustered cust_order_date
     on Orders
     (customer_ID asc,
     date desc)

Using fillfactor, max_rows_per_page, and reservepagegap

fillfactor, max_rows_per_page, and reservepagegap are space-management properties that apply to tables and indexes and affect the way physical pages are filled with data. For a detailed discussion of setting space-management properties for indexes, see create index in the Reference Manual. Table 11-1 summarizes information about the space-management properties for indexes.

This statement sets the fillfactor for an index to 65% and sets the reservepagegap to one empty page for each extent allocated:

create index postalcode_ind2 
     on authors (postalcode) 
     with fillfactor = 10, reservepagegap = 8