In a previous blog entry a while ago, I wrote about writing a single SQL statement that did a SQL MERGE operation, used the OUTPUT clause to put out a rowset and directed the rowset into an INSERT statement. This happens in one statement without the need of explicitly defining a temporary table and using multiple SQL statements.

Turns out that this feature has a name: Composable DML. I've also heard it called (in SQL Server Books Online) "DML table source".

I've also heard the term composable queries, both in references to both Entity Framework functions and LINQ to SQL. They even have an "IsComposable" attribute in the Function definition) In this context, it means that the output of a function that produces a rowset (in this case a SQL Server table-valued function) or code that produces a rowset can be combined with further queries that do more filtering or projection, or even retrieve related rows. When the queries are submitted to the database, they are composed into a single SQL statement.

The point is pretty similar, reduce the resources required to accomplish a set of operations by reducing the number of statements or database round-trips needed.

More about Service Broker priority in SQL Server 2008.

The books online states, when setting a initiator/target priority as local service name/remote service name that the priority affects:

1. Sends from the initiator queue
2. Receives from the initiator queue
3. Getting the next conversation group from the initiator queue.

And specifies the mirror image priority for target/initiator as local service name/remote service name affects:

1. Receives from the target queue
2. Sends from the target service
3. Getting the next conversation group from the target queue

So, in my previous example, where

The initiator/target as local service name/remote service name is priority 7
The target/initiator as local service name/remote service name is priority 6

Let's send a message from the initiator that "sticks" in sys.transmission_queue. Suppose that either the network is slow or we don't have the appropriate setup. But the message is floundering in the transmission queue. Already SENT, waiting to be delivered.

"select * from sys.transmission_queue;" shows the message BEING SENT (and transmitted) at priority 7. And, when the message is finally DELIVERED, the priority in the receiveq "select * from receiveq" is priority 6.

Normally, you'd likely make both priorities in the initiator/target pair the same to avoid confusion, but you can make them different for greater granularity. And it DOES help to illustrate where and when priority is used.

Conversation priority is a new feature with SQL Server 2008. In a previous blog post, I talked about how to simply set one up. But you'd usually not want to set up a priority for all messages. So, lets set up a simple service and then define a "Premier Customer" priority. Before trying this you need to make sure the database is set to honor broker priority.

ALTER DATABASE pubs SET HONOR_BROKER_PRIORITY ON;

Suppose I had a simple (one database) service pair. A service called "sender" (note lower case) using senderq is the initiator and a service called "receiver" using receiverq is the target. They use the [default] contract. So we can set up a "Premier Customer" by contract.

CREATE CONTRACT premier ([DEFAULT] SENT BY ANY);
GO

Now, let's ensure that the target can also use the premier contract in addition to the DEFAULT contract.

ALTER SERVICE receiver ON QUEUE receiveq (ADD CONTRACT premier);

We need to set up the priority.

CREATE BROKER PRIORITY PremierCustomer
    FOR CONVERSATION
    SET (CONTRACT_NAME = premier,
         LOCAL_SERVICE_NAME = sender,
         REMOTE_SERVICE_NAME = 'receiver',
         PRIORITY_LEVEL = 7);
GO

The interesting thing is, this priority ONLY covers messages sent and received (and get conversation group) by the sender/senderq, IT DOESN'T cover the receives in the receiver/receiveq queue. So we'll set up a mirror image priority, and make the priority level still higher than the default (5) but gratuitously different than the other priority

CREATE BROKER PRIORITY PremierCustomer2
    FOR CONVERSATION
    SET (CONTRACT_NAME = premier,
         LOCAL_SERVICE_NAME = receiver,   -- local service is receiver
         REMOTE_SERVICE_NAME = 'sender', -- remote service is sender
         PRIORITY_LEVEL = 6);                   -- note level 6, not 7
GO

Now, begin a conversation using sender and receiver and the premier contract and send a message. Note that when you issue "SELECT * FROM sys.conversation_endpoints;" the sender (initiator's) endpoint is priority 7, the receiver's endpoint is priority 6. And, looking at the message in the receiveq its set to priority 6.

If you'd send a return message from the receiver (target) back to the sender (initiator), the return message in the senderq would be priority 7.

So when setting up priorities, if you want all messages on both sides to be affected, don't forget to set up a priority for EACH side. Here's the entire demo.