What is an Index ?
Index is database object. It works the same way as book index do. It can be created on one or more columns (16 Max column combination). It will improve the performance of data retrieval, but it slows down data modification operations such as Insert, Update.
1. Clustered Index:
They are physical stored. Therefore a table can have only one clustered index. When a primary key is created on the table, a clustered index is automatically created. A clustered index is particularly efficient on columns that are often searched. Clustered indexes are not a good choice for columns that undergo frequent changes.
Following is the T-sql syntax of creating Primary key Clustured index:
ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
2. Non Clustered Index:
They are logical ordering of records. A table can have more than one Non-Clustered index. Non clustered indexes can be defined on a table or view with a clustered index. Non-Clustered column always depends on the Clustered column on the database.
Following is the T-sql syntax of creating Non Clustured index:
CREATE NONCLUSTERED INDEX index_nonClustered ON TableName ColumnName