Data Management

Build molecular queries from atoms in SQL Server

What is your preferred method of doing things? Arthur Fuller says his M.O. is what he calls molecular queries. This scalable technique allows him to re-use and re-combine the atoms as requirements demand.

Every SQL Server developer has a preferred method of doing things; mine is what I call molecular queries. These are queries assembled from atomic queries; by which, I mean that they address exactly one table. By combing atoms, I can create molecules. There are restrictions of course (what the chemists call valence), but in general the principle holds.

In this tip, I explore a few variations on this strategy. I begin at the bottom (i.e., the most detailed level), and then work my way up step-by-step. To give you an idea of the flexibility of such an approach, I use several different techniques at different levels. (Caveat: This is not the only design for such a problem. I'm simply discussing some of the available alternatives.)

The database that I started with is the ubiquitous Northwind (although to preserve the original, I copied it to Northwind_New, and that is actually the database I used). In my copy, I made these significant changes:

  • I removed the compound primary key, added a new column called PK, and set it up as an Identity column.
  • I added a computed column called ExtendedAmount.

Listing A contains the Create statement that builds this new table. To populate it quickly with the values from the original table, I used this command:

INSERT INTO [Northwind_New].[dbo].[OrderDetails_New]
SELECT * FROM [Northwind_New].[dbo].[Order Details]

Now that I have a table filled with rows, it's time to experiment. (Since the new column ExtendedAmount is calculated, its values are populated automatically.) For starters, I'd like to sum the ExtendedAmount column by OrderID. I create a view, as shown in Listing B.

This gives me one row per OrderID, with the ExtendedAmount column summed (call it OrderAmount, to which Shipping, Taxes etc. would be added to derive the OrderTotal).

I'll introduce a table UDF into the equation, as shown in Listing C. Note that we can invoke the function in two ways: passing a CustomerID to restrict the table to that customer's orders, or passing a NULL to get a list of all Customer orders. In the event that I pass a CustomerID, then the list of orders is presented in OrderDate. If I pass NULL, the list is grouped and ordered by OrderDate within CustomerID.

I go a step higher in the hierarchy by printing one line per customer, with a column representing the total sales per customer. View Listing D. Now all the pieces have been assembled. At the outermost layer, I can call this function in two ways: passing a legitimate CustomerID or passing NULL. Here's an example of both:

SELECT CustomerID, CustomerTotal
FROM DBO.CustomerGrandTotal_fnt(null)

SELECT CustomerID, CustomerTotal
FROM DBO.CustomerGrandTotal_fnt('VINET')

You now have a set of precision tools that allow you to dive into the detail at whatever level is appropriate. The calculated column OrderDetails.ExtendedAmount hides one level of complexity, and from there I climb the tree to whatever level of detail hiding I want. That's why I call these objects atomic and molecular queries. I use the word "query" to encapsulate the fact that it doesn't really matter whether the object of interest is a view or a table UDF (although thanks to the naming convention, the suffix _fnt indicates a table UDF while the suffix _vue indicates a view).

Admittedly, the Northwind database does not have a large number of rows, but I think this technique scales well. More importantly, I like the granularity it affords. I can do exactly what I want, and re-use and re-combine the atoms as requirements demand.

Miss a tip?

Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

Editor's Picks

Free Newsletters, In your Inbox