Explain Composite Index in SQL Server.

Answer:

In SQL 2005 and 2008 we can add up to 16 columns in a index. Such index that consists of more than one columns is referred as composite index.
Both clustered and non-clustered indexes can be composite index.The order of the columns are very important here.

Syntax

CREATE INDEX index_name

    ON [table_name] (col1,col2,...col16); 

For example.

 CREATE INDEX IX_Student_Composite

    ON [Student] (student_Code,student_Name);