Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Indexing

Indexing is something like adding page numbers to book in real world example. As a publisher if you are not adding page number to book it’s very hard for reader or user to read book and find information from specific page.

Indexes directly affects application performance, good indexing improves application
performance and bad indexing reduce application performance. 

Definition:

An index is an on-disk structure associated with a table or views that speeds retrieval of rows from the table or view.

Basic Types:

Clustered:

A table can have only one clustered index. That is because a clustered index is the actual table sorted in order of the cluster key.
If table has no clustered index data are stored in an unordered structure called heap.

Real word Example for clustered index:

Phone directory book, each directory of phone book represents one row. Primary key on table is by default clustered index for table.  

Nonclustered:

A table can have one or many non clustered indexes (i.e. max 999 for SQL SERVER 2008 and 249 for SQL server 2005).
A clustered index uses clustered key to sorts and stores the data rows of the table or view.The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.

Example for Clustered Index:

In Web-shop billing or shipping Address has multiple column data we can add User Name+MobileNumber Or telephone as Non Clustered index.
It is recommended to use unique key for non clustered indexes.

Special purpose indexes:

Hash

With a hash index, data is accessed through an in-memory hash table.

Memory-optimized nonclustered indexes

For memory-optimized nonclustered indexes, memory consumption is a function of the row count and the size of the index key columns

Unique

Uniqueness can be a property of both clustered and nonclustered indexes.This ensures that index kay contain no duplicate values.

XML

A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.

Spatial

A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type.

Filtered

An optimized nonclustered index. A proper designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

Index with included columns

A nonclustered index that is extended to include nonkey columns in addition to the key columns.

Index on computed columns

An index on a column that is derived from the value of one or more other columns, or certain deterministic inputs.

Invalid entry,please enter valid data.

Loading