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,
DROP INDEX Colleges.college_index;
DROP INDEX college_index;
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.