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.
| Author |
Topic |
|
sqlpavan
Starting Member
20 Posts |
 Posted - 2011-11-19 : 10:58:56
|
| hi..can any one explain me about reorganizing and rebuilding inde..what was d main difference between those two..plz dont sent d links...s.pavan |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-11-19 : 11:59:39
|
| Why not? |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-11-19 : 12:05:35
|
| never mind. duplicate thread. |
|
|
|
sqlpavan
Starting Member
20 Posts |
Posted - 2011-11-19 : 12:20:54
|
| can u explain d differnece???s.pavan |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-11-19 : 12:35:37
|
| Please refer to your other thread where Gail already pointed you to the answer. |
|
|
|
james_wells
Yak Posting Veteran
55 Posts |
Posted - 2011-11-19 : 12:38:08
|
| Reorganizing an index defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes.Rebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting, and the index rows are reordered in contiguous pagesRe-Orgs do not update the index stats while rebuilds do. |
|
|
|
vikki.seth
Yak Posting Veteran
66 Posts |
Posted - 2011-11-19 : 15:04:38
|
| Reorganizing an IndexReorganizing an index defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance. The index is reorganized within the existing pages allocated to it; no new pages are allocated. If an index spans more than one file, the files are reorganized one at a time. Pages do not migrate between files.Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed The reorganize process uses minimal system resources. Also, reorganizing is automatically performed online. The process does not hold long-term blocking locks; therefore, it will not block running queries or updates.Reorganize an index when the index is not heavily fragmented. See the previous table for fragmentation guidelines. However, if the index is heavily fragmented, you will achieve better results by rebuilding the index.Large Object Data Type CompactionBesides reorganizing one or more indexes, large object data types (LOBs) that are contained in the clustered index or underlying table are compacted by default when an index is reorganized. The data types image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml are large object data types. Compacting this data can cause better disk space use:• Reorganizing a specified clustered index will compact all LOB columns that are contained in the leaf level (data rows) of the clustered index.• Reorganizing a nonclustered index will compact all LOB columns that are nonkey (included) columns in the index.• When ALL is specified, all indexes associated with the specified table or view are reorganized and all LOB columns associated with the clustered index, underlying table, or nonclustered index with included columns are compacted.• The LOB_COMPACTION clause is ignored if LOB columns are not present. Rebuilding an IndexRebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting, and the index rows are reordered in contiguous pages (allocating new pages as needed). This can improve disk performance by reducing the number of page reads required to obtain the requested data.The following methods can be used to rebuild clustered and nonclustered indexes:• ALTER INDEX with the REBUILD clause. This statement replaces the DBCC DBREINDEX statement.• CREATE INDEX with the DROP_EXISTING clause.* A nonclustered index can be converted to a clustered index type by specifying CLUSTERED in the index definition. This operation must be performed with the ONLINE option set to OFF. Conversion from clustered to nonclustered is not supported regardless of the ONLINE setting.** If the index is re-created by using the same name, columns and sort order, the sort operation may be omitted. The rebuild operation checks that the rows are sorted while building the index.You can also rebuild an index by first dropping the index with the DROP INDEX statement and re-creating it with a separate CREATE INDEX statement. Performing these operations as separate statements has several disadvantages, and we do not recommend this. use the following table to determine the best method to correct the fragmentation.avg_fragmentation_in_percent value Corrective statement> 5% and < = 30% ALTER INDEX REORGANIZE> 30% ALTER INDEX REBUILD WITH (ONLINE = ON)** Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-11-19 : 15:27:39
|
| You know, it's good, professional form to properly attribute quotes. That's an exact copy-paste from Books Online.--Gail ShawSQL Server MVP |
|
|
|
sqlpavan
Starting Member
20 Posts |
Posted - 2011-11-20 : 08:03:44
|
| s gila ..thankspavan444 |
|
|
|
|
|
|
|
|
indexes