Indices

Database systems generally use indices to provide fast access to relational data. An index is a separate physical data structure that enables queries to access one or more data rows fast. Proper tuning of indices is therefore a key for query performance. An index is in many ways analogous to a book index. When you… [Continue Reading]

Clustered Indices A clustered index determines the physical order of the data in a table. The Database Engine allows the creation of a single clustered index per table, because the rows of the table cannot be physically ordered more than one way. When using a clustered index, the system navigates down from the root of… [Continue Reading]

The CREATE INDEX statement creates an index for the particular table. The general form of this statement is CREATE [UNIQUE] [CLUSTERED |NONCLUSTERED] INDEX index_name   ON table_name (column1 [ASC | DESC] ,…)     [ INCLUDE ( column_name [ ,… ] ) ]   [WITH     [FILLFACTOR=n]     [[, ] PAD_INDEX = {ON | OFF}]     [[, ] DROP_EXISTING = {ON |… [Continue Reading]

During the life cycle of an index, it can become fragmented, meaning the storage of data in its pages is done inefficiently. There are two forms of index fragmentation: internal and external. Internal fragmentation specifies the amount of data, which is stored within each page. External fragmentation occurs when the logical order of the pages… [Continue Reading]

Altering Indices The Database Engine is one of a few database systems that support the ALTER INDEX statement. This statement can be used for index maintenance activities. The syntax of the ALTER INDEX statement is very similar to the syntax of the CREATE INDEX statement. In other words, this statement allows you to change the… [Continue Reading]

Although the Database Engine does not have any practical limitations concerning the number of indices, it is advisable to limit them, for a couple of reasons. First, each index uses a certain amount of disk space, so it is possible that the total number of index pages could exceed the number of data pages within… [Continue Reading]

The Database Engine allows you to create the following special types of indices: Indexed views Filtered indices Indices on computed columns Partitioned indices Column store indices XML indices Full-text indices Indexed views are based on views and therefore will be discussed in the next chapter.Filtered indices are similar to  indexed views. You can find their… [Continue Reading]