How to defrag a table?

To all gurus,
Thanx for the info concerning the dbcc statements. The only remaining
question is there a command or code that is known to be used to defrag a
table. It would be very good if this would be usable on the fly (ad-hoc)
Thanx
BillO
*** Sent via Developersdex http://www.examnotes.net ***"Bill Orova" <nospam@.devdex.com> wrote in message
news:e3W#oPuZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> To all gurus,
> Thanx for the info concerning the dbcc statements. The only remaining
> question is there a command or code that is known to be used to defrag a
> table. It would be very good if this would be usable on the fly (ad-hoc)
> Thanx
> BillO
>
> *** Sent via Developersdex http://www.examnotes.net ***
What do you mean by defrag a table?
A heap (table without a clustered index) stores data in no particular order.
As rows are deleted and then inserted SQL Server places those rows where
they fit.
If you have a clustered index on a table and the clustered index is
fragmented, then you could rebuild the clustered index and essentially
defrag your table.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi,
See DBCC INDEXDEFRAG and DBCC DBREINDEX commands in sql server books online.
You could also see the command DBCC SHOWCONTIG in books online which details
the fragmentation for table.
Thanks
Hari
SQL Server MVP
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:uDT6fUuZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> "Bill Orova" <nospam@.devdex.com> wrote in message
> news:e3W#oPuZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> What do you mean by defrag a table?
> A heap (table without a clustered index) stores data in no particular
> order.
> As rows are deleted and then inserted SQL Server places those rows where
> they fit.
> If you have a clustered index on a table and the clustered index is
> fragmented, then you could rebuild the clustered index and essentially
> defrag your table.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>|||
Rick,
So essentially if there is no clustered index then it would not be
possible to defrag a table(heap) or if possible it would have no
benevolent consequences in term of hard disk space?
BillO
*** Sent via Developersdex http://www.examnotes.net ***|||It depends on what you mean by fragmentation. For a heap, the is no order. B
ut as pages and extents
are allocated and rows and pages are deallocated, you can leave "holes" in y
our pages as well as
extents. I.e., pages not fully utilized and extents where not all 8 pages ar
e used. If you consider
this a type of fragmentation, then there is no way to defrag except re-loadi
ng the table or possibly
(shudder) shrinking the file/database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bill Orova" <nospam@.devdex.com> wrote in message news:O0EQbd6ZFHA.1088@.TK2MSFTNGP14.phx.gb
l...
>
> Rick,
> So essentially if there is no clustered index then it would not be
> possible to defrag a table(heap) or if possible it would have no
> benevolent consequences in term of hard disk space?
>
> BillO
> *** Sent via Developersdex http://www.examnotes.net ***