Is fragmention limited to indexes in sql server?
While this probably fits better on dba stackexchange, it's kind of a grey area/borderline.

The short answer is that indexes get fragmented and need to be periodically checked and reorganized or rebuilt based on how much they have fragmented.

Going at it we find the fragmentation levels primarily through querying:

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'LIMITED')


However this query also returns the index_types. Where we see the expected clustered and non clustered indexes. But also the 'HEAP' Index type, which, isn't actually an index (the object_ID also doesn't have any kind of name in the sys.indexes view).

These are for tables with no indexes at all, and thus also no primary keys (which automatically creates an index). These too can get fragmented. Because they represent simply how spread out the data is across the disk.

You should also check and 'amend' these, either simply by actually giving them say a primary key. Or, if it's desired that they have no indexes/PK. Simply by rebuilding the table. This reorganizes the data within the table as well.

A quick example of code:

ALTER INDEX [PK_Foo] ON [dbo].[Foo] REORGANIZE --Generally used on low fragmentation count
ALTER INDEX [PK_Foo] ON [dbo].[Foo] REBUILD --Used for high fragmentation count
ALTER TABLE [dbo].[Bar] REBUILD --Used for HEAP indexes with medium-high fragmentation


There are many options that these statements can have, like padding factor. One thing to note is that REORGANIZE allows the table to stay online and other than using resources don't block users. The other options do lock users from accessing the table during the process.