CODEWITHSUNDEEP
sqlsql-serverindexing
E. Peterson
E. Peterson

Reputation: 452

Rebuild Index in SQL Only On One Table

Looking to create a SQL query that rebuilds indexes in SQL on only one table within my database. Can anyone point me in the right direction. Someone earlier suggested Ola Hallengren SQL maintenance, but I think that is too robust for what I'm after.

Upvotes: 24

Views: 78291

Answers (3)

Harsh Varde
Harsh Varde

Reputation: 144

If the purpose of Rebuilding index is performance then you can first try in these following order on table:

  1. Updating Statistics - Resource cost of updating statistics is minor compared to index reorganize /rebuild, and the operation often completes in minutes. Index rebuilds can take hours.

UPDATE STATISTICS mySchema.myTable;

  1. Reorganize Indexes - Reorganizing an index is less resource intensive than rebuilding an index. For that reason it should be your preferred index maintenance method, unless there is a specific reason to use index rebuild.
ALTER INDEX ALL ON mySchema.myTable REORGANIZE;
  1. Rebuild Indexes (offline) - An offline index rebuild usually takes less time than an online rebuild, but it holds object-level locks for the duration of the rebuild operation, blocking queries from accessing the table or view.
ALTER INDEX ALL ON mySchema.myTable REBUILD;
  1. Rebuild Indexes (online) - An online index rebuild does not require object-level locks until the end of the operation, when a lock must be held for a short duration to complete the rebuild.
ALTER INDEX ALL ON mySchema.myTable REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 10);

Notes:

  • Online Rebuild functionality only supported in Enterprise edition of SQL Server
  • MAX_DURATION are in minutes

Ref:

Upvotes: 0

Rajeev Goel
Rajeev Goel

Reputation: 1523

There's a difference between REORGANIZE and REBUILD. See this blog post for details.

If you truly want to REBUILD, and you want to do it for ALL indexes on a given table, then the command would be (straight from the official docs that npe pointed you at):

ALTER INDEX ALL ON mySchema.myTable REBUILD

Upvotes: 47

npe
npe

Reputation: 15699

Try this:

ALTER INDEX indexName ON mySchema.myTable REORGANIZE;

For more instructions see the official docs. The link points to SQL Server 2014 docs, but the syntax should work on 2005 and 2008 as well.

Upvotes: 8

Related Questions