Despite the fact that the index is present, SQL Server decides to use the same method as it used on a non-indexed column.

This may seem inefficient unless we remember that sorting is not everything we need here: we also need to get the value of data. The index on idatetime does not contain the values of data, only the values of idatetime and id. SQL Server would need to shuttle between the index pages and table pages to get the values of data while maintaining the idatetime order, were the index used. This would result in an extra row: Key Lookup in the plan that would describe this.

SQL Server considers the Key Lookup so expensive that it prefers ordering all rows over it.

Let's see whether this judgment is fair. To do this we should force SQL Server to use the index:

SELECT SUM(data)
FROM (
SELECT TOP 100000 data
FROM [20091111_clustered].t_data WITH (INDEX (IX_data_idatetime) FASTFIRSTROW)
ORDER BY
idatetime DESC
) q

I changed the TOP 999999 to TOP 100000, or SQL Server would refuse to scan the table in the index order at all. But even with 10 times less rows, the overall query time is larger than of the query ordering by id. So using the index to keep the data ordered does not seem such a wise solution.

However, there is a third option

ORDER BY cdatetime

There is a covering index on cdatetime. The index pages are stored in a B+Tree ordered by cdatetime, and, additionally, the value of data is stored along with each record pointer.

SELECT SUM(data)
FROM (
SELECT TOP 999999 data
FROM [20091111_clustered].t_data
ORDER BY
cdatetime DESC
) q

Since everything the query needs is contained in the index, no Clustered Index Scan is required to retrieve the values of data.

SQL Server documentation states that it is not possible to create two clustered indexes on a table. However, an index that would cover all columns of a table (except the indexed columns and the PRIMARY KEY) is exactly just like a secondary clustered index.

In fact, this would be just a shadow copy of the table but with records stored in other order.

Such an index could serve all the queries that require this order and it would never have to reference the actual table.