can tables themselves be fragmented? Index ID 0?

When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
Cory
Index ID of 0 means that the table is a heap. In other words the table does
not have a clustered index. The only way to remove fragmentation is to
create a clustered index on that table or to export all the data, truncate
it and import it all back in again. Index ID of 1 is always the clustered
index. You will never have both only one or the other.
Andrew J. Kelly SQL MVP
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
> of my indexes with ID's 1 and on up appear to be fine, it's just these ID
> 0's that look bad.
>
> thanks,
> Cory
>
>
|||Hey Andy, I'm wondering how exporting & re-importing the data helps? Doesn't
simply creating a clustered index & then dropping the clustered index
provide an effective defrag of the heap?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table
> does not have a clustered index. The only way to remove fragmentation is
> to create a clustered index on that table or to export all the data,
> truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>
|||In addition, it is worth explaining what you mean by fragmentation.
When the physical order doesn't match the logical order is one type of fragmentation this is
impossible for the datapages of a heap table (index 0) as there is no order. This is also known as
external fragmentation.
Another is then you have lots of free spaces on the pages and extents that the heap/index is using.
This is also known as internal fragmentation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table does not have a clustered
> index. The only way to remove fragmentation is to create a clustered index on that table or to
> export all the data, truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>