In your first query the index is used to find rows matching [InverterData].[Date] = '05.01.2016'
then it needs to lookup the rest of the row data to satisfy being able to return ACPower
and DCPower
- if you remove these columns from the output you'll see the extra lookup go away.
You could include the extra columns in the index with either:
CREATE NONCLUSTERED INDEX [NonClusteredIndex]
ON [InverterData] ([Date] DESC, [InverterID] ASC) INCLUDE ([ACPower], [DCPower])
orThis removes the extra lookup via the clustered index at the expense of making the non-clustered index consume more space on disk (and in memory). This query speed and used space trade-off is something you'll have to decide upon by running benchmarks on the bits of the application that use that table.
Note that you could also do:
CREATE NONCLUSTERED INDEX [NonClusteredIndex]
ON [InverterData] ([Date] DESC, [InverterID] ASC, [ACPower], [DCPower])
(the which uses the extra columns as part of the key rather than just INCLUDING
them. The former example is likely to be more efficient as the values are unlikely to be filtered/sorted upon so it saves page splits ascaused by the engine triestrying to keep the (effectively random) values stored in order)
This removes the extra lookup via the clustered index at the expense of making the non-clustered index consume more space on disk (and in memory). This query speed and used space trade-off is something you'll have to decide upon by running benchmarks on the bits of the application that use that table.