How to Create Index using sql Query: Sql Server

Database programmer should create indexes on the most frequently queried column in a table. However, at times, you might need to create an index based on a combination of one or more columns. An index based on one or more columns is called a composite index. A composite index can be based on a maximum of 16 columns. However, you need to consider that indexes with less number of columns use less disk space and involve fewer resources when compared to indexes based on more columns.

To create an index you can use the CREATE INDEX statement. The syntax of the CREATE INDEX statement is:

CRATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON [{database_name.[schema_name]. | schema_name.}]
{table_or_view_name} (column [ASC | DESC] [, …n])
[INCLUDE (column_name [, …n])]
[WITH (<relational_index_option>[, …n])]
[ON {partition_cheme_name (column_name [, …n]) | filegroup_name |DEFAULT) ]
<relation_index_option> : : =
{PAD_INDEX = {ON | OFF}
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = {ON | OFF}
| IGNORE_DUP_KEY = {ON | OFF}
| STASTISTICS_NO_RCOMPUTE = {ON | OFF}
| DROP_EXISTING = {ON | OFF}
| ONLINE = {ON | OFF}

Where,

UNIQUE crates an index where each row should contain a different index value. CLUSTERED specifies a clustered index where data is sorted on the index attribute. NONCLUSTERED specifies a nonclustered index that organizes data logically. The data is not sorted physically.
Index_name specifies the name of the index.
Table_name specifies the name of the table that contains the attributes on which the index is to be created.
Column specifies the column or columns on which the index will be created.
Column_name specifies the name of the column or columns on which the index would be created.
ON partition_scheme_name ( column_name ) specifies the partition scheme the specifies the filegroups in which the pattitioned index will be mapped.
ON filegroup_name specifies the filegroup on which index is created.
ON DEFAULT specifies that the specified index will be created on the default filegroup.
PAD_INDEX = { ON | OFF } specifies the index padding, which is OFF, by default.
FILLFACTOR = 1 to 100 specifies a percentage that indicates how full the leaf level of each index page should become during index creation or rebuild. The default value is 0.
SORT_IN_TEMPDB = { ON | OFF } specifies about storing temporary sort results in the tempdb.
IGNORE_DUP_KEY = { ON | OFF } specifies whether a duplicate key value can be inserted or not…
STATISTICS_NO_RECOMPUTE = { ON | OFF } specifies about recomputing the distribution statistics.
DROP_EXISTING = { ON | OFF } specifies that the pre-existing clustered, nonclustered, or XML index is dropped and rebuilt.
ONLINE = { ON | OFF } checks whether the underlying tables and associated indexes are available to query and modify the data during the index operation.