SQL CREATE INDEX

Indexes

The indexes are special objects which built on top of tables. The indexes can do an operation like SELECT, DELETE and UPDATE statement faster to manipulate a large amount of data. An INDEX can also be called a table and it has a data structure. An INDEX is created on columns of a table. One table may contain one or more INDEX tables.

Note: The CREATE INDEX command is not a part of the ANSI SQL standard, and thus its syntax varies among vendors.

What does SQL INDEX do?

The SQL INDEX does the following :

• INDEXES can locate information within a database very fast.

• An INDEX makes a catalog of rows of a database table as row can be pointed within a fraction of the time with a minimum effort.

• A table INDEX is a database structure which arranges the values of one or more columns in a specific order.

• The performance of an INDEX can not be recognized much when dealing with relatively small tables.

• INDEX can work properly and quickly for the columns that have many different values.

• It takes a long time to find an information for one or combination of columns from a table when there are thousands of records in the table. In that case, if indexes are created on that column, which are accessed frequently, the information can be retrieved quickly.

• The INDEX first sorts the data and then it assigns an identification for each row.

• The INDEX table having only two columns, one is a rowid and another is indexed-column (ordered).

• When data is retrieved from a database table based on the indexed column, the index pointer searches the rowid and quickly locates that position.in the actual table and display, the rows sought for.

Syntax:

CREATE [UNIQUE] INDEX <index name> ON <table name> (<column(s)>);

Parameters:

How to distinguish between index and views

VIEW:

• A VIEW is a data object which contains no data. Its contents are the resultant of the base table. They are operated just like the base table but they don’t contain any data of their own.
• A VIEW is similar to a table but may contain data from one or more tables connected with each other through a common set of criteria.
• A VIEW is a physical object but it is a logical table. A VIEW just refers to data which stored in base tables.
• A VIEW is also one of the database objects.
• A VIEW can be used in any SELECT statement like a table.
• A VIEW provides security for both data and table of a database. Suppose by mistake a table is dropped, it can't be recovered. but if a view dropped, it can be created again.

INDEX:

• An INDEX is also a table. So it has a data structure.
• INDEXES are pointers that represent the physical address of a data.
• An INDEX is created on columns of a table.
• An INDEX makes a catalog based on one or more columns of a table.
• One table may contain one or more INDEX tables.
• An INDEX can be created on a single column or combination of columns of a database table.

Example : SQL CREATE INDEX

Sample table : customer

To create an index on 'custcity' column of the table 'customer', the following SQL statement can be used :

CREATE  INDEX custcity 
ON customer(cust_city);

Output:

SQL CREATE INDEX using more columns

To create an index on combination of 'custcity' and 'cust_country' columns of the table 'customer', the following SQL statement can be used :

CREATE  INDEX custcity_country
ON customer(cust_city,cust_country);

Output :

SQL CREATE UNIQUE INDEX

A UNIQUE INDEX comes only once in a table. This INDEX assures that value of a column or value of a combination of one or more columns can not appear more than once in a table.

Example :

Sample table: customer

To create a unique index on 'cust_code' column in the table 'customer', the following SQL statement can be used :

CREATE  UNIQUE INDEX custcode 
ON customer(cust_code);

Output:

Create Index in MySQL, PostgreSQL, Oracle, SQL Server

Create Index in MySQL [5.7]

Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.

Syntax:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] 
    [algorithm_option | lock_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

algorithm_option:
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}:

Create Index in PostgreSQL 9.3.13

In PostgreSQL CREATE INDEX command constructs an index on the specified column(s) of the specified relation, which can be a table or a materialized view. Indexes are primarily used to enhance database performance (though inappropriate use can result in slower performance).

Syntax:

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

Create Index in Oracle 11g

In Oracle CREATE INDEX statement is used to create an index on:

• One or more columns of a table, a partitioned table, an index-organized table, or a cluster
• One or more scalar typed object attributes of a table or a cluster
• A nested table storage table for indexing a nested table column

Syntax:

CREATE [ UNIQUE | BITMAP ] INDEX [ schema. ] index
  ON { cluster_index_clause
     | table_index_clause
     | bitmap_join_index_clause
     }
[ UNUSABLE ]

Create Index in SQL Server 2014

In SQL Server CREATE INDEX command creates a relational index on a table or view. Also called a rowstore index because it is either a clustered or nonclustered btree index. You can create a rowstore index before there is data in the table. Use a rowstore index to improve query performance, especially when the queries select from specific columns or require values to be sorted in a particular order.

Syntax:

-- SQL Server Syntax
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
  ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
  [ INCLUDE ( column_name [ ,...n ] ) ]
  [ WHERE <filter_predicate> ]
  [ WITH ( <relational_index_option> [ ,...n ] ) ]
  [ ON { partition_scheme_name ( column_name ) 
  | filegroup_name 
  | default 
  }
  ]
  [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
[ ; ]
<object> ::=
  {
  [ database_name. [ schema_name ] . | schema_name. ] 
  table_or_view_name
  }
<relational_index_option> ::=
  {
  PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE} 
  [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
  [ , ...n ] ) ]
  }
<filter_predicate> ::= 
  <conjunct> [ AND <conjunct> ]
<conjunct> ::=
  <disjunct> | <comparison>
<disjunct> ::=
  column_name IN (constant ,...n)
<comparison> ::=
  column_name <comparison_op> constant
<comparison_op> ::=
  { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }
<range> ::= 
  <partition_number_expression> TO <partition_number_expression>
Backward Compatible Relational Index
  Important   The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. Use the syntax structure specified in <relational_index_option> instead.
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
  ON <object> ( column_name [ ASC | DESC ] [ ,...n ] ) 
  [ WITH <backward_compatible_index_option> [ ,...n ] ]
  [ ON { filegroup_name | "default" } ]
<object> ::=
  {
  [ database_name. [ owner_name ] . | owner_name. ] 
  table_or_view_name
  }
<backward_compatible_index_option> ::=
  { 
  PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE 
  | DROP_EXISTING 
  }

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Update View
Next: Alter Index