SQL CREATE INDEX

In SQL, if a column has CREATE INDEX constraint, it's faster to retrieve data if we use that column for data retrieval. For example,

-- create table
CREATE TABLE Colleges (
  college_id INT PRIMARY KEY,
  college_code VARCHAR(20) NOT NULL,
  college_name VARCHAR(50)
);

-- create index
CREATE INDEX college_index
ON Colleges(college_code);

Here, the SQL command creates an index named college_index on the Colleges table using the college_code column.

Note: Since database systems are very fast by default, the difference in speed is noticeable only when we are working with a table that has a large number of records..

CREATE UNIQUE INDEX for Unique Values

If we want to create indexes for unique values in a column, we use the CREATE UNIQUE INDEX constraint. For example,

-- create unique index
CREATE UNIQUE INDEX college_index
ON Colleges(college_code);

Here, the SQL command creates a unique index named college_index on the Colleges table using the college_code column.

Note: Although the index is created for only unique values, the original data in table remains unaltered.

Remove Index From Tables

To remove index from a table, we can use the DROP INDEX command. For example,

SQL Server

DROP INDEX Colleges.college_index;

PostgreSQL, Oracle

DROP INDEX college_index;

MySQL

ALTER TABLE Colleges
DROP INDEX college_index;

Here, the SQL command removes the college_index constraint from the Colleges table.

Note: Deleting an index means the index is only deleted. The data in the original table remains unaltered.