Index Page VS Data Page
-
Hai,
I got confused with Index Page and Data pages,
is it both are same ?
is it that both Clusterd and non clustered indexes have index pages ?
Fill factor is associated with index page only right ?
Can anybody clarify my points ?
-
Check
- Table and Index Architecture in BOL
- http://www.sqlservercentral.com/columnists/chedgate/clusterthatindex.asp
- http://www.sqlservercentral.com/columnists/sjones/sqlserver2000indexing.asp
- http://www.sqlservercentral.com/columnists/dharris/indexesanoverviewandmaintenanceforperformance.asp
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
No, data pages and index pages are two different kind of things. Actually there are several different kinds of index pages.
- Leaf level für Nonclustered Indexes
- Non-Leaf level for Nonclustered and Clustered Indexes
- There is no Leaf level index page structure für Clustered Indexes, because at the Leaf level of a Clustered Index _are_ the data pages.
Fillfactor only apply to index pages. But in case of a Clustered Index it also affect the data pages, because...see above
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
so, i am understanding that, Non clustered index will have an index page, which they will refer to find the location of data page in the heap.
Clustered index will not have an index page, since the data pages are odered physically.
am i right ?
-
It is my understanding that Clustered Indexes *do not have* index pages at the leaf level (you can find there the data pages instead), but Clustered Indexes *do have* index pages for all non-leaf levels.
But this has nothing to do with the physical ordering per se.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
OK agreed.
But the fill factor which we used to specify during the index creation or index rebuilding is for the index pages right ? not for the data pages
am i correct ?
-
Close, FILLFACTOR apply to both kind of indexes no matter if index page = index page or index page = data page. See, if this helps:
http://www.sqlservercentral.com/columnists/gjackson/whocaresaboutfillfactor.asp
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
An other difference is that nonclustering indexes have the actual RID (page-row-address) when there is no clustered index defined for the object.
If there is a clustered index defined, it contains a uniquified address which refers to the clustered index key.
Uniquified = added something to the clustered index key column(s) if it's not defined unique by itself. That's the reason why clix-keys should be kept as short as possible.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply