Rebuild indexes of single table.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Rebuild indexes of single table.

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-04-06 : 07:13:07

Dear All,

Can anybody tell me how to rebuild the indexes of single table ?

Thanks and Regard's
Harish Patil

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-06 : 07:21:48

Ok try this will help If You Know Index name

http://www.cryer.co.uk/brian/oracle/howto_orcl_rbai.htm

Raghu' S

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-06 : 07:30:42

quote:
Originally posted by raghuveer125

Ok try this will help If You Know Index name

http://www.cryer.co.uk/brian/oracle/howto_orcl_rbai.htm

Raghu' S

That link seems to be specifically for Oracle. Assuming you are asking about Microsoft SQL server, the syntax might be similar to Oracles, but you can do the following, which is Microsoft-specific:

In SSMS object explorer, under tables, right click on Indexes and select rebuild all. In the window that pops up you can click OK to rebuild, or click Script to generate the script for rebuilding and change it to suit your preferences.

Sachin.Nand

2937 Posts

Posted - 2011-04-06 : 07:48:03

quote:
Originally posted by hspatil31

Dear All,

Can anybody tell me how to rebuild the indexes of single table ?

Thanks and Regard's
Harish Patil

How big is your table and why do you want to rebuild them ?

I wouldnt go blindly rebuilding indexes when I am not facing any performance problem.

PBUH

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-06 : 07:48:40

There's also:

ALTER INDEX ALL ON myTable REBUILD

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-06 : 08:00:56

Here is logic when to rebuild an index

First check your index Avg_Fragmentation_in_percent
If this column value is <10% or 15% Then leave as it is
if this Column value is >10% and <30% then Rebulid
if this column value is >30% Then drop and recreate

Raghu' S

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-04-06 : 08:02:43

thnks. This one is useful for me.

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-04-06 : 08:03:09

thnks. This one is useful for me.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-06 : 08:34:32

Try your best to avoid drop/create method. DROP_EXISTING is good when you actually need to drop and recreate index, but still try to avoid using this for de-fregmentation process. Use ALTER INDEX REORGANIZE when fragmentation is up to 40 % (avg_fregmentation_in_percent value of dm_db_index_physical_stats) and if percentage of fragmentation increases from 40% you must use ALTER INDEX REBUILD.
http://connectsql.blogspot.com/2011/01/sql-server-4-possible-methods-to.html

--------------------------
http://connectsql.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources