Link to home
Start Free TrialLog in
Avatar of mjacobs2929
mjacobs2929

asked on

SQL Server 2005 defrag compress re-index tables

I have a hosted SQL Server 2005 database on which there are several tables that I wish to defrag or compress or whatever is necessary. These tables get a lot of use - they temporarily hold rows which are deleted and then re-populated over and over again, hence the indexes on these tables have grown quite large. Firstly, is this a potential problem?

Secondly, what is the simplest way to refresh the indexes and compress or defrag the tables?

My host gives me ASP.NET Enterprise Manager to work with, which is a rather simple web-based tool, but  does allow the running of basic scripts. However, it will hang if asked to do too much, thus I wish to keep this script simple and run it on a per table basis.

Many thanks,
MJ
Avatar of kenhaley
kenhaley

DBCC INDEXDEFRAG will do everything you're talking about.  It can be run at the database level, the table level, or for an individual index.  See books on line.
HOWEVER (and there's always a however), this statement is being deprecated (but it does work in SQL 2005). MS says to use ALTER INDEX instead.  (Again, see Books On Line).  ALTER INDEX can be run at the table level or index level.
To answer your question about compressing and defragging the table itself...by rebuilding the clustered index on a table (using either method above) does in fact rebuild the table.  All the table's data is contained in the clustered index.  If your table doesn't have a clustered index, you should add one, not only for this rebuild capability, but to speed up retrieval for the more common queries.
Avatar of mjacobs2929

ASKER

kenhaley,

Thanks. I had aleady read the stuff online about DBCC INDEXDEFRAG and ALTER INDEX before I posted my question. I know very little about this side of things and really none the wiser for having read it. What I'm looking for here is a simple ready to run script (minus the specific table name, of course), hence the 500 points. I have no idea whether the tables have clustered indexes, or not, nor how to go about finding this out. It doesn't sound like theres a very straight-forward answer to this question, although the tables thenselves are very very simple; in most cases three or four columns and one incrementing index column. None of the tables 'rely' on any of the others in any way, if this is what is menat by 'clustered'.

Cheers,
MJ
ASKER CERTIFIED SOLUTION
Avatar of kenhaley
kenhaley

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Scratch my statement that you "can't change them even if you did know".  I misread your post, and thought you had no control over the table's structures.