Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 11 Creating Indexes on Tables

Creating indexes

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.


	Expand Search