|
Home > Archive > MS SQL Server > October 2006 > can tables themselves be fragmented? Index ID 0?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
can tables themselves be fragmented? Index ID 0?
|
|
| Cory Harrison 2006-10-24, 6:38 pm |
| 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
| |
| Andrew J. Kelly 2006-10-24, 6:38 pm |
| 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
>
>
>
| |
| Greg Linwood 2006-10-24, 6:38 pm |
| 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@shadh
awk.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...
>
>
|
|
|
|
|