Article Index
SQL Tutorial
Microsoft SQL Server
Stored In The Master Database?
Installing A Production Data Server
SQL Server B (Middle Of The Road)
SQL Commands
SQL BETWEEN
SQL Aggregate Functions
SQL ALIAS
SQL Subquery
SQL INTERSECT
Table Manipulation
SQL CREATE INDEX Statement
SQL PRIMARY KEY
SQL UPDATE Statement
Advanced SQL
SQL Running Totals

The same principle applies for retrieving data from a database table. Without an 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 these locations directly to get the needed data. This is much faster.

Therefore, it is often desirable to create indexes on tables. An index can cover one or more columns. The general syntax for creating an index is:

CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)

Let's assume that we have the following table,

TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)

and we want to create an index on the column Last_Name, we would type in,

CREATE INDEX IDX_CUSTOMER_LAST_NAME
on CUSTOMER (Last_Name)

If we want to create an index on both City and Country, we would type in,

CREATE INDEX IDX_CUSTOMER_LOCATION
on CUSTOMER (City, Country)

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.

·    Add a column
·    Drop a column
·    Change a column name
·    Change the data type for a column

Please note that the above is not an exhaustive list. There are other instances where ALTER TABLE is used to change the table structure, such as changing the primary key specification.

The SQL syntax for ALTER TABLE is

ALTER TABLE "table_name"
[alter specification]

[alter specification] is dependent on the type of alteration we wish to perform. For the uses cited above, the [alter specification] statements are:

·    Add a column: ADD "column 1" "data type for column 1"
·    Drop a column: DROP "column 1"
·    Change a column name: CHANGE "old column name" "new column name" "data type for new column name"
·    Change the data type for a column: MODIFY "column 1" "new data type"

Let's run through examples for each one of the above, using the "customer" table created in the CREATE TABLE section:

Table customer

First, we want to add a column called "Gender" to this table. To do this, we key in:

ALTER table customer add Gender char(1)

Resulting table structure:

Table customer

Next, we want to rename "Address" to "Addr". To do this, we key in,

ALTER table customer change Address Addr char(50)

Resulting table structure:

Table customer

Then, we want to change the data type for "Addr" to char(30). To do this, we key in,

ALTER table customer modify Addr char(30)

Resulting table structure:

Table customer

Finally, we want to drop the column "Gender". To do this, we key in,

ALTER table customer drop Gender

Resulting table structure:

Table customer