Web | This Site

Learning Basic SQL
Advanced SQL
SQL Functions
SQL Useful Resources
Selected Reading
© 2011 TutorialsPoint.COM

| Home |  | References |  | Discussion Forums |  | About TP

# SQL - INDEX Constraint

The INDEX is used to create and retrieve data from the database very quickly. Index can be created by using single or group of columns in a table. When index is created it is assigned a ROWID for each rows before it sort out the data.

Proper indexes are good for performance in large databases but you need to be careful while creating index. Selection of fields depends on what you are using in your SQL queries.

# Example:

For example, the following SQL creates a new table called CUSTOMERS and adds five columns:

    CREATE TABLE CUSTOMERS(
           ID   INT              NOT NULL,
           NAME VARCHAR (20)     NOT NULL,
           AGE  INT              NOT NULL,
           ADDRESS  CHAR (25) ,
           SALARY   DECIMAL (18, 2),
           PRIMARY KEY (ID)
    );

Now you can create index on single or multiple columns using the folloiwng syntax:

    CREATE INDEX index_name
        ON table_name ( column1, column2.....);

To create an INDEX on AGE column, to optimize the search on customers for a particular age, following is the SQL syntax:

    CREATE INDEX idx_age
        ON CUSTOMERS ( AGE );

# DROP a INDEX Constraint:

To drop a INDEX constraint, use the following SQL:

    ALTER TABLE CUSTOMERS
       DROP INDEX idx_age;

Advertisement

Online Image Processing

Indian Baby Names