Indexes

• speed up the querying process by providing swift access to rows in the data tables. (same as book index)
• Created on columns of tables or view.
• No need add primary key to index, because SQL Server automatically creates a unique index when you declare the primary key.
• good to use Index at column WHERE and JOIN.
• SQL Server first finds that value in index, then use the index to quickly locate the row of data
• Index not recommend create on large object data type (image,text,varchar(max))
• E.g. search value 123, the search engine will search root level and determine which intermediate level to go, 101-200, then will go to the node level until find 123.
• The leaf node will contain entire row of data or pointer to the row, depending on the index is clustered or nonclustered

Clustered Index

• Store actual data rows at the leaf level of index.
• clustered index is sorted in ascending or descending.
• only one clustered index on a table.
• Query will faster than NON-Clustered.

Nonclustered Index

• Leaf nodes of a non-clustered index only contain the values from the indexed columns and row locators to point the actual row.
• Non-clustered indexes cannot be sorted like clustered index.
• Create more than one non-clustered index per table or view.

Why many indexes will cause slower performance

• Each index is a system-managed table, so every add or update data will update the index, then will cause slower performance of data updates.
• Frequently update key column