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 Customer 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."
Copyright 2000 by Infinite Software Solutions, Inc.
Trademark Information
|