SQL Server Create Index | Create Index Statement in SQL - sql - sql tutorial - learn sql




 create index statement
  • A Table index helps SQL statements run faster.
  • The CREATE INDEX statement is used to create indexes in tables.
  • Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries.
  • Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against

The Syntax for creating an index is:

CREATE INDEX "index_name" ON "table_name" (column_name);

 create index
  • Note that an index can only cover one table. We cannot build an index that covers multiple tables.
  • Let's assume that we have the following table,

Table Employee

Column Name Data Type
First_Name char(50)
Last_Name char(50)
Address char(50)
City char(50)
Country char(25)
Birth_Date datetime
  • and we want to create an index on the column Last_Name, we would type in,
CREATE INDEX IDX_ Employee_LAST_NAME
ON Employee(Last_Name);
  • If we want to create an index on both City and Country, we would type in,
CREATE INDEX IDX_ Employee_LOCATION
ON Employee(City, Country);
  • A simple index is an index on a single column, while a composite index is an index on two or more columns.
  • In the examples above, IDX_ EMPLOYEE_LAST_NAME is a simple index because there is only one column, while IDX_ EMPLOYEE_LOCATION is a composite index because there are two columns.
  • There is no strict rule on how to name an index. The generally accepted method is to place a prefix, such as "IDX_", before an index name to avoid confusion with other database objects
  • It is also a good idea to provide information on which table and column(s) the index is used on.
  • Please note that the exact syntax for CREATE INDEX may be different for different databases. You should consult with your database reference manual for the precise syntax.

Use of index in SQL:

  • An index is used to speed up the performance of queries.
  • It does this by reducing the number of database data pages that have to be visited/scanned.
  • In SQL Server, a clustered index determines the physical order of data in a table.
  • There can be only one clustered index per table (the clustered index IS the table)
 index in sql use



This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Create Index Statement in SQL