Setting where to leave the gaps in Indexes (fill factors)
-
I'm familiar with the fill fact concept when it comes to creating indexes. Is is possible to specify where to leave the free space? For example if your index includes a date field or an identity field and any new values will be greater than any that already exist in the fields. In this instance you would want the index to have no free space in the pages at the beginning but sparsely populated pages near the end of the index. This way querying is optimised as well as the insertion of new records. Or does SQL server do this anyway?
Also if a integer field has been index doe SQL server leave gaps in the pages for values that are not possible. For example you the values 1 to 10 indexed in a table with a fill factor of 80. Is 10% of free space before the first value and 10% free space after the last value or is free space created between the values? Can you specify that the 20% free space occurs after the last value? This will be useful if you were certain that only positive values were begin entered.
-
AFAIK this is not possible with sqlserver, unless you use partitioned views. i.e. create a table per partition, manage each table's indexes according to the specific tables needs and create a partitioned view to couver all tables for access.
Check out partitioned views in BOL.
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
-
There is no such level of tuning on SQL server indexes. The most you can specify is PAD index to apply that(Fill Factor) to all index pages instead of to the default (leaf level) but over time that is not maintained so you will have to rebuild the index again every time you want to control that.
IMO this is done to maintain the index as compact as possible and only in cases where extreme data modification OLTP is in place will probably be the only applicability for that. Even in that case, like alzdba mentioned, you could partition your tables to control the insertion points (logically) using LPV (local partitioned views)
HTH
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply