Efficiently get the last record I have a table order which contains OrderId (WarehouseId and OrderId are composite primary key).

WarehouseId | OrderId | ItemId | OrderDate
-------------------------------------------
          1 |       1 |      1 | 2016-08-01
          1 |       2 |      2 | 2016-08-02
          1 |       3 |      5 | 2016-08-10
          2 |       1 |      1 | 2016-08-05
          3 |       1 |      6 | 2016-08-06


(table is simplified and only shown required fields)

How to efficiently select the last order for particular Warehouse? I currently do:

SELECT TOP 1 * FROM tblOrder WHERE WarehouseId = 1 ORDER BY OrderId DESC


My concern is, when I have a million (or more) orders for particular warehouse, by doing sorting and select the first record, it will be too slow (I think?). From advice I get so far, it is not an expensive operation, therefore it should be fine. Is that right?

Or, is there any more efficient way to select the last order record?
Where appropriate, the database engine can do a backwards scan, ie go straight to the end of the index and work backwards so it's nice and efficient.  You can see this on your query execution plans under the 'Scan Direction' attribute of scan operators.  For your example, with your composite primary key on WarehouseId,OrderId then this will work:



As you are doing select * the clustered index already covers the whole query so no additional bookmark lookups are required.