DevGuru Jet SQL CREATE INDEX Statement

Quick References

Features

Partners


			STATEMENT: CREATE INDEX 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. The following example creates a simple index on the Name field of the Customers table: CREATE INDEX CustomerIndex ON Customers (Name); 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. CREATE INDEX CustomerIndex ON Customers (Name DESC, City); If you want to prohibit duplicate values in the indexed field or fields, you can use the reserved word UNIQUE: CREATE UNIQUE INDEX CustomerIndex ON Customers (Name); 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): CREATE UNIQUE INDEX CustomerIndex ON Customers (Name) WITH DISALLOW NULL; With the IGNORE NULL option you can exclude records with NULL values in the indexed field(s) from the index: CREATE UNIQUE INDEX CustomerIndex ON Customers (Name) WITH IGNORE NULL; 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): CREATE INDEX CustomerIndex ON Customers (CustomerID) WITH PRIMARY; Note that you cannot use the reserved word PRIMARY to create an index on a table that already has a primary key. 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: CREATE UNIQUE INDEX OrderIndex ON OrderDetailsODBC (OrderID); 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."