What is the use of creating indexes in table?

creating index on the table will improve the performance of the search query.

Usually without index in the table is called table scan where it needs to scan row by row on all the columns. So it is costly to search and takes the time.

But index is a mechanism to search in the index table, it will find the appropriate matching records in the original table.

It behaves like normal text book where it will have the contents in the front page and you can see the page number for quick navigation.

There are two types of indexes.
1) Clustered index
2) Non clustered index

Clustered index will create default when you have the unique key of the column. A table can have only one clustered index

Non clustered will have the index table and a table can have many non clustered.

CREATE INDEX IX_Product_Manfacturer
ON Products(Manfacturer)

when you search the manfacturer like this

SELECT * FROM Products WHERE manfacturer='TVS'

It will look into the index table.

Hope this will help you.

An Index is used to find the data quickly in the Table. A user can not see the Index.

Syntax:

create index index_name
on table_name (column_name)


For Ex:

create index fn
on Student(FirstName);

It creates an Index(fn)for the FirstName Column of the Student Table.

If we want to create an index on multiple columns in a table, we write the following Code:

create index fnln
on Student(FirstName,LastName);

We can also create a unique index like this:

create unique index index_name
on table_name (column_name)