Index
Index in table is similar to that of index in books. When an index key is created, actually a b-tree table is created and populated with row data / address. We use index in a book to go to a particular page quickly. Same way, index in a table is used to go to a specific data page quickly to return against search.
Clustered Index
When a column or composite column is set as Clustered index, data rows (pages) arranged physically based on the key that has been set. When a search command is supplied for the clustered index field, Sql engine do a seek instead of scanning the table as the table is arranged sequential physical order. This results in fast retrieval of data. This type of physical arrangement of table based on the key is called as Clustered Index.
As, the physical table structure is arranged, we can have only one clustered index in a table. But one key can have one or multiple fields.
Index in table is similar to that of index in books. When an index key is created, actually a b-tree table is created and populated with row data / address. We use index in a book to go to a particular page quickly. Same way, index in a table is used to go to a specific data page quickly to return against search.
Clustered Index
When a column or composite column is set as Clustered index, data rows (pages) arranged physically based on the key that has been set. When a search command is supplied for the clustered index field, Sql engine do a seek instead of scanning the table as the table is arranged sequential physical order. This results in fast retrieval of data. This type of physical arrangement of table based on the key is called as Clustered Index.
As, the physical table structure is arranged, we can have only one clustered index in a table. But one key can have one or multiple fields.
Index is actually a tree data structure. A tree contains branches and Leaf nodes. Leaf nodes of a clustered index is the actual data.
When clustered should be used
Clustered index should be used when there no/less insert/delete/key update in tables. As, clustered index sorts the physical order of the table. Insert, Delete and key updates make the physical order of the table rearrange at every delete / key update causing delay
Non Clustered Index
Non clustered index on the other hand do not touch the physical order of the table. It creates b-tree table based on the non clustered key values and keys will be used as pointer to fetch the particular data row. A table can have many non clustered index.
When a unique key is set in table, by default a non clustered index will be created by sql. Non clustered can be created based on a max of 16 columns which should be with in 900 bytes.
Non clustered index have no constraints / limitation. When a better performance is required for searching a specific filed. non clustered can be created
