At What Point Does Having an Index Become Efficient

Question

I've found a lot of resources that mention that adding an index to a table makes searches faster and inserts slower, but only if the table is large. This creates a tradeoff, which is a design decision, but there should be an approximate table size before which using an index is absurd. (10 rows, for example, is probably way beneath that limit)

Does anybody know about where this limit would be, or know of a resource that would point me in the right direction?

Answer 1

The exact limit is really hard to determine ahead of time.

One thing most people underestimate is the high requirements that an index must fulfill, before it becomes a candidate to be used in a query.

An efficient (nonclustered) index

• offers great selectivity, e.g. returns only a very small percentage (< 1%, < 2%) of the total rows. If the selectivity is not a given - SQL Server's query optimizer will most likely ignore this index

• should ideally cover the query, i.e. return all teh columns required by the query. If you can create an index that has 1 or 2 index columns, and includes another handful (2-4) columns as included columns and thus you can cover a query - then chances are the query optimizer will use this index. Which also means: if your code is always using SELECT * ..... to fetch all columns, the likelihood of indices being used goes down - quite dramatically, actually

I'm sure there are a ton of other criteria as well - but I would believe these two are the most critical ones. Of course, you should always keep your indices properly maintained (reorganize, rebuild) and make sure the statistics associated with your indices are up to date.

PS: nonclustered indices on foreign key columns are a special case; by default, I would always recommend adding those, since they help speed up both referential integrity checks, as well as JOIN's on those FK constraints. But even here, it's absolutely valid to "extend" those FK column indices by adding some additional "include" columns to make them even more useful.

Answer 2

You might see an improvement from an index with only 10 rows.

In the following test on my machine the version without an index completed in 10.5 seconds and the version with an index in 9.8 seconds (consistent over 3 runs).

The index in this case only consists of 1 leaf page but as the slot array is ordered in index key order it's presence allows SQL Server to just return the single row of interest rather than perform an aggregation on all 10.

CREATE TABLE T
(
X INT,
Y CHAR(100) NULL
)

INSERT INTO T (X)
SELECT number 
FROM master..spt_values
WHERE type='P' AND number BETWEEN 1 AND 10

set nocount on;

DECLARE @I INT, @X INT

DECLARE @Time DATETIME2(7) = SYSUTCDATETIME()

SET @I = 1
    WHILE (@I < 1000000)
    BEGIN
    SELECT @X = MAX(X)
    FROM T
    SET @I += 1
    END

SELECT DATEDIFF(MICROSECOND, @Time, SYSUTCDATETIME())

CREATE CLUSTERED INDEX IX ON T(X)
SET @Time = SYSUTCDATETIME()
SET @I = 1
    WHILE (@I < 1000000)
    BEGIN
    SELECT @X = MAX(X)
    FROM T
    SET @I += 1
    END

SELECT DATEDIFF(MICROSECOND, @Time, SYSUTCDATETIME())

DROP TABLE T