Indexing order by columns Based on the query below it was suggested that I create an index on the column in the order by clause. My understanding is the sorting is done on the result set and not the table. 

The person suggesting this insists it helps with performance. I don't think it does but I have been wrong once or twice before.

Thanks.

SELECT TOP 1 [Process_Activity_Id]
,[Process_Id]
,[Transaction_Type]
,[Company_Code]
,[Entity_Identifier]
,[Item_Request]
,[Item_Response]
,[Item_Description]
,[Status]
,[Status_Detail]
,[Start_Date]
,[End_Date]
,[Last_Update_Date]
FROM [dbo].[Process_Activity] (NOLOCK)
WHERE [Entity_Identifier] = @EntityIdentifier 
  AND [Status] = 'In_Progress' AND [Process_Id] = @ProcessId
ORDER BY [Message_Sequence] ASC
Based on:

WHERE [Entity_Identifier] = @EntityIdentifier 
AND [Status] = 'In_Progress' AND [Process_Id] = @ProcessId
ORDER BY [Message_Sequence] ASC


...the best thing is to have an index on 

([Entity_Identifier], [Process_Id], [Message_Sequence]) WHERE [Status] = 'In_Progress'


As Status is filtered with a constant, a WHERE clause is going to be useful for this, so that the index only holds the "In_Progress" rows.

But you need to find the rows that match the Entity and Process, and to have those rows in Message_Sequence order (to save getting all the Message_Sequence values and then sorting them when the query runs).

The composite index like this will let the database engine seek into the appropriate Entity and Process, knowing that the first row it finds is the smallest Message_Sequence.

So the advice about indexing on Message_Sequence is kinda right, so long as you're only indexing on Message_Sequence within an index that's on Entity and Process, because those columns are more important.

If you only had an index on Message_Sequence, you'd be searching through the index for the first one that matched Entity and Process, which is only good for non-equality predicates (oh - I'm talking about this at the PASS Summit in Seattle this October).