Indexing in SQL Server 2000

Indexing in SQL Server 2000:

                What is an index?

                                The simplest example for the index is book.  In book have the chapters, it have front page of the index. Using index we can easily get the information (go through that chapter).

                In SQL Server indexing is used to fetch the information in fastest way.

                There are two different types of index’s are available,

i.                     Cluster Index

ii.                   Non Cluster Index

Important points of index:

a.       Retrieve the records from the database with large number of records. If we have minimum level of records could not find any difference.

b.      When we use index we do index DML (Insert, Delete, Update) operation in query, in this case it takes some time. If any DML Operation performed in database that time index has been re arranged (changed from in existing location).

c.       We set index for frequently accessible columns.

d.      Creating index using the below commands,

CREATE INDEX index_name 
ON table_name (column_name1, column_name2...); 

CREATE UNIQUE INDEX index_name 
ON table_name (column_name1, column_name2...); 

e.      Index values are stored in disk. This space occupied based on the table. We can able to view using the below command,

EXEC sp_spaceused