When use Fill factor on indexen

[INFORMAT]

Hello

I'm using SQl server 7.0 and I want to make indexes on the tables that I have and I have the following questions:

Microsoft does not make a Clustered index automatically on the primary key. I have read that you can make only one clustered index. Is it best that you make the primary key as a clustered index, or is it better that you use a non clustered index ?

Fill factor:
When you have an index with fill factor 70 % and a index with fill factor 30 %. When he split his data pages which fill factor is he using. The last one ? Or is he using a data page for every index ?

Thanks for your time and answer

Devriendt Nico
Informat CV
informat@unicall.be

 

[cdukes]

Yes, you can only have one clustered index on each table as this is used to physically store the data in SQLServers data pages.

BOOK ON LINE :
A clustered index is analogous to a telephone directory, which arranges data by last name. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. However, the index can comprise multiple columns (a composite index), like the way a telephone directory is organized by last name and first name

SQL Server does not automatically put a clusted index on the primary key as this is not always the order in which you want to maintain your data.

Which colummns you use for your clustered index is dependant on how you will be querying your database. If the primary key is used most of the time in queries then it is often best to have a clustered index on it.

As for Fill Factor, each index maintains its own data pages so you will have a fill factor on one index of 70% and a fill factor on the other index of 30%.

Hope this helps

Chris Dukes

 

[foxdev]

To follow up on Chris' comments: clustered indexes are typically best for columns where:

you would typically do range searches (such as a DateTime column)

you often include in the ORDER BY clause

you frequently JOIN on
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org