rebulid index

Question

Post reply

rebulid index

ramyours2003

SSChampion

Points: 12268
More actions
September 15, 2008 at 1:47 am

#126323

how can we rebulid index in sql 2005?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Alter Index ... Rebuild

You can get all the details of the various options from Books Online

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 2

Besides REBUILDING, there's also REORGANIZING indexes.

As mentioned, there are a lot of articles about rebuilding/reorganizing indexes

Wilfred
The best things in life are the simple things

psa Old Hand Points: 336 More actions · Answer 3

Check the following links:

http://msdn.microsoft.com/en-us/library/ms189858.aspx

http://msdn.microsoft.com/en-us/library/ms188917.aspx which contains a very useful script at the end for reindexing

PSA

Thato SSC Veteran Points: 288 More actions · Answer 4

It depends on your version of SQL 2005. If it's Ent. then you'd run it like this if you wish

USE [YourDatabaseName]

GO

ALTER INDEX [IDX_YourIndexName] ON [dbo].[YourTableName] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON )

GO

unfortunately if you are running a STD version you'd have to turn the ONLINE option OFF, otherwise you get an error like this...

Executing the query

"ALTER INDEX [IDX_YourIndexName] ON [dbo].[YourTableName] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON )"

failed with the following error: "Online index operations can only be performed in Enterprise edition of SQL Server.".

Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.