When data volumes increase, organizations are faced with problems relating to data retrieval and posting. They feel the need for a mechanism that will increase the speed of data access. An index, like the index of a book, enables the database retrieve and present data to the end user with ease. An index can be defined as a mechanism for providing fast access to table rows and for enforcing constraints.
An index can be created by selecting one or more columns in a table that is being searched. It is a kind of ‘on disk’ structure associated with the table or view and contains keys that are built from one or more of the columns in the table or view. This structure known as B-Tree helps the SQL Server find the row or rows associated with the key values. Indexes can be created on computed columns or xml columns also.
Indexes can be clustered or non clustered. A clustered index stores data rows in the table based on their key values. Each table can have only one clustered index as the key values in the data rows are unique and the index is built on the unique key column. When a table has a clustered index, it is known as a clustered table. Non-Clustered indexeshave structures that are different from the data rows. A non clustered index key value is used to point to data rows that contain the key value. This value is known as row locator. The structure of the row locator is determined on the basis of the type of storage of the data pages. If the data page is stored as a heap, a row locator becomes a pointer to a row. If the data page is stored in a clustered table the row locator is a clustered index key. Clustered and Non clustered indexes can be unique and indexes are automatically maintained for a table or view whenever the data table is modified.
No comments:
Post a Comment