There are various types of indexes in SQL server:

• Clustered Index
• Non-Clustered Index
• Column Store Index
• Filtered Index
• Hash Index
• Unique Index

Clustered Index

• Rows of data are sorted and stored data in a table or view depending on the central values by clustered index.
• Each table can have one clustered index as it enables the user to store the data in a single order.
• Data is sorted and stored in gathered index, so if we see a data in a table sorted means it is arranged with clustered index.
• If a table contains clustered index, it is called as clustered table.
• When a huge data needs to mdified in a database we use clustered index.

Non Clustered index

• Non clustered index are structures that are seperated from the data row.
• This type of index contains non clustered key values and those pairs has a pointer to that data row.
• In non clustered index, arrow from an index row to data row is called as row locator.
• Row locator structure identifies whether the data pages are in the form of clustered table or heap.
• Row locator is termed as cluster index key in clustered chart.
• User can easily add non key columns to the leaf level in a non clustered index as it byepasses the existing index key limits to perform fully covered indexed queries.
• A non clustered index is created to improve the overall performance of frequently asked questions which cannot be created in clustered items.

Unique Index

• It enables users to confirm that every row in the table is unique in one way or other by checking if the indexing key contains any duplicate values.
• When user wants to use the unique character of data he can use unique index.
• Unique index allows indidviduals to ensure that data integrity of each defined column of the table in the database.
• It provides additional information about the data table which is helpful for query optimizer.

Filtered Index

• This index is created when column has only a small number of relevent values for queries on the subset of values.
• When table contains hetrogeneous data rows, a filtered index is created in sql for one or more data types.
• Filtered index is indicate even when query optimizer does not cover any query.
• Filtered index indexes the portion of rows in a table, it applies filter on index by improving the overall performance of the query.
• Filtered index helps to reduce the maintanence cost and storage cost
• Overall impact of data modification is less in filtered index as it is updated only when a new record is inserted or when the data of the index is impacted.

Column Store Index

• Column store index is standard form of index when it comes to storing and querying the large data warehousing tables.
• This index was designed for the impovement in the performance of query in case of workloads with vast data.
• Data are stored as column based format.
• It reduces overall storage cost and provides high level compression of data.
• Since data compression happens, user can efficiently perform input and output functions for a high performance result.

Hash Index

• Hash index is simply an array in sql that contains pointer slot and a row on each slot.
• Hash index use hash function F(K,N) where K is critical and N is number of Buckets.
• Function maps out to key corresponding to the bucket of the hash index.
• Each hash bucket index contains 8 bytes which is used to store the memory address of the linked list of critical entities.