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.