SQl Server tables: to heap or not to heap?

Answer 1

I would consider to put a clustered index on the large tables. The clustered index defines the physical order of how the records are stored. The consequence of this, is that the rows in the table can be stored more efficiently, and reduce fragmentation. I'm sure there will at least be one column in the table which could be a candidate to put a clustered index on. (And if not, you could create a new column, which contains the date and time when the record has been created, and you put a clustered index on that column. I think that this is still better then no CI at all).

Edit: if the large tables are indeed log-tables, that aren't read frequently, then they can be left as a heap.

Answer 2

It depends on how the tables are used. Normally I want a clustered index on a table with millions of records, but you also need to consider how the table is used. Adding an index will slow down inserts because it has to lookup the proper page for each new record (and possibly insert a new page) rather than just append it. If these title are primarily "dumps" for data and are rarely checked (like emergency logging, for example), then leaving them alone might be better.

As always, you should profile to find out what works best for your application or system.

Answer 3

Sounds like the database was created by someone who knew what he was doing. Log tables and small code tables are exactly where heaps make sense.

If there are no current problems with the database, I'd leave it as it is!

Answer 4

for large table a Clustered index is always a good idea. even for insert only table. of course your clustering key should be an ever increasing value.

Answer 5

Add a clustered index always. Without a clustered index, you can not quickly compact or defrag the table. Without it, you can't.

Simplistic, but I bet some of the performance issues could be traced to badly organised data.

Answer 6

One problem with a clustered index on large tables is that the memory required to store the index is equal to the size of the table. There is no separate index. A non clustered index stores only the data of the index and then the table's primary key or a refid. So a normal index can much more likely fit in RAM. If you are doing searches using the clustered index and the table is large then you could easily be slowing things down. If your clustered index is part of the date and your searches are all for recent dates, then maybe the clustered index won't hurt searching performance since you never access all of it.

I disagree with posters claiming the clustered index will reduce data fragmentation. It increases the data fragmentation. On a normal table, only deleting causes fragmentation. As you add rows to a clustered table, or change the a field of the clustered index, SQL has to physically reorder table. This means breaking and adding data pages which increases fragmentation. Thats why everyone recommends being careful to pick a field for clustering that a) doesnt change often if ever, and b) always increments.

I find that a clustered index is useful on large tables when your queries need to return multiple "related" rows often. You can use the cluster so the related rows are stored consecutively and easier for SQL to retrieve. I wouldn't necessarily cluster a large table so that I would have a new index for searching.

The one advantage that clustering does have, like a covering index, is that the index contains the data that query is trying to return. There is no extra step from the index to the table to get the data.

In the end, you have to get the profiler out, and run some tests.

Am I getting this right or missing something?