[dba-SQLServer] HSID database from hell - structure

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



More information about the dba-SQLServer mailing list