Indexes in SQL Server

An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

Types of indexes

A table or view can contain the following types of indexes:

Clustered

  • Clustered indexes sort and store the data rows in the table or view based on their key values.
    • These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.
  • The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

    • Nonclustered

  • Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
  • The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
  • You can add nonkey columns to the leaf level of the nonclustered index to by-pass existing index key limits, and execute fully covered, indexed, queries.

    • Both clustered and nonclustered indexes can be unique. This means no two rows can have the same value for the index key. Otherwise, the index is not unique and multiple rows can share the same key value.
    Indexes and Constraints

    Indexes are automatically created when PRIMARY KEY and UNIQUE constraints are defined on table columns. For example, when you create a table with a UNIQUE constraint, Database Engine automatically creates a nonclustered index. If you configure a PRIMARY KEY , Database Engine automatically creates a clustered index, unless a clustered index already exists. When you try to enforce a PRIMARY KEY constraint on an existing table and a clustered index already exists on that table, SQL Server enforces the primary key using a nonclustered index.

    CREATE INDEX Syntax

    Creates an index on a table. Duplicate values are allowed: 

                                    CREATE INDEX index_name
                                ON table_name (column1, column2, ...);

    CREATE UNIQUE INDEX Syntax

    Creates a unique index on a table. Duplicate values are not allowed: 

                                    CREATE UNIQUE INDEX index_name
                                ON table_name (column1, column2, ...);

    Trending Post
    What is Anonymous methods in C#.
    Why Is It A Bad Idea To Throw Your Own Exceptions.
    How's The DLL Hell Problem Solved In .net?
    Interview Questions and Answers
    Describe the Async method of C#?
    Is it possible to serialise hashtables?
    Explain the differences between “out” and “ref” parameters in C#?
    How would you implement the Singleton design pattern in C#?
    What Are The Ways To Deploy An Assembly?
    What Are Advantages And Disadvantages Of Microsoft-provided Data Provider Classes In Ado.net?