SQL Create Index, SQL Tutorial, Global Guide Line Technology.
Bookmark Bookmark: SQL Create Index, SQL Tutorial Forum | Blog |

      Home                    

Basic SQL Guide.           
SQL Introduction.               
 What is Table?                  
 SQL Create Table.            
 SQL Insert Statement.      
 SQL Select Keyword.        
 SQL Order by clause.        
 SQL Count Statement.      
 SQL Group By Clause.      
 SQL Having Clause.          
 SQL Alias Tables.             
 SQL Join Tables.              
 SQL Outer Join.               
 SQL Update Statement.   
 SQL Delete Statement.    
 SQL Distinct Keyword.      
 SQL Where Keyword.       
 SQL And Or Keyword.      
 SQL In Keyword.              
 SQL Between Keyword.   
 SQL Like Keyword.          
 SQL Functions.                
 SQL Concatenate.           
 SQL Substrings.               
 SQL Trim Function.         
 SQL Constraints.              
 SQL Primary Key.            
 SQL Foreign Key.            
 SQL Create Views.           
 SQL Create Index.
 SQL Alter Table.             
 SQL Drop Table.             
 SQL Truncate Table.      
 SQL Summary.                

SQL Tutorial >> SQL Create Index.


     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     
 

[ About ] [ Contact ] [ Home ]
[ Links ] [ Site Map ] [ Services ] [ Privacy ]

Copyright 2005 -  2018 www.globalguideline.com All rights reserved. (Best viewed in IE 6.0+ or Firefox 2.0+ at 1024 * 768 or higher) 224 visitors are online now