Table fragmentation in SQL server

Question

Hi Anybody have an idea about Table fragmentation in SQL Server(not Index fragmentation).We have a table ,this is the main table and its not storing any data permently,dat come here and goes out continusly. there is no index on this because only insert and delete staements are running frequently. recently We face a huge delay for the response from this table. If we select anything it tooks more than 2 to 5 minuts to return result,even there is very few datas. At last we delete and recreate this table,and now its working very fine. Appreciate If any comments,how this is happening ?

Joseph

Answer 1

If you are selecting parts of the data from the table, then it may well be beneficial to have appropriate indexes on the table.

Answer 2

A table without a clustered index is called a heap. A heap can be fragmented too.

Performance would probably improve if you added a clustered index with an auto-increment primary key. A clustered index does not slow deletes or inserts (to the contrary.) In addition, the scheduled routine maintenance will keep tables with a clustered index defragmented.