INDEXES

Indexes are created on one column or more columns for speedy retrieval of data. Creating an index on a column ensures god response times for database transactions. If a table does not have an index the results in to a sequential scan on a table which is the most costly database operation in the RDBMS.
The time it takes to perform this scan is directly proportional to the actual number of records in that table.

Creating Indexes
An index is created using CREATE INDEX command.

Syntax
CREATE INDEX index_name on table_name (column name)

Example
Create Index cust_name on customer(cust_name)

Types of Indexes
1.Clustered Indexes
2.NonClustered Indexes
3.Composite Indexes.

Clustered Indexes
is one in which the order of the keys in the index is the same as the physical order, so there can be only one clustered index per table. We will get a clear picture if we take the following example, think you are on a vacation to a big resort which has 1000 houses spreaded all over the resort and you got house no 250, but the house 250 may be in between the 450 and 324 ie houses are not in an order. Now it becomes like a game to find house hunting down all the homes until you get your house number. Here the houses are all around the places ie the data is not in an order. If they numberd the houses in an order then finding the house could have been so pleasent. So in the table if we have clustered index the retrieval will be faster because the data is ordered.

Per example if you are finding maximum of column which has clustered index on it then it will be faster because as the data is order the maximum value is at the end of the table. If you have some other indexes it has to some work to find the maximum value.

Non-Clustered Indexes
are dense indexes, meaning that every key value is stored in the index. this is because the rows do not occur in any particular order. As a result they are larger than clustered indexes. They are typically defined on foreign keys, and the keys can be up to 16 colkumns with a total key length of 255 char. Easy way to explain the Non clustered index is nothing but a book. We will take Best Resorts in the world in which it has 1000 pages, somebody told FUJI is so good so to get more information instead of going through the book page by page we can go the back of the book and search in 'F' page we will get the page number so that we can directly jump to that page ie Non-Clustered Index.

Difference between Clustered index and Non Clustered Index.

Clustered Index

Non Clustered Index

Index Page

Composite Indexes
Indexes are also can be created on multiple columns, These are called composite indexes. Up to 16 columns from one table can be combined to perform a composite index.

Enforcing Uniqueness
Uniqueness on a Key is enforced by specifying the UNIQUE statement in the CREATE index command. In a unique index column no two rows can have same index value. An attemtn to create a duplicate record falis during an INSERT or UPDATE statements.

Example
CREATE UNIQUE INDEX au_ind
on PUBLISHER

Help on indexes.
Using sp_help system stored procedure you can view the information about any object in the database.
Syntax
SP_HELP < index object name >
Example
If you want to see the index name ind_customer then
sp_help ind_customer

Dropping an Index
You can drop an index from any table using DROP INDEX command.
Syntax
DROP INDEX <index_name>
Example
drop index customer_ind