SQL Index are special object built on top of the table. Indexes are created on columns of a table. One table can have more than one indexes.Data from databases could be retrieved fast using SQL indexes.Indexes are not visible and they are only used to speed up queries.
Note: A table with a Index takes more time to update ,Because index also updates itself . It is to be recommended that indexes for only those columns are made which are important and frequently searched.
SQL INDEX /CREATE:
SQL index could either be created or can be dropped. let us look at the combination of SQL Index with CREATE and DROP command.
Note: The CREATE INDEX command is not a part of the ANSI SQL standard, and thus its syntax varies among various platforms.
SQL CREATE INDEX:
- Following statement mentioned below , creates an index on a table
- Duplicate Values in Columns are allowed
CREATE INDEX index_name ON table_name (column1, column2, ...);
SQL CREATE UNIQUE INDEX:
- UNIQUE INDEX is same as of ‘INDEX’ ; It do not allows duplicate values in the column. rest everything is same between them.
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);
DROP INDEX Statement
Drop Index is used to delete index inside a table. [DROP–.>delete index]
Note: The DROP INDEX command is not a part of the ANSI SQL standard, and thus its syntax varies among various platforms.
Syntax:
MySQL version:
ALTER TABLE table_name DROP INDEX index_name;
Oracle version:
DROP INDEX index_name;
SQL Server:
DROP INDEX table_name.index_name;
MS Access:
DROP INDEX index_name ON table_name;
Indexes are not supposed to be used in following conditions:
- Indexes are not suppose to be used for small tables
- columns with high number of ‘NULL’ values should not be indexed.
- Columns that are frequently changed are not supposed to be indexed.
This was all for SQL INDEX , Drop your doubts and queries in the comment section down below.