What is Index and Type of Index in SQL Database ?
Definition : Index are used to make SQL query faster.
Syntax : Create Index statement is used to create Index in table.
CREATE INDEX [INX_INDEX_NAME]ON TABLE_NAME (COLUMN NAME, COLUMN NAME......);
Example :Assume StudentDetails is tablename which contain a column name as ID, Name, Gender, Class, Admissiondate) then How will you create index on column.
Answer : Create Index Idx_StudID
ON StudentDetails(ID)
- Type Of Index
There are two types of Index in SQL
- Clustered Index
- Non Clustered Index
A clustered index is an index that specifies the physical arrangement of a database's table records.
it means when index is created on column it store the records in certain alphabetic or sequential sorted order.
Therefore There can only be one clustered index per table
This sorted data structure is called a B-tree (balanced tree).
As we can see, the searched row was found with the minimum read operation by the storage engine otherwise it has to read whole table rows.
What is Non-Clustered Index with Real time example.
The non-Clustered Index is similar to the index of a book.
The index of a book consists of a chapter name and page number, if you want to read any topic or chapter then you can directly go to that page by using the index of that book.
So No need to go through each and every page of a book.
The data is stored in one place, and the index is stored in another place.
Since the data and non-clustered index is stored separately, then you can have multiple non-clustered indexes in a table.