Ask the Expert

Index on table that is truncated/repopulated daily

I have a table that gets rebuilt every day after truncate. Have created a composite index idx1 on col1 and col2 (not clustered) on that and also two more indexes to speed up the processing.

This table is a continuously growing, like 400 new index values for idx1. And now it has around 170,000 records. So since I truncate and repopulate the data, is the index of much use? On truncate will the space utilized for index get released?

    Requires Free Membership to View

When you truncate the table, all but the INITIAL extent of the table will be released as the space is no longer needed. Fortunately, the same is true of the table's indexes as well. If you do not want to release space from the table and its indexes, use the REUSE STORAGE clause of the TRUNCATE TABLE command. You may (or may not) find it preferrable to keep the allocated space since it may be reused again.

This was first published in July 2007

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: