Index in SQL Server

Index : 

• Index is a pointer to the data in database.
• Index are created on a table or views.
• Index can be stored either with in the table data or separate from it.
• Index can also be explained like ordered list of values taken from one or more column in the table arranged in the form of B-Tree structure.

Types of Indexes:

• Clustered Index
• Non Clustered Index

Clustered Index:-

• It determines the physical order of the database.
• The data should be physically stored according to the key column specified for the index. This will stored the information according to the alphabetically order.
• The Index are particularly efficient when you frequently need to sort the data.
• We can have only one Clustered Index per table.

Advantages:
Searched results are quicker when records are retrieved by the clustered index.

Disadvantages:

• This index needs more disk space. The more indexes have more disk space.
• It takes longer to update the records if only when the fields in the  clustered index are changed.
• Avoid Clustered Index constructions when there is a risk that many concurrent inserts will happen on almost the same clustered index value.

Non Clustered Index:

• This index indicates which page to go, but the data is not with in the index itself.
• This index tells the Sql server where to find the data row corresponding to the key in the index.
• It allows 249 non clustered indexes in the table.

Advantages:

• Non Clustered Index on views can provide additional query performance.

Advantages of Index:-

• it will not allow duplicate values.
• less time  will take when the Index is searching for  particular data.
• Better performance while retrieving data.

Limitations of Index:

• Index will degrade the performance while performing INSERT, DELETE AND UPDATE operations.
• Index are recommended only on large tables with 100's rows

View Index:
SP_HELPINDEX Index name

Execute Index:
EXEC Index name

Dropping Index:
Drop Index Tablename.Index