ps (7/29/2009)
charipg (7/29/2009)
what is fillfactor and what are the advantages and disadvantages?Refer this BOL link to know about fill factor.
http://msdn.microsoft.com/en-us/library/ms177459.aspx%5B/url%5D
A fill factor of 30 means 30% of space within the data page will be kept vacant, so that when you insert more rows later, they would be accomodated within this free space. This means sql server will not ask for new pages to accomodate your data every time you insert and hence pages will be contiguous, leading to better performance.
Just to continue on Pradeeps response, the disadvantage of a low fill factor is that the index takes up more disk space. the space is allocated ready for the data to go into the page/extent but if you have say 10% fill factor then only 10% is acually used, which means your index will take up 10 times more space than the same index will a fill factor of 100 (or 0, which is the same as 100)
The dissadvantage (which you can probably also work out from Pradeeps response) is that if you have a high fill factor and you have a lot of inserts you will have a lot of page splits which will cause fragmentation (the reason we rebuild/reindex indexes).