Please note that the information on this page is related to an older version of Microsoft SQL Server.

Click on SQL Server Tutorials for new training content and material. In addition we have FREE SQL Tutorial videos here, so feel free to download the demos. Our partners also manage two blogs on the following SQL Server Versions:

-SQL Server 2008 Blog
-SQL Azure Blog

Please send any comments or suggestion to info@sqlserver2008tutorial.com

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             Samaritan hospital
650000         5             397-22-1234
650000         8             ------------------
---------         n             ------------------

CREATE UNIQUE CLUSTERED INDEX unique_report
ON mrm_report_pre (Report_num,Line_num)


FOREIGN KEY CONSTRAINT:

Foreign key constraints define relationships among tables. Typically these are know as "parent_child" or "master_detail" relationships. There are two ways you can define foreign key constraint, either by using SQL code or using the Enterprise Manager in SQL Server. We are going to use the latter in this case. Consider a table "mrm_patient" with five fields and one primary key (sysno_patient)

Notice that both the above tables share a common field, the "sysno_patient" field. This field is used to identify the patient. The difference is that this is the primary key in table "mrm_patient" which is considered the parent table. Since table "mrm_condensed_history" already has a primary key "sysno_porder" we can assign a foreign key to "sysno_patient" and this table is considered the child table. Since these two tables have a common field, they have a relationship with each other.
In order for this relationship to exist, go to the Enterprise Manager and edit the child table "mrm_condensed_history". Click on the advance features and then click Foreign Keys. Now you can give a name to the foreign key let us say "FK_sysno_patient". Under the Referenced Table, click on dbo.mrm_patient which gives "sysno_patient" in Key Columns. Under the Foreign Key Columns, select "sysno_patient" and finally click the Add button. Now we have defined a foreign key and established a relationship with table "mrm_patient" In order to have a meaningful relationship, we have to do one more thing. We have to make sure that there are no extra values in the child table that do not exist in the parent table. Once you click the Add button in the previous step, you want to save the table definition. If you get an error 549, that would occur due to extra values. These values should be located using an OUTER JOIN and eliminated before establsihing a relationship.