can you say what is the use of index in SQL server 2000?

[raghunadhs]

Hi,
could u pls illustrate what is the use of creating index in Sql Server 2000? Is it used to create a unique key for each row in a table ? or is there any other special reason for that?

I tried an example by seeing help. the index is starting from 1. can't i start the index from my desired value like 1000?

The question what i am posting may be silly one, but really i don't know. please help me if u know it.

Thanks & regards,
raghunadhs

[szlamany]

Indexes serve many purposes.

A primary key index is almost required in order to have some unique field in which to identify one single row. If a column already in the table cannot be used for this purpose sometimes it makes sense to create an identity column - where the key value is automatically assigned when rows are added. You can create this identity rule for your primary key to start at any value and increment by any value for subsequent adds.

Other alternate key indexes can be added to a table as well. Sometimes to force uniqueness - but not always...

We put many alternate key indexes on table columns because those columns are used in JOIN's to other tables when we write queries. This speeds up the JOIN operation.

Indexes on a column allow for the "fastest" possible "finding" of a value in that column.

[Hack]

We put many alternate key indexes on table columns because those columns are used in JOIN's to other tables when we write queries. This speeds up the JOIN operation.

Indexes on a column allow for the "fastest" possible "finding" of a value in that column.

I'm sure the other reasons szlamany mentioned are near and dear to the heart of a DBA, however, these two are the ones that are near and dear to a programmer!

[raghunadhs]

Hi szlamany,
Really it is a good explenation. Thank you very much.
regards,
raghunadhs

[QUOTE=szlamany]Indexes serve many purposes.

A primary key index is almost required in order to have some unique field in which to identify one single row. If a column already in the table cannot be used for this purpose sometimes it makes sense to create an identity column - where the key value is automatically assigned when rows are added. You can create this identity rule for your primary key to start at any value and increment by any value for subsequent adds.