Using DEFAULT Constraints in SQL: A Comprehensive Guide

An index is a schema object. It is used by the server to speed up the retrieval of rows by using a pointer. It can reduce disk I/O(input/output) by using a rapid path access method to locate data quickly. An index helps to speed up select queries and where clauses, but it slows down data input, with the update and the insert statements. Indexes can be created or dropped with no effect on the data.

For example, if you want to reference all pages in a book that discusses a certain topic, you first refer to the index, which lists all the topics alphabetically and is then referred to one or more specific page numbers.

The CREATE INDEX Command

The basic syntax of a CREATE INDEX is as follows.

CREATE INDEX index_name ON table_name;

Single-Column Indexes

A single-column index is created based on only one table column. The basic syntax is as follows.

CREATE INDEX index_name
ON table_name (column_name);

Unique Indexes

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows.

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Composite Indexes

A composite index is an index on two or more columns of a table. Its basic syntax is as follows.

CREATE INDEX index_name
on table_name (column1, column2);

Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

DROP INDEX Statement

The DROP INDEX statement is used to delete an index in a table.

MS Access:

Syntax:

<span class="pln">
DROP INDEX index_name ON table_name</span><span class="pun">;</span><span class="pln">
</span>

SQL Server:

Syntax:

<span class="pln">
DROP INDEX table_name</span><span class="pun">.</span><span class="pln">index_name</span><span class="pun">;</span><span class="pln">
</span>

DB2/Oracle:

Syntax:

<span class="pln">
DROP INDEX index_name</span><span class="pun">;</span><span class="pln">
</span>

MySQL:

Syntax:

<span class="pln">
ALTER TABLE table_name
DROP INDEX index_name</span><span class="pun">;</span><span class="pln">
</span>

The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.