Indexes in databases are very similar to indexes in libraries. Indexes allow locating information within a database fast, much like they do in libraries. If all books in a library are indexed alphabetically then you don’t need to browse the whole library to find particular book. Instead you’ll simply get the first letter from the book title and you’ll find this letter’s section in the library starting your search from there, which will narrow down your search significantly.
An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order. The table index has pointers to the values stored in specified column or combination of columns of the table. These pointers are ordered depending on the sort order specified in the index.
Here is how to use CREATE INDEX SQL statement to create an index on column Model in the Product table, called idxModel:
CREATE INDEX idxModel
ON Product (Model)
The syntax for creating indexes varies greatly amongst different RDBMS, that’s why we will not discuss this matter further.
There are some general rules which describe when to use indexes. When dealing with relatively small tables, indexes do not improve performance. In general indexes improve performance when they are created on fields used in table joins. Use indexes when most of your database queries retrieve relatively small datasets, because if your queries retrieve most of the data most of the time, the indexes will actually slow the data retrieval. Use indexes for columns that have many different values (there are not many repeated values within the column). Although indexes improve search performance, they slow the updates, and this might be something worth considering.
|
| Author: Milind Kansagara 24 Jun 2009 | Member Level: Gold Points : 0 |
NIce
|
| Author: Manigandan 25 Jun 2009 | Member Level: Gold Points : 2 |
Having a data connection in the Server Explorer view of Visual Studio.NET allows us to easily create new indexes:
* Navigate to the Products table of the Any database.
* Right click the table and select Design Table from the context menu.
* With the design screen in focus, click the Indexes/Keys item on the View menu of the IDE.
This should bring you to the following tabbed dialog box.
(Attached Fig)
The dialog is currently displaying an existing index on the Products table: the PK_Products index. We will see later in this chapter how primary key fields are automatically indexed to enforce uniqueness in the key values.
* In the above dialog click on the New button, and in the Index name text box, replace the existing entry with IDX_UnitPrice.
* Beneath the text box is a control where we set the columns to index. Pull down the entry with ProductID and select the UnitPrice column instead.
* Leave all of the other options with default settings.
* Close the dialog and the table design view, making sure to save all of the changes when prompted to do so. The IDE will then issue the commands to create the new index.
We can create the same index using the following SQL. The command specifies the name of the index (IDX_UnitPrice), the table name (Products), and the column to index (UnitPrice).
$$CREATE INDEX [IDX_UnitPrice] ON Products (UnitPrice)
For executing,
$$EXEC sp_helpindex Customers
 |