What's the content of the index data pages ?

[INFORMAT]

Hello,

Can you tell me if I'm with the following correct:


When you create a clustered index,then is the data page
equal to the physical data pages.So if you create a
clustered index with the fill factor 80 % the physical
data pages are 80 % full.

When you create a non clustered index,the data pages
from the non clustered index exist of the fields that
you have defined in the index and it use the clustered
index to go to the physical data page.

But what's the content of the data pages from a non
clustered index an when there is no clustered index ?

When you insert a record in de table, is this record
inserted in all the index data pages ?

I hope you can help me, my biggest problem is that I
don't know exactly what's the content of the index data
pages.



Thanks for your time and answer

Devriendt Nico
Informat CV
informat@unicall.be

 

[Ruairi]

The index pages for a non clustered index contain the index field(s) and a pointer to the record's physical location. If you don't have clustered index there is no specific order to how the records are stored. That means that without the clustered index SQL Server has to scan the entire index, doing a comparison on each record. There are very few specialized cases where you wouldn't want a clustered index. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[Ruairi]

Oh yeah, in a clustered index you are right, the index page is the data page. If you specify a fill factor of 80% the data pages will be 80% full when the index is built. This fill factor is not maintained over time. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com