CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-designindexing
Dave New
Dave New

Reputation: 40102

Non-clustered index on column in a small table

I have the following table, ReportType. This table will only ever have 100 or so rows and is never updated by the application (so INSERT, UPDATE or DELETE performance is not an issue). 

Table: ReportType
===========================================================
|  ID (PK)  |  Name  |  ExportFormat  |  SourceDatabase   |
===========================================================

Is it still worth putting a non-clustered index on ExportFormat? This column is used as a filter criteria in some scenarios and in some reports. It isn't highly selective at all (there are maybe only 10 distinct values) which indicates that it would not make a good candidate for a non-clustered index. BUT this table never experiences any INSERT, UPDATE or DELETE operations, so surely an index would actually benefit here (even if only slightly)?

Upvotes: 3

Views: 371

Answers (2)

Martin Smith
Martin Smith

Reputation: 453990

I disagree with the answer you accepted.

You say this table is read only so I don't see a downside of creating a covering non clustered index as below.

CREATE NONCLUSTERED INDEX IX 
     ON  ReportType(ExportFormat) INCLUDE(ID,Name,SourceDatabase )

For such a small amount of rows the benefit may be quite marginal but it avoids having to process all the rows for every query filtering on ExportFormat

Upvotes: 2

Andomar
Andomar

Reputation: 238296

As a rule of thumb, an index on a table with less than 128 rows adds more overhead than it's worth. Especially a non-clustered index-- a single bookmark lookup is probably more expansive than scanning the entire table.

Upvotes: 3

Related Questions