What is SQL Indexes?
SQL Indexes are used to increase the performance of data extract from the database object such as table or view.
We can not see SQL indexes physically but virtually it's there in the database.
When it comes to performance tuning of the database object, we may consider applying SQL indexes on tables or views.
SQL Indexes are stored in special lookup table which is used by database search engine to speed up the data retrieval process of the table or view.
Each SQL Index create a pointer to each row of the table.
SQL Indexes speed up the SELECT and WHERE queries operation but simultaneously slows down the INSERT and UPDATE queries operation.
If you delete an index of the table, there will be no effect on data.
You can apply SQL Index on multiple columns in a table and such index is called as Composite Index.
You may consider such columns which are frequently used in WHERE clause as a filter condition.
SQL Indexes can be unique using UNIQUE constraint to prevent the duplicate entries while inserting data in table.
Some indexes are automatically created by database server when object is created, such indexes are called as Implicit Indexes. When the primary key and unique key has applied on column that time Implicit indexes are created automatically.
Following is the syntax and example of index creation. In example student table is used and index is created on student_name column.
SQL Indexes: Syntax and Example
1) Following is the syntax and example of SQL Index creation. Only one column is used to create an index. It is also known as single column index. The example is based upon student table and index is created on student name.
Syntax: CREATE INDEX index_name ON table_name(column_name);
Example: CREATE INDEX student_index ON student(student_name);
2) Following is the syntax and example of composite index i.e. also know as multi column index. When two or more column used to define an index, is called as Composite Index. The example is based upon employee table and index is created on employee number and name.
Syntax: CREATE INDEX index_name ON table_name(column_name1, column_name2);
Example: CREATE INDEX employee_index ON employee(emp_no,emp_name);
3) Following is the syntax and example of Unique Index. Unique Index is to prevent the duplicate entries while inserting data in table. It is used not only to increase the performance but also for data integrity. It will not allow to insert duplicate values. The example is based upon employee table and index is created on employee number.
Syntax: CREATE UNIQUE INDEX index_name ON table_name(column_name);
Example: CREATE INDEX employee_index ON employee(emp_name);
How to drop an Index?
Following is the syntax for dropping an index from table. This will delete an index permanently, applied on table.
Syntax: DROP INDEX index_name;
Example: DROP INDEX employee_index;
