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.
Best Answer
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.