Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links
Manage Tables & User Access with
SQL Table Management Commands
Alter Table DDL Language Reference








Create Table
Create Index
Alter Table

 

Microsoft Office:
  MS Access 2003
  Access 2007
  Access 2010
  Access 2013


Create Index Statement Example

To create an index get into the create new query wizard and then close the Show Table dialog box without specifying a table or query. Change to SQL view and type the index creation DDL statement.





We have expert enterprise-level database development capabilities for cloud/web-based databases through our partnership with IT Impact.



The following example creates a non-unique index on the field emp_phone in the Employees table.

CREATE INDEX Emp_Phone_IDX ON Employees (emp_phone);

Now let's drop (delete) this index so we can show additional options for the Create Index statement:

DROP INDEX Emp_Phone_IDX on Employees;

Here's an example to create a Unique index:

CREATE UNIQUE INDEX Emp_Phone_IDX ON Employees (emp_phone);

There are several other options to the create index statement - we have shown the common options.

One powerful feature of the create index statement is to create an index on an ODBC linked table.  This is usually referred to as a pseudo index and can significantly improve the performance of slow ODBC queries by adding an index on field references in the right side of where clauses in queries.

Here is the syntax for the create index statement from Microsoft Access 2003 help:

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

The CREATE INDEX statement has these parts:

Part Description
index The name of the index to be created.
table The name of the existing table that will contain the index.
field The name of the field or fields to be indexed. To create a single-field index, list the field name in parentheses following the table name. To create a multiple-field index, list the name of each field to be included in the index. To create descending indexes, use the DESC reserved word; otherwise, indexes are assumed to be ascending.







Contact Information

Copyright 2000-2017 Blue Claw Database Design