Microsoft SQL Server 7.0 Database Implementation Training Kit: Exam #70-029

Chapter 6: Planning and Creating Indexes

...Lesson 3: Creating Indexes

After this lesson you will be able to:

• Create clustered and nonclustered indexes with unique or composite characteristics.
• Use the CREATE INDEX options to expedite index creation and improve index performance.
• Apply the appropriate fillfactor value to accommodate the future growth of tables.

Estimated lesson time: 75 minutes

Creating and Dropping Indexes

Using the CREATE INDEX Statement

Use the CREATE INDEX statement to create indexes. You also can use the Create Index wizard in SQL Server Enterprise Manager. When you create an index on one or more columns in a table, consider the following facts and guidelines:

• SQL Server automatically creates indexes when a PRIMARY KEY or UNIQUE constraint is created on a table. Defining a PRIMARY KEY or UNIQUE constraint is preferred over creating standard indexes.
• You must be the table owner to execute the CREATE INDEX statement.
• Indexes cannot be created on views.
• SQL Server stores index information in the sysindexes system table.
• Before you create an index on a column, determine whether indexes already exist on that column.
• Keep your indexes small by limiting the key values to one or two columns. Typically, smaller indexes are more efficient than indexes with larger key values.
• Select columns on the basis of uniqueness so that each key value identifies a small number of rows.

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index_name ON table (column [,...n])
[WITH
[PAD_INDEX]
[[,]FILLFACTOR = fillfactor]
[[,] IGNORE_DUP_KEY]

[[,] DROP-EXISTING]

[[,] STATISTICS_NORECOMPUTE]
]
[ON filegroup]

If you do not specify UNIQUE in the CREATE INDEX statement, the index will allow duplicates. If you do not specify CLUSTERED in the CREATE INDEX statement, a nonclustered index will be created.

Example

This example creates a clustered index on the lastname column in the member table.

CREATE CLUSTERED INDEX cl_lastname
     ON library..member (lastname)

Using the DROP INDEX Statement

Use the DROP INDEX statement to remove an index from a table. When you drop an index, consider the following facts:

• SQL Server reclaims disk space that is occupied by the index when you execute the DROP INDEX statement.
• You cannot use the DROP INDEX statement on indexes that are created by PRIMARY KEY or UNIQUE constraints. You must drop the constraint in order to drop these indexes.
• When you drop a table, all indexes for that table are also dropped.
• When you drop a clustered index, all nonclustered indexes on the table are rebuilt automatically.
• You must make the database in which an index resides the current database in order to drop that index.
• The DROP INDEX statement cannot be used on system tables.

DROP INDEX table.index [,...n]

Example

This example drops the cl_lastname index from the member table.

USE library
DROP INDEX member.cl_lastname

To create an index on the loan table

In this exercise, you will open a script file that creates an index, review the contents of the script, execute the script, and then verify that the index was created.

1. Open SQL Server Query Analyzer and verify that you are using the library database.
2. Open the c:\sqlimpl\exercise\ch06\creaindl.sql script file.
3. Review the CREATE INDEX statement. This script creates a nonclustered index named loan_member_link on the member_no column in the loan table with a fillfactor value of 75.
4. Execute the script file.
5. Verify that the loan_member_link index was created by executing the following statement:
   
   EXEC sp_helpindex loan
   
   
6. The results of the sp_helpindex system stored procedure show that the loan_member_link nonclustered index exists on the member_no column of the loan table.

A unique index ensures that all data in an indexed column does not contain duplicate values. If the table has a PRIMARY KEY or UNIQUE constraint, SQL Server automatically creates a unique index when you execute the CREATE TABLE or ALTER TABLE statements.

Create a unique index if you want an index that is independent of constraints. However, in most cases, create PRIMARY KEY or UNIQUE constraints rather than creating unique indexes. When you create a unique index, consider the following facts and guidelines:

• If a table contains data, SQL Server checks for duplicate values when you create the index. If the data contains duplicates, the CREATE INDEX statement fails.
• SQL Server checks for duplicate values each time you use the INSERT or UPDATE statement. If duplicate key values exist, SQL Server rolls back the entire update and returns an error message with the first duplicate.
• Create unique indexes only on columns in which entity integrity can be enforced. For example, you would not create a unique index on the lastname column of the member table because some members may have the same last name.

Figure 6.12 shows the creation of a unique, nonclustered index named title_ident on the title table. The index is built on the title_no column. The value in the title_no column must be a unique value for each row of the table.

USE library
CREATE UNIQUE INDEX title_ident
     ON title (title_no)

Finding All Duplicate Values in a Column

If duplicate key values exist when you create a unique index, the CREATE INDEX statement fails. SQL Server returns an error message with the first duplicate, but other duplicate values may exist as well. Use the following sample script on any table to find all duplicate values in a column. Replace the italicized text with information specific to your query.

SELECT index_col, COUNT(index_col) AS '# of Duplicates'
FROM tablename
GROUP BY index_col HAVING COUNT(index-col) > 1
ORDER BY index-col

Example

This example determines whether duplicate member numbers exist in the member_no column in the member table. If so, SQL Server returns the member number and number of duplicate entries for each duplicate member number in the table.

SELECT member_no, COUNT(member_no) AS '# of Duplicates'
FROM library..member
GROUP BY member_no HAVING COUNT(member_no) > 1
ORDER BY member_no

Creating Composite Indexes

An index that is created on more than one column in a table is called a composite index. You can create composite indexes:

• When two or more columns are best searched as a key.
• If queries reference only the columns in the index. This is called a covered query. When a query is covered, the data rows do not have to be retrieved as the column values are read directly from the index keys.

When you create a composite index, consider the following facts and guidelines:

• You can combine as many as 16 columns into a single composite index. The sum of the lengths of the columns that make up the composite index cannot exceed 900 bytes.
• All columns in a composite index must be from the same table.
• Define the most unique column first. The first column defined in the CREATE INDEX statement is referred to as the highest order. In the telephone directory example, the last name is the high order column and the first name is the low order column.
• An index on (columnl, column2) is not the same as an index on (column2, column 1) -- each has a distinct column order. Generally, the column that contains more selective data or that would return the lowest percentage of rows should be the higher order column.
• If the WHERE clause of a query references a lower order column of the composite index, it must also reference all higher order columns in the index for the query optimizer to use the composite index. Like using the first name in a telephone directory, it is only useful if you have first found the last name.
• Composite indexes are useful for tables with multiple column keys. For example, in a sales table the key may be made up of a column that specifies a sales region and another column that specifies an invoice number.
• Used correctly, composite indexes can increase query performance and reduce the number of indexes that you create on a table.
• Multiple indexes on the same columns are typically not useful.

Figure 6.13 shows the creation of a nonclustered, composite index on the loan table. The isbn and the copy_no columns are the composite key values. Notice that the isbn column is listed first because it is more selective than the copy_no column.

USE library
CREATE INDEX loan_ident
ON loan (isbn, copy_no)

...