Back
Next
SQL Tutorial will describe us that
SQL
Indexes help us to retrieve data from tables quicker.
Let's use an example to illustrate this point; Suppose we
are interested in reading about how to grow peppers in
a gardening book. Instead of reading the book from
the beginning until we find a section on peppers (Glossary), it
is much quicker for us to go to the index section at
the end of the book, locate which pages contain
information on peppers, and then go to these pages
directly. Going to the index first saves us time and
is by far a more efficient method for locating the
information we need.
The same principle applies for retrieving data from
a database table. Without an SQL Index, the database system
reads through the entire table this process is called
a 'table scan' to locate the desired information. With
the proper index in place, the database system can then
first go through the index to find out where to retrieve
the data, and then go to that location directly to get
the needed data. This is much faster due to the SQL Index.
Therefore, it is often desirable to create SQL Indexes on
tables. An SQL index can cover one or more columns.
The general syntax for creating an SQL index is as under.
Syntax for SQL Create Index
CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)
Let's assume that we have the following table structure in our database,
TABLE Employees
(FirstName char(50),
LastName char(50),
Email char(25),
Address char(50),
City char(50),
Country char(25),
DOB date)
and we want to create an index on the column FirstName, we would write below
query,
Example of SQL Create Index
CREATE INDEX Index_Employees_FirstName
on Employees (FirstName)
If we want to create an SQL Index on both Email and Address, we would follow
the below query,
CREATE INDEX Index_Employees_Location
on Employees (Email, Address)
There is no strict rule on how to name an SQL Index.
The general using method is to place a prefix,
such as "Index_", before creating an SQL Index name to avoid confusion
with other database objects such as tables
views and others. It is also a good idea to
provide information on which table and column or columns the
index is used on.
SQL Tutorial ends this segment of SQL Create Index here and hops it is enough
for experiments now.
Please note that the exact syntax for SQL CREATE INDEX may
differ for different databases. We should consult
with our database reference manual for the precise syntax.
Back
Next
| 
Home 