It actually creates a lookup table with column and a pointer to the memory address where a row with this column is actually stored. B Tree data structure is mostly used to store in the indexes. Because of time efficiency in B Trees.
Types of Indexing
Primary Index
Primary Index is an ordered file which is fixed length size with two fields. The first field is the same a primary key and second, filed is pointed to that specific data block. In the primary Index, there is always one to one relationship between the entries in the index table.
Primary Index (Ordered Indexing) is divided into two types
- Dense Index
- Sparse Index
Dense Index
In a dense index, a record is created for every search key valued in the database. This helps you to search faster but needs more space to store index records. In this Indexing, method records contain search key value and points to the real record on the disk
Sparse Index
It is an index record that appears for only some of the values in the file. Sparse Index helps you to resolve the issues of dense Indexing in DBMS. In this method of indexing technique, a range of index columns stores the same data block address, and when data needs to be retrieved, the block address will be fetched.
Secondary Index
Secondary index may be generated from a field which is a candidate key and has a unique value in every record, or a non-key with duplicate values. It is also known as a non-clustering index.
Clustering Index
In a clustered index, records themselves are stored in the Index and not pointers.
Sometimes the Index is created on non-primary key columns which might not be unique for each record. In such a situation, you can group two or more columns to get the unique values and create an index which is called clustered Index. This also helps you to identify the record faster.
How we create an index in SQL?
Single column index syntax
CREATE INDEX index_name ON TABLE_NAME (COLUMN_NAME);
CREATE INDEX idx_phone ON EMPLOYEES (PHONE_NUMBER);
Single column index syntax
CREATE INDEX index_name ON TABLE_NAME (COLUMN_NAME1, COLUMN_NAME2,.. COLUMN_NAME);
CREATE INDEX idx_emp_name ON EMPLOYEES (FIRST_NAME, LAST_NAME);