Recently, I was asked this question "If I have a compressed index and I rebuild it with fill factor 70%, do I actually get any fill factor? Or does compression take away the empty space?". The answer to this question is 'yes'. In fact all index options listed below available with data compression (both for ROW and PAGE)
<relational_index_option> ::={PAD_INDEX = { ON | OFF }| FILLFACTOR = fillfactor| SORT_IN_TEMPDB = { ON | OFF }| IGNORE_DUP_KEY = { ON | OFF } | STATISTICS_NORECOMPUTE = { ON | OFF }| DROP_EXISTING = { ON | OFF }| ONLINE = { ON | OFF }| ALLOW_ROW_LOCKS = { ON | OFF }| ALLOW_PAGE_LOCKS = { ON | OFF }| MAXDOP = max_degree_of_parallelismHere is a simple script to show the 'fill factor" experimentcreate table t1 (c1 int, c2 char (1000))go
-- load 10000 rowsdeclare @i intselect @i = 0while (@i < 10000) begininsert into t1 values (@i, REPLICATE ('1', 500))set @i = @i + 1end
-- create index with 40% fill factorcreate clustered index ci on t1 (c1) with (fillfactor = 40)
-- run this DMV query to get the info on fill factor, number of pagesselect max_record_size_in_bytes,page_count, avg_page_space_used_in_percentfrom sys.dm_db_index_physical_stats (db_id('foo'), object_id('foo.t1'), null, null, 'DETAILED')where object_name (object_id) like 't1'
max_record_size_in_bytes page_count avg_page_space_used_in_percent------------------------ -------------------- ------------------------------1011 2500 50.0370644922165
-- enable ROW compression.Note, it will maintain the fill factor or 40%alter index ci on t1 rebuild with ( data_compression = ROW)
-- The output below shows almost ½ the page couns but fill factor is around 40%
max_record_size_in_bytes page_count avg_page_space_used_in_percent------------------------ -------------------- -----------------------------511 1430 44.2960588089943
-- enable PAGE compression. Note, it will maintain the fill factor or 40%alter index ci on t1 rebuild with ( data_compression = PAGE)
max_record_size_in_bytes page_count avg_page_space_used_in_percent------------------------ -------------------- ------------------------------9 42 38.6835680751174
ThanksSunil Agarwal