Index entries are too large

Try to keep index entries as small as possible. You can create indexes with keys up to 600 bytes, but those indexes can store very few rows per index page, which increases the amount of disk I/O needed during queries. The index has more levels, and each level has more pages.

The following example uses values reported by sp_estspace to demonstrate how the number of index pages and leaf levels required increases with key size. It creates nonclustered indexes using 10-, 20, and 40-character keys.

create table demotable (c10 char(10), 
                        c20 char(20), 
                          c40 char(40))

create index t10 on demotable(c10)

create index t20 on demotable(c20)

create index t40 on demotable(c40)

sp_estspace demotable, 500000

Table 8-1 shows the results.

**Table 8-1: Effects of key size on index size and levels**
Index, key size	Leaf-level pages	Index levels
t10, 10 bytes	4311	3
t20, 20 bytes	6946	3
t40, 40 bytes	12501	4

The output shows that the indexes for the 10-column and 20-column keys each have three levels, while the 40-column key requires a fourth level.

The number of pages required is more than 50 percent higher at each level.