In relation to Most efficient way to return multiple aggregates in a single stored proc?

I have an email type application and want to select all the messages (inbox) for a user. The problem is that I normalize the header part of the emails into the DB such that the flat data goes into a Message table and the from, to, CC, BCC get stored to another table.

What's the best way to select messages (in full - meaning denormalize the full message) so that each record contains all the message pertinent fields including all the Message table fields and any related records from the recipient table related to the message as per the PK/ FK relationship.

One thing I am placing much weight on is the efficiency of the SQL solution because this will be code that gets executed many many times over and will likely be the most run sql of the entire DB

For context here is a view of my DB schema.


  • How much data do you expect in the message table?
    – gbn
    May 6, 2011 at 19:02
  • @gbn: millions of records. thus at least two records for each message record will be had in recipients table (from, to) as a minimum.
    – kacalapy
    May 6, 2011 at 19:07
  • Do you want your output to be a single text string like the original email? If so you'll need some kind of string concatenation aggregate such as the methods discussed in this SO question May 7, 2011 at 7:36
  • 4
    is it just me or this schema looks way too normalized?
    – Marian
    May 7, 2011 at 8:18
  • @Marian: the normilization is due to the recipient table being able to capture messages from outlook where a single message can have multiple TO, CC, and BCC listings.
    – kacalapy
    May 9, 2011 at 17:34

2 Answers 2


This is how I would do it. I regularly use Coalesce to place rows into delimeted fields and it always performs and scales well (as long as you realize that a subquery is ALWAYS going to cause some performance hit).

If you didn't like running it as a stored procedure you could also easily rewrite as a table valued function.

Another approach would be a CTE I suppose but I am not as familiar with that approach to type from scratch.

    @pMessageID int


Declare @pTo varchar(max)
Declare @pCC varchar(max)
Declare @pBC varchar(max)

SELECT @pTo = COALESCE(@pTo + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 1 /** or whatever the id of TO field is */

SELECT @pCC = COALESCE(@pCC + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 2 /** or whatever the id of CC field is */

SELECT @pBC = COALESCE(@pBC + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 3 /** or whatever the id of BCC field is */

SELECT Message.*, @pTo as [ToField], @pCC as [CCField], @pBC as [BCCField], (SELECT TOP 1 [EmailAddress] FROM MessageRecipient Where RecipientTypeID = 0 /**<sender id>*/ AND MessageID = @pmessageID) AS [FromField] FROM Message Where Message.ID = @pMessageID


You might ask yourself how Coalesce works when used in this way ( I did, when I first saw it used ). Basically it creates a recursive query returning each subsequent non-null value in the set, in turn, until the end of the return set. Coming out the other end you get a coma delimited list of all results as a single string.

  • Just got a chance to actually try this code at home and realized I needed to debug a little.
    – RThomas
    May 10, 2011 at 4:55
  • 1
    That's a pretty neat trick with COALESCE. Where did you learn that? May 13, 2011 at 16:34
  • Wish I could say I figured it out myself but alas... I found an article awhile back on alternatives to cursors. This was one of the tricks described. I think it was a SqlServerCentral article.
    – RThomas
    May 13, 2011 at 16:40

I would create a view called viewInbox that is devised by all the one-to-one relationship tables. This would be my main query view. I'd use this view (viewInbox) to display a list of all the inbox items.

When the user drills down into the message, I would then bring back all the information including the one-to-many relationships from the multiple To's, CC's and BCC's.

  • More performant for sure, and probably how I would do it as well, but doesn't meet request to "denormalize".
    – RThomas
    May 12, 2011 at 17:56
  • 2
    @LazyDBA - I would consider the viewInbox to be "denormalized". May 12, 2011 at 18:26
  • "normalized" and "denormalized" refer to the design of tables. To denormalize a schema means a change to the schema. So when there's a request to "denormalize" the data using a select statement, I suspect the word is being used to mean "include redundant data" in essence, joining tables into a single resultset. May 13, 2011 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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