SBERRY at Tech

Let’s learn Index with Sberry and unravel the different types of 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.
Index contains keys built from one or more columns of the table. These key are arranged in B+ Tree structure. The maximum number of key columns in an index is 16 or an index size of 900 bytes.

B+ Trees have keys and pointers at each node.
Types of Indexes:
Clustered Index
Non Clustered Index 

CLUSTERED INDEX:

clustered

The logical ordering of key values defines the physical order of the data rows of the table i.e., the data is stored in a sorted order in the table if a clustered index is defined on the table. The B-Tree structure contain a key and pointer at each node which points to other nodes. The leaf nodes of the index contains the actual data pages acting as folder for data rows. When a new row is created then it is stored in a sorted order so as to preserve the sortedness of the index. The clustered index stores the data pages in row wise and so it is called row store index.
When a table has a clustered index then it is called a clustered table else it is heap.

Syntax
Create clustered index index_Name
on Table_Name(Column_Name)

To dig into working of Clustered Index

NON CLUSTERED INDEX:
Non Clustered Index 1

It has a structure disparate than the data rows. It has keys (non clustered keys) with every key possessing value and a pointer that maps to the original data row. This pointer from an index row in a non clustered index to a data row is called a row locator.

Non clustered index can be defined on a heap table or clustered table.In the leaf level of non clustered index, each index row contain the non clustered key value and a row locator. This locator point to a the data row in the clustered table or heap.The row locator in non clustered index rows are either a pointer to a row or a clustered index key for a row. If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier, page number and slot number of the row on the page. The whole pointer is known as a Row ID(RID). If the table has a clustered index, the row locator is the clustered index key for the row. There can be 999 non clustered index for a table.

syntax:
CREATE NONCLUSTERED INDEX index_name
ON table_name(Column1 ASC,Column2 DESC…….)

To Dig into Non Clustered Index 

UNIQUE INDEX: No two key values in the index can be same. For this the columns used in the index must be not null since two null values are considered to be the same. The Database Engine will issue an error message if the column has duplicate data when creating a unique index even IGNORE_DUP_KEY is set ON.

A unique clustered index is created default for a primary key constraint while a unique non clustered index is created for unique constraint. How ever we can define a unique clustered index for non primary key columns provided that clustered index is not defined before for the table.

syntax:
CREATE UNIQUE INDEX index_name
ON table_name(Column1 ASC,Column2 DESC…….)

INCLUDED COLUMNS:
Non clustered index created with Included columns cover more queries. This is because the non key columns have the following benefits:
They can be data types not allowed as index key columns.
They are not considered by the Database Engine when calculating the number of index key columns or index key size.
Non key columns can only be defined on non clustered indexes.
All data types except textntext, and image can be used as non key columns.

Syntax:
CREATE NON CLUSTERED INDEX index_name
ON table_name(Column1 ASC,Column2 DESC…….)
INCLUDE(Col1……)

COMPOSITE INDEX: When index contains more than one column then it is referred to as composite index.

COLUMN STORE INDEX: The Index are stored in columnar format instead of Row format. Individual columns are stored in separate segments and they are joined together to form the row group. The rows of every columns are stored in correspondence to a single row such that when joined results in the single row data of all columns. Segments contain data from only one column while columns can span multiple segments. 
Column store index are profoundly used in large data sets and BI.

Advertisements