How to choose the last recorded added without sorting I've got this simple table: RecordID (int PK), UserID (int FK), DTime (datetime), Length (int)

I need to run a simple query:

SELECT TOP(1) * FROM uTable WHERE UserID = @userid ORDER BY DTime DESC


The query is run using a Stored Procedure on SQL Server 2008 R2.

The objective is to get the last record for @userid in terms of DTime.

While checking the execution plans I've noticed the sorting (ORDER BY) is almost 80% of the cost to run this query (which is the most "expensive" query on my DB for now) - how could I change the query so I won't use ORDER BY and thus eliminate that heavy cost.

Thanks!
The generic way to access a table in order without a sort is a clustered index, which physically stores the rows in that order. However a table can only have one clustered index. From the documentation:


  PRIMARY KEY constraints create
  clustered indexes automatically if no
  clustered index already exists on the
  table and a nonclustered index is not
  specified when you create the PRIMARY
  KEY constraint.


So if this is your main access pattern for this table, then perhaps making the PK unique nonclustered and (DTime desc, UserID) a composite clustered index is the way to go. But be careful not to optimize for one access pattern at the expense of any others (e.g. is there another part of your app that wants fast lookups by RecordID, you would want to make sure that wasn't suboptimal now).