Q

Index on table that is truncated/repopulated daily

Since I truncate and repopulate the data in my table every day, is the index of much use?

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?

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
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close