CREATE INDEX Statement Example (MDB)

To try the following examples in Microsoft Access, first create a new query in the Northwind sample database. Close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.

Warning   These examples makes changes to the Northwind sample database. Before beginning, you may wish to make a backup copy of the sample database.

The next example creates an index consisting of the fields HomePhone and Extension in the Employees table:

CREATE INDEX NewIndex ON Employees (HomePhone, _
    Extension);

The following example creates an index on the Customers table with the CustomerID field. No two records can have the same data in the CustomerID field, and no Null values are allowed.

CREATE UNIQUE INDEX CustID ON Customers (CustomerID) _
    WITH DISALLOW NULL;

To delete the index created in the preceding example, you must use the DROP statement. You can't delete the index from table Design view unless you also delete the relationship on the Customers table. The following statement deletes the new index by using the DROP statement:

DROP INDEX CustID ON Customers;

The following example creates an index on an ODBC linked table. The table's remote database is unaware of and unaffected by the new index. The following example won't work if you simply paste it into Microsoft Access; you first need to create an ODBC linked table called OrderDetailsODBC.

CREATE UNIQUE INDEX OrderID ON OrderDetailsODBC _
    (OrderID);