Good article and makes a lot of sense... providing that the table doesn't have a lot of inserts where every index counts AGAINST inserts. Most people don't know that INSERTS on heavily indexed tables are one of the primary causes of very high reads. For example and using the given example table, if I run the following code...

--drop table test_table

SELECT object_id, name, system_type_id INTO test_table FROM master.sys.all_parameters

CREATE NONCLUSTERED INDEX ix_name ON test_table (name ASC)

ALTER TABLE test_table ADD name_REVERSED AS REVERSE(name)

PRINT '========== Insert with no index =========='

SET STATISTICS IO ON

SET STATISTICS TIME ON

INSERT INTO test_table SELECT 1,'Dodah',2

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

GO

CREATE NONCLUSTERED INDEX IX_REVERSED ON TEST_TABLE (NAME_REVERSED ASC) INCLUDE (NAME)

GO

PRINT '========== Insert with index =========='

SET STATISTICS IO ON

SET STATISTICS TIME ON

INSERT INTO test_table SELECT 1,'Dodah',2

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

... then we can see that a single insert after the index is created now takes 11 reads instead of just 3.

(6756 row(s) affected)

========== Insert with no index ==========

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Table 'test_table'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)

========== Insert with index ==========

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Table 'test_table'. Scan count 0, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)

If you have an IO bound system, you need to be really careful about adding any indexes to tables that have a high insertion rate. Like everything else, "It Depends" and only a bit of "complete" testing will show you things that you may have not considered.