Indexing

An index is a list of all the values in a specific column of one of your tables that SQL Server can reference to speed up data access. If an index does not exist to help find the necessary rows, SQL Server has no other option but to look at every row in a table to see if it contains the information required by the query. This is called a table scan.

          When you create a clustered index on a view, the result set returned by the view is stored in the database the same way a table with a clustered index is stored, meaning the result set of the view is stored as an entirely separate object in the database and doesn’t have to be regenerated (or materialized) every time someone runs a SELECT query against it.

                   An index can be created on one column, called a simple index, or on more than one column, called a compound index.

A clustered index defines the physical order of the data in the table. If you have more than one column defined in a clustered index, the data will be stored in sequential order according to columns: the first column, then the next column, and so on. Only one clustered index can be defined per table.

                                      Do not place a clustered index on columns that will have a lot of updates performed on them, as this means SQL Server will have to constantly alter the physical order of the data and so use up a great deal of processing power.

A nonclustered index does not store the table data itself. Instead, a nonclustered index stores pointers to the table data as part of the index keys; therefore, many nonclustered indexes can exist on a single table at one time. When a nonclustered index is created, the information used to build the index is placed in a separate location to the table and therefore can be stored on a different physical disk if required.

An index can be defined as either unique or nonunique. A unique index ensures that the values contained within the unique index columns will appear only once within the table, including a value of NULL. SQL Server automatically enforces the uniqueness of the columns contained within a unique index.

A nonunique index has more overhead than a unique index when retrieving data. SQL Server will need to check if there are multiple entries to return, compared with a unique index where SQL Server knows to stop searching after finding the first row.

Unique indexes are commonly implemented to support constraints such as the primary key. Nonunique indexes are commonly implemented to support locating rows using a nonkey column.

There is a limit of 16 columns on an index, and the total amount of data for the index columns within a row cannot be more than 900 bytes.

Some of factors that can determine if an index is good:

• Using “low-maintenance” columns

• Using primary and foreign keys

• Being able to find a specific record

• Using covering indexes

• Looking for a range of information

• Keeping the data in order