quote:

---

Originally posted by Kristen

"you would have to reload the table to increase the page density"

Blimey, I never knew that - is there no way to defrag such a table then? (other than "reload")

... mind you, not sure why I'm worrying, we always have a clustered index ... but I suppose I ought to add a Query to me "Make a Release Version" process to list any table that does NOT have a C.I. ...

Kristen

---

print ''
print '-- Create a heap table'
print ''
create table NUM ( NUMBER int not null ) 

print ''
print '-- Insert data'
print ''
insert into  NUM (NUMBER)
select	top 100 percent
	NUMBER
from
	F_TABLE_NUMBER_RANGE(1,100000)
order by
	NUMBER

print ''
print '-- showcontig before delete'
print ''
dbcc showcontig ( NUM )

print ''
print '-- Delete 99% of the rows'
print ''
delete from NUM
where
	NUMBER%100 <> 0

print ''
print '-- showcontig before dbreindex'
print ''
dbcc showcontig ( NUM )

print ''
print '-- Run dbreindex'
print ''
dbcc dbreindex ( NUM ,'',90)

print ''
print '-- showcontig after dbreindex'
print ''
dbcc showcontig ( NUM )

drop table NUM

 
-- Create a heap table
 
 
-- Insert data
 

(100000 row(s) affected)

 
-- showcontig before delete
 
DBCC SHOWCONTIG scanning 'NUM' table...
Table: 'NUM' (546817010); index ID: 0, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 179
- Extents Scanned..............................: 24
- Extent Switches..............................: 23
- Avg. Pages per Extent........................: 7.5
- Scan Density [Best Count:Actual Count].......: 95.83% [23:24]
- Extent Scan Fragmentation ...................: 8.33%
- Avg. Bytes Free per Page.....................: 833.4
- Avg. Page Density (full).....................: 89.70%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
-- Delete 99% of the rows
 

(99000 row(s) affected)

 
-- showcontig before dbreindex
 
DBCC SHOWCONTIG scanning 'NUM' table...
Table: 'NUM' (546817010); index ID: 0, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 173
- Extents Scanned..............................: 24
- Extent Switches..............................: 23
- Avg. Pages per Extent........................: 7.2
- Scan Density [Best Count:Actual Count].......: 91.67% [22:24]
- Extent Scan Fragmentation ...................: 8.33%
- Avg. Bytes Free per Page.....................: 7069.2
- Avg. Page Density (full).....................: 12.66%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
-- Run dbreindex
 
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
-- showcontig after dbreindex
 
DBCC SHOWCONTIG scanning 'NUM' table...
Table: 'NUM' (546817010); index ID: 0, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 173
- Extents Scanned..............................: 24
- Extent Switches..............................: 23
- Avg. Pages per Extent........................: 7.2
- Scan Density [Best Count:Actual Count].......: 91.67% [22:24]
- Extent Scan Fragmentation ...................: 8.33%
- Avg. Bytes Free per Page.....................: 7069.2
- Avg. Page Density (full).....................: 12.66%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.