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!)