An Index is a database object that can be created on one or more columns (16 max column combinations). When creating the index it will read the column(s) and forms a relevant data structure to minimize the number of data comparisons. The index will improve the performance of data retrieval and add some overhead to the data modification such as create, delete and modify. So it depends on how much data retrieval can be done on table versus how much of DML (Insert, Delete and Update) operations.
Need of Index in Database: An index is basically used for fast data retrieval from the database.
Syntax: CREATE INDEX index_name ON table_name;
or
DROP INDEX index_name;
Type of Index:
In a SQL Server database there are mainly the following two types of indexes:
Clustered index and
Non Clustered index
