Why do we use Indexes?
If you want to find a record in a table without having an index, the system will scan the whole table to get that record. Well, this is a very costly operation in terms of time. For example, if your table has 10 million records, it will scan all these records to find the value. To overcome this problem, indexes are used. Indexes use the B-TREE search algorithm to search a record in a table, which is way faster than the table scan.
Types of Index
Below are the frequently used types of Indexes.
- Clustered Index
A clustered index is an index that defines the physical order of records to be stored in a table. Therefore, the table can have only one clustered index.
- Non-Clustered Index
A non-clustered index is similar to an index in a book. The data is stored in one place and the index is stored in another place. Here, the index has pointers to the storage location of the data. Therefore, a table can have more than one Non-clustered index.
The syntax for creating an index.
- CREATE INDEX <Index Name>
- ON <Table Name> (<Column Name>);
-
- /*Example of creating index on Lastname column of Persons table*/
-
- CREATE INDEX idx_lastname
- ON Persons (LastName);
Benefits of indexing foreign key columns
There are two primary benefits of using the Index on foreign key.
- Makes delete operation of parent table faster
When you delete a row from a parent table, the SQL Server checks if there are any rows which reference the row being deleted in the child table. To find the rows efficiently, an index on the foreign key column is extremely useful.
- Making joins faster
Using Index, SQL Server can more effectively find the rows to join to when the child and parent tables are joined.
Can we have multiple indexes on the same column?
- As you can have only one clustered index per table, you cannot have multiple clustered indexes on the same column; however, you can have multiple non-clustered indices on the same column.
Here is the link which explains how duplicate indexes affect the SQL Server's performance.