This is a spin-off question from Sort order specified in primary key, yet sorting is executed on SELECT.

@Catcall says this on the subject of storage order (clustered index) and the output order

A lot of people believe that a clustered index guarantees a sort order on output. But that's not what it does; it guarantees a storage order on disk. See, for example, this blog post.

I've read the blog post by Hugo Kornelis and understands that an index doesn't guarantee that the sql server reads the records in a specific order. Yet I have a hard time accepting that I can't assume this for my scenario?

CREATE TABLE [dbo].[SensorValues](
  [DeviceId] [int] NOT NULL,
  [SensorId] [int] NOT NULL,
  [SensorValue] [int] NOT NULL,
  [Date] [int] NOT NULL,
CONSTRAINT [PK_SensorValues] PRIMARY KEY CLUSTERED 
(
  [DeviceId] ASC,
  [SensorId] ASC,
  [Date] DESC
) WITH (
    FILLFACTOR=75,
    DATA_COMPRESSION = PAGE,
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
  ON [MyPartitioningScheme]([Date])

My original query was this:

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010
  ORDER BY Date DESC

But I suggest that I could as well use this one (read below for my explanation):

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010

As you can see, my table rows are small (16bytes) and I've got only one index, a clustered. In my scenario, the table consists of 100.000.000 records at this moment (and this will most likely increase tenfold).

When the database server queries this table it has two ways of finding my rows, either it seeks the primary key and thereby reading and returning my values in desc. order of Date, or it has to do a full table scan. My conclusion is that a full table scan on all those records will be way too slow and the database server will therefore always seek the table via its primary key and thereby returning the values sorted by Date DESC