indexes in sql server

By using index: it will traverse through the index tree structure to find the required data and extract the data that satisfy the query criteria.

Indexes: indexes in sql server are similar to index in a book. Indexes are used to improve the performance of quires.
Indexes are generally created for following columns.
1). Primary key column
2). Foreign key column: Frequently used in join conditions
3). Column which are frequently used in where clause
4). Columns which are used to retrieve the data in sorting order
Indexes are cannot be created for following columns
The columns which are not frequently used in where clause.
Columns containing the duplicate and NULL values.
Columns containing images, binary information, and text information.
Types of indexes
1). Clustered index
2). Non-clustered index
3). Unique index
4). Composite index
1). Clustered index: Only one clustered index is allowed for a table. The order of values in a table. Order of values in index is also same. When cluster index is created on table data is arranged in ascending order cluster index will occupy 5% of the table.
Syntax:
Create clustered index <index_name> an <table_name> (columns)
Create clustered index emp_clindex an emp (empno)
2). Non-clustered index:
It is the default index created by the server the physical order of the data in the table is different from the order of the values in index.
Max no of non – clustered indexes allowed for table is 249.
Syntax:
Create non-clustered index <index_name> on table_name <columns>
Create non-clustered index emp_sal on emp (deptno, sal)

3). Unique index:
An index with unique constraint. It will not allow duplicate values.
Syntax:
Create unique index <index name> on <tablename> (column)
Create unique index dept_index on dept (dname)