INDEXES:
Indexes provide speedy access to specific rows of a table based on the values stored in the specific columns.The command CREATE INDEX creates an index on a given table that either changes the physical ordering of the table or provides the optimizer with a logical ordering of the table to increase efficiency for queries. The syntax for this command is
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON [[database.]owner.]table_name (column_name [, column_name]...)[WITH [FILLFACTOR = x] [[,] IGNORE_DUP_KEY] [[,] {SORTED_DATA | SORTED_DATA_REORG}] [[,] {IGNORE_DUP_ROW | ALLOW_DUP_ROW}]][ON segment_name]
where
UNIQUE:Specifies to create a unique index (one in which no two rows are permitted to have the same index value). The system checks for duplicate values when the index is created (if data already exists) and checks each time data is added with an INSERT or UPDATE statement. If there are duplicate key values, the statement is canceled and an error message giving the first duplicate is returned. You cannot create a unique index on a single column or multiple columns (composite index) where the complete key (all columns of that key) is NULL in more than one row; these are treated as duplicate values for indexing purposes.
CLUSTERED:Creates an object where the physical order of rows is the same as the indexed order of the rows, and the bottom, or leaf, level of the clustered index contains the actual data pages. Because a clustered index changes the physical order of the rows, create the clustered index before creating any nonclustered indexes so that the nonclustered indexes will not have to be rebuilt. By definition, only one clustered index is permitted per table (regardless of how it is created: implicitly, with constraints, or explicitly, with CREATE INDEX). Often it is created on the column(s) that is frequently accessed in groupings. Before you create indexes, it is important to know how your data will be accessed.
Using a clustered index to find data is almost always faster than using a nonclustered index. In addition, using a clustered index is advantageous when many rows with contiguous key values are being retrieved ¾ that is, on columns that are often searched for ranges of values. Once the row with the first key value is found, rows with subsequent indexed values are guaranteed to be physically adjacent.
If you do not specify CLUSTERED, a nonclustered index will be created.
NONCLUSTERED:Creates an object that specifies the logical ordering of a table. With a nonclustered index, the physical order of the rows is not the same as their indexed order. The leaf level of a nonclustered index contains pointers to rows on data pages. That is, each leaf page contains an indexed value and a pointer to the row with that value. A nonclustered index, in comparison with a clustered index, has an extra level between the index structure and the data itself.
index_name:Is the name of the index. Index names must be unique within a table but need not be unique within a database. Index names must follow the rules of identifiers. You must be the owner of a table to create an index on it. The owner of a table can create an index at any time, whether or not there is data in the table. Indexes can be created on tables in another database by qualifying the database name.
table_name:Specifies the table that contains the column or columns to be indexed.
column_name:Specifies the column or columns to which the index applies. Specify two or more column names if you want to create a composite index on the combined values in the specified columns. List the columns to be included in the composite index (in sort-priority order) inside the parentheses after table_name.
Composite Indexes:
Composite indexes are used when two or more columns are best searched as a unit. You can combine as many as 16 columns into a single composite index. All the columns in a composite index must be in the same table. The maximum allowable size of the combined index values is 256 bytes. (That is, the sum of the lengths of the columns that make up the composite index cannot exceed 256.)
Example 1
This example creates an index on the au_id column of the authors table.
CREATE INDEX au_id_ind
ON authors (au_id)
Example 2
The following example creates a unique index on the following table. Notice that we cannot assign a primary key to this table and this is our second choice to index the table for faster querries
Report_num Line_num Report
650000 1 St. Joseph's hospital
650000 5 397-22-1234
650000 8 ------------------
--------- n ------------------
CREATE UNIQUE CLUSTERED INDEX unique_report
ON mrm_report_pre (Report_num,Line_num)