I have the following table,
ReportType. This table will only ever have 100 or so rows and is never updated by the application (so
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
DELETE operations, so surely an index would actually benefit here (even if only slightly)?
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