Tuesday, July 7, 2015

SQL – Indexes

Indexes in a database are analogous to indexes that you find in a book. Indexes can help queries to find data quickly in a database. An index is a data structure (most commonly a B- tree) that stores the values for a specific column in a table. An index is created on a column of a table. 

An index consists of column values from one table, and that those values are stored in a data structure.  The index also stores pointers to the table row so that other column values can be retrieved – read on for more details on that. It is important to understand that a database index does not store the values in the other columns of the same table.
Simply, we can say that Indexes are just a performance optimization technique in the database that speeds up the data retrieval process. It is a persistent data structure that associated with a Table (or View) in order to increase performance during retrieving the data from that table (or View).
An index is used for several different reasons in the database such as –
  1. Indexes are used for performance optimization means speed up querying to get rows or sort rows faster as possible. 
  2. Indexes are to define a primary-key or unique index which will guarantee that no other columns have the same values.
Data structure is an index
B- Trees are the most commonly used data structures for indexes. The reason B- trees are the most popular data structure for indexes is due to the fact that they are time efficient – because look-ups, deletions, and insertions can all be done in logarithmic time. And, another major reason B- trees are more commonly used is because the data that is stored inside the B- tree can be sorted.
A hash table (hash map) is a data structure used to implement an associative array, a structure that can map keys to values. A hash table uses a hash function to compute an index into an array of buckets or slots, from which the correct value can be found. A hash table is an unordered data structure and there are many types of queries which hash indexes cannot even help with.

Advantage of Indexes
The database engine can use indexes to boost performance in a number of different queries. Sometimes these performance improvements are dramatic. We can say –
  1. Indexes are improve the Searching Records process to finding a record or set of records matching a WHERE clause. Indexes can aid queries looking for values inside of a range as well as queries looking for a specific value.
  2. Indexes work amazingly as well when searching for a record in DELETE and UPDATE commands as they do for SELECT statements.
  3. Indexes are improve the Sorting Records process to finding a record or set of records matching a WHERE clause.
  4. GROUP BY queries can also take benefit from indexes. Indexing a field used in a GROUP BY clause can often speed up a query.
  5. Maintaining a Unique Column - Each time an application adds or modifies a row in the table, the database needs to search all existing records to ensure none of values in the new data duplicate existing values.
Disadvantages of Indexes
  1. Every Non-Clustered index requires additional disk space as it is stored separately from the table while Clustered Index does not require any additional storage. 
  2. The ratio of index size to table size can vary greatly, depending on the columns, data types, and number of indexes on a table. This may cause the system to restructure the index structure (Hash Index, B-Tree etc), which can be very computationally expensive.
  3. Another downside to using an index is the performance implication on data modification statements means any time a query modifies the data in a table (INSERT, UPDATE, or DELETE) then the database needs to update all of the indexes where data has changed.
Type of Indexes in SQL
The following types of indexes are available in SQL Server-

We will cover all the indexes one by one here.

No comments:

Post a Comment