1

My current client needs to be able to PEEK messages in a SQL Server Service Broker queue. The current implementation uses RECEIVE and rollback to peek.

As that generates writes to the transaction log I'm wondering if a SELECT TOP(1) would return the same row as the RECEIVE command?

There are several writers but just one reader to that queue.

5

No. RECEIVE constructs a query AST that cannot be constructed with SELECT. RECEIVE has to guarantee EOIO and conversation group locking, SELECT does not.

Service Broker has a built-in 'peek': GET CONVERSATION GROUP. This will lock the next available conversation group, on which you are guaranteed to get a message if you issue a RECEIVE within the same transaction. I strongly discourage the use of it.

Anything else you're trying to do is broken and you need to change the application to stop doing it. Always issue a RECEIVE and react to the returned messages result set.

  • In this case I do not care about exactly once but only about the order. Will it be the same for the SELECT? – jgauffin Sep 8 '14 at 20:40
  • No. Aside from the issue of asking does SELECT without ORDER BY guarantee an order, even if you would try to add an ORDER BY, there is no ORDER BY you could add to match a RECEIVE. But the point is moot. You shouldn't try to do it. – Remus Rusanu Sep 8 '14 at 20:43
  • A select without an order by is sorted by the clustered index, right? – jgauffin Sep 8 '14 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.