11.2. Indexes

SQL Server allows you to create several indexes on a table. In SQL Server , it is the job of the query optimizer to determine which indexes will be the most useful in processing a specific query. Although indexes may speed up queries in large tables, indexes will slow update operations (insert, delete, update), because every update causes a rebuild of the index. We begin by introducing the "simple" CREATE INDEX statement.

Discussing the query optimizer is beyond the scope of this book.

11.2.1. The "Simple" CREATE INDEX

The CREATE INDEX statement is used to create a new index on some column in an existing table. The following is the general syntax for the CREATE INDEX statement:

CREATE INDEX index_name
ON Tablename (column [ASC | DESC])

For example, if we wanted to create an index called ssn_ndx on the ssn column, in descending order of ssn, for the Test1 table, we would type the following:

CREATE INDEX ssn_ndx
ON Test1 (ssn DESC)

You will get:

Command(s) completed successfully.

This result means that the index was successfully created. Although the user has the option of setting the column in ascending (ASC) or descending (DESC) order, if DESC is not included, the index will be created in ascending order, because ASC is the default order for indexes .

To view the index that you just created, click on the + sign beside the newly created table, Test1, and then click on the + sign beside the Indexes node, and you will be able to see that index that we just created, ssn_ndx, and you will get Figure 11-1.

Figure 11-1. Viewing the index

Now, to see if this index, ssn_ndx, is in ascending order or descending order, right-click on the index, ssn_ndx and select Properties, and you will get Figure 11-2. Figure 11-2 shows that this index, ssn_ndx, is in descending order, indexed by the ssn column.

Also from Figure 11-2, to add more columns to the index key, we can click Add; to remove columns from the index key, we can select the key and then click Remove.

To prevent duplicate values in indexed columns, you must use the UNIQUE option in the CREATE INDEX statement, as follows:

CREATE UNIQUE INDEX ssn_ndx1
ON Test1 (ssn DESC)

This query will create the unique index, ssn_ndx1, as shown in Figure 11-3.

The UNIQUE option can be used on columns that will not be a primary key in a table. A primary key is a key or field that uniquely identifies a row in a table.

The UNIQUE option will disallow duplicate entries for a column even though the column is not a primary key in a table. NULLs are allowed in nonprimary key indexes.

11.2.2. Deleting Indexes Using SQL

You can use a DROP INDEX statement to delete an index in SQL. The general format of the DROP INDEX statement is as follows:

DROP INDEX Table_name.index_name

For example, to delete the index ssn_ndx1 created on Test1, you would type the following:

DROP INDEX Test1.ssn_ndx1

Figure 11-2. Index properties

Figure 11-3. Showing the UNIQUE index

Unused indexes slow data modification without helping retrieval. So, if you have indexes that are not being used, you should delete (drop) them. All indexes will automatically get deleted (dropped) if the table is deleted.

Indexes cannot be created on all column types in SQL Server . For example, you cannot create an index on a column of TEXT, NTEXT, or IMAGE data type.