Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

will there be a dataloss doing rebuild index instead of reorg index?

Posted on 2010-09-21
2
Medium Priority
?
443 Views
Last Modified: 2012-05-10
Hi All,
will there be a dataloss doing rebuild index instead of reorg index?
if not, then what is teh use of prefering reorg instead of rebuild?
0
Comment
Question by:saratcm
2 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 33730097
No, SQL will never allow a data loss with any command.

Which is preferred actually depends on the condition of the index(es).  [If there are no indexes, neither rebuild nor reorg apply at all anyway.]

If the index is only slightly fragmented, say from 10-30% [you will see some recommendations as low as 20%, or as high as 40%], then you can do a reorg.  A reorg is always done live, so the table can be used by others while it is being reorg'ed.  The reorg is done is small chunks and so does not have a major impact on log size either.  A reorg does not fully defragment the index but it does some logical defragmentation.

Conversely, a rebuild of a clus index requires SQL to lock the table until the operation is complete [unless you have the Enterprise version, specify ONLINE rebuild, and meet the criteria for an ONLINE rebuild].  But a rebuild completely rebuilds the index, restoring the FILLFACTOR and removing all unrequired pages (if, for example, lots of rows have been deleted).

Rebuilding a nonclustered index also requires a table lock unless you specify ONLINE; the table will be locked while the nonclus index is being rebuilt.

"Locked" means any row mods (insert/update/delete) are prevented for sure, as are column mods.  

I don't think you can even read the table, not even if you specify WITH (NOLOCK), but I'm not 100% sure of that.
0
 

Author Closing Comment

by:saratcm
ID: 33730376
Thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question