SQL Index

SQL is a structured Query language and is the standard language that is supported by just about every database Product.

Indexing is a way of sorting a number of records on multiple fields. The Index Structure is sorted, allowing Binary Searches to Be Performed on it. Having an index is to speed up Search queries by essentially cutting down the number of records/rows in a table that need to be examined. Its Improves Performance. An Index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.


CREATE INDEX index_name ON table_name (Column_Name);

CREATE INDEX index_name ON table_name (Column1,Column2);

Indexes can also be Unique, Similar to UNIQUE Constraint. Its Prevents duplicate Entries in Column or Combination of columns.

CREATE UNIQUE INDEX index_name ON table_name(Column_name);

Types of Index :
1) A Clustered index determines the order in which the rows of the table will be stored on disk- and it actually stores row level data in the leaf nodes of the index itself. A non-clustered index has no effect on which the order of the rows will be stored.
2) A Table can have multiple non-clustered indexes. But, a table can have only one clustered index. Whenever Table Create with Primary key automatically Primary Clustered Index Created on That Primary Column of that Table.

The Downside to indexing is that these indexes Require additional space on the Disk. Another Problem with indexes is their fragmentation over time as data is inserted/deleted/updated. REORGANIZE helps, you must write routines to have it done.