INDEX: SQL Tutorial

SQL INDEX – Free SQL Tutorials

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.

Leave a Comment

Your email address will not be published. Required fields are marked *