Order By with Multiple Joins

Since I can't post an answer to my previous question yet, I decided to open a new post. To make things work with the code in that post, I had to add the hints WITH ( ... , INDEX ( IX_REQUESTID ) ) and WITH ( ... , INDEX ( IX_REQUESTIDREF ) ) to the tables Request and Options respectively. It seems that SQL Server was unable to find which index to use itself, because now it does the job correctly. But now the problem is other... I appended to the statement the clause ORDER BY Priority, DateEnteredand even though there is an index on (Priority, DateEntered) INCLUDE (RequestID) on the RequestTable, Query2 returns no results again... What can be wrong this time? Ple开发者_开发技巧ase help me, since this is really important and I can't figure out an answer myself.

There may be further requirements I'm not aware of, but when my code gets to a certain level of complexity I usually stand back and ask myself - could I do things simpler.

In this case, could you not run the query on just the Request table and store the values returned into a tablevar?

You'll probably need to set Transaction Isolation Level Serializable in order to get the row locking you desire. Not sure though - would need to try with and without.

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

 DECLARE @TempResults TABLE (RequestID int, Priority int, DateEntered DateTime)

 INSERT INTO @TempResults (RequestId, Priority, DateEntered)
 SELECT TOP 2 RequestId, Priority, DateEntered FROM Request WITH (ROWLOCK, UPDLOCK, READPAST) 
 ORDER BY Priority, DateEntered

Now with the rows locked join between the tablevar and the Options table to return the result-set you require.

 SELECT * FROM @TempResults Request INNER JOIN Options ON Request.RequestID = Options.RequestIDRef

I apologise if this isn't an option for you.