Wednesday, March 31, 2010

Querying’s SQL Azure Northwind Sample Data Service

Updated 3/30 and 3/31/2010 with responses to Scott Hanselman’s comment about lack of OData compression in the OData’s AtomPub Protocol Overhead section.

Updated 3/28/2010 with minor fixes and additions.

odataMy Enabling and Using the OData Protocol with SQL Azure post (updated 3/26/2010) used SQL Server 2008 R2’s AdventureWorksLT database on OakLeaf Systems’ SQL Azure instance for basic OData URI query and Excel PivotTable examples.

Alex Barnett, Group Manager, Intuit Partner Platform, offers a recent third-party assessment of OData in his Why OData Matters (IMHO) post of 3/19/2010. Intuit offers a Windows Azure SDK (Beta) for Intuit Partner Platform.’s URI query examples use a publicly accessible, abbreviated Northwind version with the OData: URI Conventions. This version includes only the Categories, Products and Suppliers tables:


Note: The OData Team has added to this example a product (Bread), category (Food) and supplier (Exotic Liquids) with an ID of 0. The single Suppliers entry supports e-tag-based concurrency management.

The Complete Northwind Sample Database as an OData Data Source offers a publicly accessible full instance of the Northwind sample database, which is more suitable for testing queries that retrieve related items or collections because, unlike AdventureWorksLT, all but one item has a complete complement of related items. That is, all customers except one (FISSA Fabrica Inter. Salchichas S.A.) have related Orders records, all Orders have related Order_Details, all Order_Details have related Products, etc. Another Northwind benefit is that most Microsoft-oriented developers are more familiar with Northwind’s structure than AdventureWorksLT’s:


(Shippers, Suppliers, and Territories, as well as 11 views, are omitted from the preceding capture.)

Important: If you’re using Internet Explorer’s Atom/RSS Feed Reading feature, you must disable it to see AtomPub content formatted by IE 7 and 8’s XML stylesheet. To disable Feeds:

  1. Choose Tools, Internet Options to open the dialog of the same name.
  2. Click the Content tab and the Feeds and Web Slices section’s Settings button to open the dialog of the same name.
  3. Clear the Turn On Feed Reading View checkbox.
  4. Click OK twice to close the dialogs.

Retrieving Data Source Metadata

Adding the $metadata Query Option to the Service Root URI returns the full description of each service collection, including [primary] key Name and data Type and other datatype-dependent attributes, such as Nullable, MaxLength, FixedLength, and Unicode for strings, as well as NavigationProperty attributes for related entries:


image Note: The  Edm in Edm.String, edmx, XML namespaces, and the like is an abbreviation for Entity Data Model, an element of the ADO.NET Entity Framework, v1 (.NET 3.5) and v4 (.NET 4). Relationships NorthwindModel.FK_Products_Categories attribute format also derives from the Entity Data Model.

Chapter 15, “Using the Entity Framework as a Data Source,” of my Professional ADO.NET 3.5 with LINQ and the Entity Framework book describes ADO.NET Data Services’ URI query syntax, including Query String Options, Comparison, Logical, Arithmetic, and Grouping Operators, as well as Datetime, Math, and Type Functions. The chapter also covers the substitution of the JSON protocol for AtomPub with AJAX.

Shawn Wildermuth reports in his SQL Azure's OData Support post of 3/24/2010 that the following operations and functions aren’t working in SQL Azure as of that date:

    • Stored procedures
    • Tables/views with no primary key
    • Date and Math scalar functions
    • EntityClient support
    • $expand query option
    • Many:Many relationships
    • Multi-level relationship navigation
    • Media Link Entries

Note that SQL Azure itself doesn’t support tables without clustered primary keys (heap tables).

Ed Katibah (a.k.a. Spatial Ed) replied to my “Is an extension of the OData protocol in the works for spatial features?” question on 3/19/2009:

We are actively discussing this internally and hope to have some proposals firmed up soon.

Ed is Spatial Program Manager, SQL Server, Microsoft Corporation.

Retrieving Collections and Individual Items

Append the collection name to the Service Root URI, as in to return all 2,155 members. To return the top N item(s) in the order of the primary key, replace the trailing / with ?top=# where # is the number of items you want. Here’s IE8 displaying the first Order_Details item:


Alternatively, you can skip N item(s) by replace the trailing / with ?skip=#, where # is the number of items you want to skip. To specify sorting on a column other than the primary key, add the ?orderby=ColumnName Query Option, as in$skip=10&$top=2&$orderby=ProductName.

For a composite primary key, append the Key values in parenthesis, separated by a comma:,ProductID=11) for Order_Details or for tables with numeric or‘ALFKI’)/ for tables with string primary keys.

OData’s AtomPub Protocol Overhead

AtomPub isn’t a very efficient data transfer protocol. Fiddler 2.0’s Transformer feature reports the size of the entire Order_Details document to be 630,279 bytes or 292.5 bytes per item. Here’s Fiddler 2.0’s display of the HTTP Request and Response messages to retrieve all Order_Details items:


The actual data size of the Order_Details table is 72 KB, which indicates that AtomPub multiplies Data Transfer Out bytes by a factor of 8.75. Microsoft charges $0.15/GB of Data Transfer Out ($0.10/GB of Data Transfer In), so Microsoft might repay its entire development cost for enabling SQL Azure to use the OData protocol with added data egress charges for uncompressed content. 

Update 3/30/2010: Scott Hanselman posted a Creating an OData API for StackOverflow including XML and JSON in 30 minutes tutorial on 3/28/2010 and added the following comment on 3/30/2010:

If you compressed the OData with GZip, it'll be as small or smaller than JSON...

I replied on the same date:

I agree, but despite that Accept-Encoding: gzip, deflate header, the SQL Azure OData response isn't compressed.

You can determine whether the data is compressed or not by selecting Fiddler2’s response Transformer tab, which displays “No Compression”:


I started a When Will the SQL Azure OData Provider Deliver GZipped Responses? thread on 3/30/2010 in the SQL Azure — Getting Started forum. Microsoft’s forum moderator, Yi-Lun Luo, suggested I submit a feature request, so here it is: Enable GZip/Deflate Compression of SQL Azure OData Response of 3/31/2010 on the wishlist. I’ve also sent a similar e-mail to

David Robinson, who’s a Senior Program Manager on the SQL Azure team and the technical editor for my Cloud Computing with the Windows Azure Platform book, replied to my e-mail on 3/31/2010 as follows:

… We definitely want to have a more compact wire format for OData, although it's not clear that compression is the right answer.  There are two goals for having the more compact format; reducing server (and, to a lesser degree, client) processing and reducing the bits on the wire.  Compression solves the later, but for many scenarios the former is at least as important.  We are looking at ways to provide this, but also must stay true to the interoperability tenets that OData is all about.

My take is compression solves the data egress cost problem.

Note: You can download and install Eric Lawrence’s Fiddler 2.0 from here. The author describes Fiddler 2.0 as follows:

Fiddler is a Web Debugging Proxy which logs all HTTP(S) traffic between your computer and the Internet. Fiddler allows you to inspect all HTTP(S) traffic, set breakpoints, and "fiddle" with incoming or outgoing data. Fiddler includes a powerful event-based scripting subsystem, and can be extended using any .NET language.

Fiddler is freeware and can debug traffic from virtually any application, including Internet Explorer, Mozilla Firefox, Opera, and thousands more.

Fiddler is an indispensible utility for any developer working with RESTful protocols, such as AtomPub.

Retrieving Related Items (1:Many Navigation)

Append the related collection name to a query that returns a single item, such as'ALFKI')/Orders to return all all orders for Alfreds Futterkiste. The following capture shows the first of six orders:


Alternatively, you can use the $links query option to return only the link(s) to related items:'ALFKI')/$links/Orders


Applying the $expand Query Option to the service URI and a collection as in$expand=Products expands the many side of the navigation property inline with items of the parent collections, in this case each category followed by each product in the category. Here’s Categories(1)/Products(1):


Retrieving Related Items (Many:1 Navigation)

Returning the Categories record for a Products item is an example of many:1 navigation (the reverse of 1:many navigation) with the following syntax:


An alternative is$links/Category.

Stay tuned for future posts about OData services for SQL Azure and Microsoft Codename “Dallas.”

blog comments powered by Disqus