Indexes are used to retrieve the data from the database in a shorter span of time. Users can only speed up searches or queries, they can’t see the indexes.
SQL CREATE INDEX Statement: This CREATE INDEX statement is used to create indexes in tables.
- Syntax of CREATE INDEX: In this duplicate value is allowed, this creates an index on the table.
CREATE INDEX index_name
ON Table_Name (columnA, columnB, ...);
- Syntax of CREATE UNIQUE INDEX: This creates a unique index on the table. Duplicate values are not allowed in this:
CREATE UNIQUE INDEX index_name
ON Table_Name (columnA, columnB, ...);
Note: In different databases, the syntax varies for creating indexes.
Example of CREATE INDEX
The SQL statement given below creates an index named "idx_lastname" on the "Last_Name" column in the "Students" table:
CREATE INDEX idx_lastname
ON Students (Last_Name);
You can list the column names within the parentheses, separated by commas, if you want to create an index on a combination of columns.
CREATE INDEX idx_sname
ON Students (LastName, FirstName);