Indexes in database are used to accelerate the data retrieval process. Queries like SELECT and WHERE clauses can be accelerated using indexes but they ultimately slow down UPDATE and INSERT statements. Index creation has no effect on the data present in a table. The CREATE INDEX statement is used create an index; CREATE INDEX statement allows to name the index and also specify the table and columns to the index. Index can prevent duplicate entries in the table.
- Read: What is Index in SQL?
Basic Syntax for CREATE INDEX
CREATE INDEX index_name ON table_name;
The Types of Indexes are
- Single-Column Indexes
- Unique Indexes
- Non-Unique Indexes
- Composite Indexes
- Implicit Indexes
- Bi Directional Indexes
- Partitioned Indexes
- Clustered Indexes
Single-column index is created for the table which has only one column. The basic syntax for single column index is given below
CREATE INDEX index_name
ON table_name (column_name);
Unique indexes are used to increase the data integrity and performance of a table. Insertion of duplicate values inside a table can be prevented by using unique index. The basic syntax for unique index is given below
CREATE UNIQUE INDEX index_name
on table_name (column_name);
- Read: What is database index
Non-unique indexes are used to enforce the constraints on the associated tables. The query performance of non unique index can be improved by maintaining the sorted data values order.
Composite indexes are used in table which contains two or more columns. The basic syntax for composite index is given below.
CREATE INDEX index_name
on table_name (column1, column2);
Queries like SELECT and WHERE clause are commonly used along with the indexes to filter the conditions. Composite index along with WHERE clause is the best choice for tables containing two or more columns.
Indexes which are automatically created by the database are called as implicit indexes. Implicit indexes are automatically created for primary key constraints and unique constraints.
Bi Directional Indexes
Bi-directional indexes are the indexes which allows the scan in both forward and reverse directions. The ALLOW REVERSE SCAN clause is used along with CREATE INDEX clause will enable the forward and reverse scans. The forward and reverse scan will enable the user to perform the following functions.
- Facilitation of MIN and MAX functions
- Fetch Previous Keys
- Eliminates the need of database manager to create a temporary table
- Eliminates redundant reverse order indexes
Partitioned indexes are the indexes which contains the nonpartitioned data. These indexes are utilized to perform roll-in operations in the database. Keywords like ATTACH PARTITION and ALTER are used to perform the roll-in operations.
Indexes with data page order corresponding to the order of rows are called as clustered indexes. Only one clustered index is allowed in a table, the leaf node of a clustered index will correspond to the actual data in the table. The use of pointers in clustered index is not allowed.
Drop Index command
The DROP command can be used to drop the index used in a SQL. DROP command is commonly used when an index slows down the performance of the database. The basic syntax for drop index command is given below.
DROP INDEX index_name;