Truncate Table effect on index space allocation

Truncate Table effect on index space allocation

Post by Peter T » Sat, 18 Mar 2000 04:00:00



After a TRUNCATE TABLE, what would be the effect on the index space
allocations? I am thinking about the "effect" like what the FILLFACTOR
setting can do for a new index creation.

And how about DELETE?

The reason I am considering this is that I need to do a large number of
record inserts after the TRUNCATE or DELETE.

Thanks
Peter Ty

 
 
 

Truncate Table effect on index space allocation

Post by Tony Rogerso » Sat, 18 Mar 2000 04:00:00


The truncate table will remove the index data as well. You will loose the
fillfactor on the index - fillfactor is only applied on creation of the
index.

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
www.sql-server.co.uk (UK User group, code library, FAQ etc..)


Quote:> After a TRUNCATE TABLE, what would be the effect on the index space
> allocations? I am thinking about the "effect" like what the FILLFACTOR
> setting can do for a new index creation.

> And how about DELETE?

> The reason I am considering this is that I need to do a large number of
> record inserts after the TRUNCATE or DELETE.

> Thanks
> Peter Ty


 
 
 

1. Index space allocation in fragmented dbspaces

We are attempting to fragment tables on our system. During testing we are
finding that the engine 7.31.TD4 is allocating HUGE first/next sizes for
indexes that are fragmented by expression and attached to the table.

First extent size                       193395
Next extent size                         34883
Number of pages allocated       193395
Number of pages used             25461

This is repeated in all 4 fragments.

Is there a way to control the first/next size of an index? Do I have to
create them all detached? Have I hit a bug?

Thanks

2. ODBC/ORACLE syntax error or access violation

3. Truncate table and effects on backup/restore

4. how to check whether table exists in stored procedure

5. Data space allocation gone haywire on table ??

6. Error: [MERANT][ODBC 20101 Driver]1201

7. table space allocation

8. Database backup...

9. Table Size, Allocation, Space Requirements

10. Question on Table space allocation

11. Display Tables space allocation

12. Oracle Free Space or Space used - DBA (now index space usage)

13. Recapturing disk space from the transaction log after truncating the log space