EXPLAIN EXTENDED

From Stack Overflow:

Hypothetically, in a SQL Server database, if I have a table with two INT fields (say a many-to-many relation) that participates in joins between two other tables, at what approximate size does the table become large enough where the performance benefit of indexes on the two INT fields overcomes the overhead imposed by said indexes?

Very nice question.

Imagine we have a table table1 (col INT NOT NULL, value1 INT NOT NULL, value2 VARCHAR(100) NOT NULL, …) with an index on col.

In SQL Server, an index can improve the following things.

1. Index lookups (range scans). An index can be used to limit the number of rows scanned:

   SELECT  *
   FROM    table1 t1
   WHERE   t1.col BETWEEN @start AND @end
   

2. Secondary data source. This query will get all data it need from the index:

   SELECT  t1.col
   FROM    table1 t1
   

3. Ordered rowsets. This query will not need to sort:

   SELECT  *
   FROM    table1 t1
   ORDER BY
           t1.col
   

4. Efficient MIN / MAX:

   SELECT  MIN(col)
   FROM    table1 t1
   

The latter case is probably the best illustration of the fact that even a table of 2 rows can benefit from creating an index.

Let's create the sample tables:
Read the rest of this entry »