Indexes

One of the most important routes to high performance in sql server database is an index.

It is a database object which is used to speed up the querying process by providing

Quickly access to rows in the database Tables.

By using Indexes we can save time and can improve the performance of database queries and applications.

An Index contains keys built from one or more columns in the table and map to the storage Location of the specified data.

When we create an Index on any column,sql server internally maintain a separate table called index table.

So that when ever user trying to retrieve the data from existing Table depends on index Table sql server directly go to the table and retrieve required data very quickly.

In the Table we can use Maximum 250 Indexes.the Index Type refers to the way the index is stored internally by sql server.So a Table can contain the two types of indexes.

1. Clustered Index

2. Non-clustered Index

Clustered Indexes-The only time the data rows in a Table are stored in Sorted (ascending order only) order structure is when the Table contains Clustered Index.

When a Table has a clustered index,Then is called a Clustered Table.If a Table has no clustered index,its data rows are stored in an unordered structure.

A Table can have only 1 clustered Index on it,which will be created when primary key constraint is used in a Table.

No-Clustered Indexes-Non-clustered Indexes will not have any arrangement order(unordered structure) of the data in the table.In a Table we can create 249 Non Clustered Indexes.

If we dont mention clustered indexes in a table Then default is stored as Non-clustered Indexes