Import Staging Table Fragmentation

[BigMickeyD]

I'm using DTS to import a table from our AS400 to SQL
server 7. The import staging table is truncated before the
import starts. There are no indexes on the staging table nor a primary key. The import completes successfully but the
staging table is highly fragmented. Of course adding a
clustered index can resolve the fragmentation and I've included the DBCC SHOWCONTIG results of doing that just to show where the table should be.

I just don't understand why the table is getting so fragmented since the data isn't indexed. SQL server just has to fill the pages as the records arive (already in perfect order). It looks like I'm getting one record per page and one page per extent!

I could care less whether the data is indexed since it is transformed and placed in another fully indexed table. The staging table exists just for capturing the raw data.

Any suggestions or advice would be appreciated.

Mike


After Importing:

DBCC SHOWCONTIG scanning 'AS400OrderImport' table...
Table: 'AS400OrderImport' (421576540); index ID: 0, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 294601
- Extents Scanned..............................: 294601
- Extent Switches..............................: 294600
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 12.50% [36826:294601]
- Extent Scan Fragmentation ...................: 11.11%
- Avg. Bytes Free per Page.....................: 7868.0
- Avg. Page Density (full).....................: 2.79%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



After adding a clustered index, showcontig shows:

DBCC SHOWCONTIG scanning 'AS400OrderImport' table...
Table: 'AS400OrderImport' (421576540); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 8665
- Extents Scanned..............................: 1084
- Extent Switches..............................: 1083
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1084:1084]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.46%
- Avg. Bytes Free per Page.....................: 206.3
- Avg. Page Density (full).....................: 97.45%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.