SQL Index, SQL Index Types, SQL Index Best Practices

By Admin at 18 Feb 2010, 16:13 PM
  • What is SQL Index?

    An index in a SQL database is similar to the index, or table of contents, in a book. The index in a book helps to quickly search for a particular topic instead of flipping through all the pages of the book. Likewise, when a database has large volume of data, then retrieving particular data from the database consumes more time and thus it becomes a performance issue. Creating indexes on the columns that are frequently accessed can be a good solution to retrieve the data quickly.

    Indexes can be created on one or more fields of a table. Indexes are not applicable to the fields of large object (LOB) data types like text, image, and varchar(max).

    SQL Index Types

    In SQL Server, we can create different types of indexes.

    1. Unique Index

      • This type of index does not allow the fields being indexed to have duplicate values.
      • A unique index will be created automatically when you define a primary key or unique constraint.
    2. Clustered Index

      • This type of index reorders the physical data of the table based on the logical order of the key values.
      • Each table can have only one clustered index.
    3. Non Clustered Index

      • Unlike Clustered Indexes, Non Clustered Indexes does not alter the physical order of database table rows.
      • It maintains the logical ordering information of the data alone.
      • SQL server 2008 allows each table to have up to 999 Non Clustered Indexes.

    In SQL server, indexes can be created using either SQL Server Management Studio or T-SQL. Let’s see about dealing with indexes using T-SQL.

    To create an index

    1. CREATEINDEX index_name
    2. ON table_name (column_name1, column_name2,..)

    (Or)

    1. CREATE NONCLUSTERED INDEX index_name
    2. ON table_name (column_name1, column_name2,..)

    By default, a non clustered index will be created unless the CLUSTERED key word is mentioned as follows.

    1. CREATE CLUSTERED INDEX index_name
    2. ON table_name (column_name1, column_name2,..)

    To create a unique index

    1. CREATEUNIQUEINDEX index_name
    2. ON table_name (column_name1, column_name2,..)

    The above query creates a unique non clustered index. To create a clustured unique index mention the CLUSTERED keyword explicitly as follows.

    1. CREATEUNIQUE CLUSTERED INDEX index_name
    2. ON table_name (column_name1, column_name2,..)

    To drop an index when it is no longer needed

    1. DROPINDEX table_name.index_name

    Include Option

    While creating a non clustered index, using the Include option the values of non key columns can also be stored along with the key column values being indexed. These included non key columns do not take part in the indexing process. Including the non key columns to the non clustered indexes can improve query performance when all columns selected in the query are in included in the index as key or non-key fields. The performance improvement is because the query optimizer can locate all the columns within in the index, requiring less work to execute the query.

    To include non key columns to a non clustered index,

    1. CREATEINDEX index_name
    2. ON table_name (column_name1, column_name2,..)
    3. INCLUDE ( nonkey_column1, nonkey_column2,...)

    SQL Index Best Practices


    The following points must be considered before you decide to use indexes:

    • Creating indexes on tables may improve the search performance. However, it slows down the performance of DML operations on the indexed tables.
    • Indexing a table will not be efficient unless the table has large number of records.
    • The performance of an index depends on the uniqueness of the columns involved. More duplicate values less index performance.
    • Creating too many indexes on a table requires more physical space to store them.
    • Creating an index on too many variables may lead to index that’s larger than the size of the table. An index must be relatively small compared to the table.

    Comments

     

    Post a comment

    Please correct the following: