Creating an Index

Syntax:

CREATE [ UNIQUE ] INDEX index ON table (field [ASC|DESC][, field [ASC|DESC], ...]) [WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

The CREATE INDEX statement is used to index one or more fields in an existing table.

You can do this by giving the index a name, and by stating the table and field(s) to which the index will apply.

Examples

Code:

CREATE INDEX CustomerIndex ON Customers (Name);

Explanation:

This example creates a simple index on the Name field of the Customers table:

Language(s): MS SQL Server

Code:

CREATE INDEX CustomerIndex ON Customers (Name DESC, City);

Explanation:

By default, the values in a field are indexed in ascending order, but if you want to index them in descending order, you can add the reserved word DESC. You can also index more than one field simply by listing the fields within parentheses.

Language(s): MS SQL Server

Code:

CREATE UNIQUE INDEX CustomerIndex ON Customers (Name);

Explanation:

If you want to prohibit duplicate values in the indexed field or fields, you can use the reserved word UNIQUE.

Code:

CREATE UNIQUE INDEX CustomerIndex ON Customers (Name) WITH DISALLOW NULL;

Explanation:

The optional WITH clause allows you to enforce further data validation rules by using three options: With the DISALLOW NULL option you can prohibit NULL entries in the indexed field(s).

Language(s): MS SQL Server

Code:

CREATE UNIQUE INDEX CustomerIndex ON Customers (Name) WITH IGNORE NULL;

Explanation:

With the IGNORE NULL option you can exclude records with NULL values in the indexed field(s) from the index.

Language(s): MS SQL Server

Code:

CREATE UNIQUE INDEX OrderIndex ON OrderDetailsODBC (OrderID);

Explanation:

You can also use CREATE INDEX to create a pseudo index on a linked table in an ODBC data source, such as SQL SERVER Note however, that this will only work if the table does not already have an index. You do not need permission or access to the remote server to create the pseudo index, and the remote database will be unaware of and unaffected by the operation.

Note:
Microsoft warns, "The Microsoft Jet database engine doesn't support the use of any DDL statements with databases produced by any other database engine. Use the DAO (Data Access Objects) Create methods instead."

Language(s): MS SQL Server

Code:

CREATE INDEX CustomerIndex ON Customers (CustomerID) WITH PRIMARY;

Explanation:

While the PRIMARY option allows you to designate which indexed field or fields to be the primary key (since primary keys are always unique, there's no need to include the reserved word UNIQUE).

Note that you cannot use the reserved word PRIMARY to create an index on a table that already has a primary key.

Language(s): MS SQL Server