Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt
You are on page 1/ 6
Indexing:
Indexing is a technique to locate and access data in databases. It optimizes querying speed by serving as an organized lookup table with pointers on location.
There are two types index in Databases:
Clustered Indexing: A clustered index defines the order in which data is stored in table. A table can be clustered indexed on any specified column. By default the primary field in table will be chosen for clustered indexing as you can see IDs’ will be in ascending order in any table.
Results on querying a table on clustered index is comparatively faster compared to other fields that exists in the table.
I mean SELECT * FROM EMPLOYEE WHERE EMPLOYEE_ID = 007 is faster than
SELECT * FROM EMPLOYEE WHERE EMPLOYEE_NAME = “James Bond”
This is because the data is clustered w.r.t ID field alone in a tree structure.
There should only be one clustered index on a table and it may contain as many as columns a user require, such indexes are called composite clustered index.
Syntax:
CREATE CLUSTERED INDEX <index_name> ON <table_name>(column1 DESC, column2 ASC)
In the above syntax if we give GENDER and SALARY as index then data will be clustered as per those two columns only In the above figure we can see ID column is not in order and the data is sorted by SALARY in ascending order and GENDER in Descending order. NonClustered Index: It is like a content page in every book which gives the page information about every chapter. The data will be sorted w.r.t the key values (column values). These key values are stored in a B-Tree like structure.
The index will have pointers to the storage location of the data. The data is stored in one place and index in another place, which mean the data format will not be changed in the quey result but it is definitely faster compared to other columns which do not have any index. Since the index information is stored in some other place there can be more than one Non clustered Index.
In case of the Non clustered index the intermediate nodes stores the physical location of the rows in the table. So here we don’t search till leaf node level but fetch the rows as per locations stored in the intermediate nodes level.
Syntax: CREATE NONCLUSTERED INDEX <index_name> on <table_name>(column1, column2)
Data will be fetched in three steps
1. Index Seek on non clustered index
2. Key lookup on clustered index 3. Nested loops: for each row in outer input, scan the inner input and output matching rows
Filtered Index: Filtered Index will have where condition restricting the data. A clustered index cannot be filtered because it must have all the data
Syntax: CREATE NONCLUSTERED INDEX <index_name> on <table_name>(column1, column2…)
