0

I have a table, which in most of the cases, I need to fetch all the rows. It have 10334 records in it now, not expected to grow beyond 15K. The table is frequently updated.

I can create a Clustered index if needed.

But will it be a good idea, keeping table as a heap?. As all the record need to be fetched every-time, will a table scan and Clustered-index scan give same performance?.

Jithin Shaji
  • 5,893
  • 5
  • 25
  • 47
  • 1
    In the end it's not up to you whether it uses a table scan or index scan - it's up to the query planner, and it usually picks the right one. For a table of 15,000 rows it probably doesn't matter either way – Nick.Mc Mar 20 '14 at 05:07
  • 1
    Read Kimberly Tripp's excellent [The Clustered Index Debate Continues...](http://www.sqlskills.com/blogs/kimberly/the-clustered-index-debate-continues/) blog post - she explains why a **good** clustering key can speed up **all** operations when compared to a heap - so yes, I would always recommend to pick a good clustering key (narrow, unique, static, ever-increasing) and not just let your data rot in heaps ... – marc_s Mar 20 '14 at 05:43
  • The clustered index greatness is a fact. I was just looking whether, in this particular situation, will both act same? – Jithin Shaji Mar 20 '14 at 05:47
  • 1
    A good rule of thumb most experienced DBA's I know of go by, is that a HEAP is only ever good for one thing: To be used in dump tables where you need to bulk insert a large amount of data as fast as possible. For instance, if you only have a small window per day to dump reporting data from another system, you might consider dumping it into a HEAP to reduce the overhead it takes for SQL Server to create and sort the data on the clustered index. If that's not an issue however, you should pretty much always use a clustered index on the table. – Kahn Mar 20 '14 at 06:12

1 Answers1

1

Suggesting to use full table scan rather than going with any kind of Index(cluster/non-cluster Index) seek/scan in following context:

  1. While accessing all the records of your table. Based on your comment - "all the record need to be fetched every-time", so it's not effective to access data via any kind of index.
  2. Table rows contains 10-20K rows and updated frequently. Based on your comment - "...have 10334 records in it now, not expected to grow beyond 15K. The table is frequently updated."

In addition, cluster index doesn't maintain data structure(table) separately rather it sorts and stores the data rows in the table itself.

Answer for your Questions:

Q# But will it be a good idea, keeping table as a heap?

Answer# There is no harm if you keep table as a heap(without cluster index) and access all rows all the time without ordering by specific Coloumn for the result set.

Q# As all the record need to be fetched every-time, will a table scan and Clustered-index scan give same performance?.

Answer# Get Clustered-index if you fetch records in a sorted order at any point of time.

Suresh Gautam
  • 816
  • 8
  • 21