7

If a table is <8kb in size in SQL Server then presumably it is all stored on a single page. Thus - aside from enforcing a unique constraint - is there any performance benefit that could be obtained by applying an index?

My assumption is that in order to read such a table, SQL Server has to read the single page it is stored on into memory. So any indexing on an <8kb table is pointless from a performance point of view as any index would take up an extra page by itself and then you'd have to read two pages (unless the index included required columns) to get the data.

So unless there's something in the indexing which would either improve the handling of the data in ways I don't understand, or it helps the query optimiser, my assumption is indexing such a small table is pointless.

Would appreciate your expert thoughts!

(This is a purely theoretical question to help me ensure I understand some database concepts properly... thanks!)

Improve this question
1
  • 1
    insertions should be a little faster in a heap, more-so if a page split occurs on the b-tree
    – g2server
    Commented Jun 23, 2014 at 5:07

1 Answer 1

Reset to default
7

Yes.

One potential benefit is that the slot array is ordered by index key order so matching row(s) on the page can be found by performing a binary search (or sometimes linear interpolation) rather than reading all rows on the page.

SQL Server, Seeks, and Binary Search contains much more details about this.

Improve this answer
2
  • Thanks @MartinSmith, so it appears the seek within the indexes page may be faster. This leads to a couple of clarifications. 1) If the data is not an included column on the index, aren't you still shouldered with 2 distinct page reads which is slower than 1 scan across an unindexed page? 2) Even if the data is an included column, would there be any noticeable performance gain? For (2) I guess if you were joining it to something huge it may make a difference.
    – BI Monkey
    Commented Jun 23, 2014 at 22:06
  • @BIMonkey - Yes if both the non covering non clustered index and the base table itself (CI or heap) both fit on a single page you'd need to factor in the cost of another page read that would probably outweigh the benefit of binary seek. For tables this small it is exceedingly likely to matter anyway. There are always bigger fish to fry than this!
    – Martin Smith
    Commented Jun 29, 2014 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.