jwcolby
jwcolby at colbyconsulting.com
Wed Oct 6 13:03:44 CDT 2010
A while ago it occurred to me that if I have a PKID which is autoincrement and I use that as the key for a unique clustered index, and I insert data into the table in sorted order (on pkid) with the index built before I started, then the database table should grow with minimum fragmentation etc and minimum wasted space. It seems that is in fact the case. I did this, created the table as described. My free space is about 250 megs on a file size of 47.5 gigs, my clustered index has a total fragmentation of .3%, and one of the several indexes (selected at random) I created after creating the table has a total fragmentation of .01%. It appears that as I create new indexes it adds size to the file at the end and stores the new index with virtually no fragmentation and maximum page fullness. So for this specific database / table it seems I have achieved Nirvana. -- John W. Colby www.ColbyConsulting.com