To Use or Not To Use DataSets

That has been the subject of great debate since the dawn of .NET, and is now even more debatable with the availability of Entity Framework. Some developers have dismissed DataSets out of hand long ago, primarily because—despite their ability to be strongly typed and to encapsulate business logic—they are not true business objects. For example, you need to navigate through relationship objects in the DataSet to connect between parent and child rows. This is not intuitive to object oriented programmers, who think of parent child relationships in simpler terms; each parent has as a child collection property and each child has a parent property. Furthermore, the DataSet paradigm does not allow for inheritance, which is also extremely important to object-oriented developers. Null values in a DataSet also require special handling.

Notwithstanding these concerns, I don’t generally advocate dismissing any technology out of hand. Every application is different, and you are doing yourself a disservice if you don’t examine the facets of all available choices on a case by case basis. Like anything else, DataSets can be used or they can be abused, and it’s true that they do present limitations if you try to use them as business objects.But if what you need is a generic in-memory database model, then that’s what a DataSet is, and that’s what a DataSet gives you. While the aforementioned concerns are all valid, the fact remains that DataSets are very powerful and can serve extremely well as data transfer objects. Furthermore, their unique ability to dynamically adapt their shape according to the schema of whatever data you stream into them is a capability that none of the newer APIs provide.

So to be clear, DataSets are not obsolete. The DataSet is a cornerstone of the .NET framework, and it is not going away. In fact, even as late as .NET 3.5 when LINQ to SQL and Entity Framework were first released, Microsoft has been enhancing DataSets. For example, the TableAdapterManager was added to greatly simplify hierarchical updates. The fact remains that DataSets do still have their place, and you are very likely to encounter them for a long time to come as you maintain existing applications.

Having said that, let me also stress that Microsoft has clearly positioned ADO.NET Entity Framework as the preferred data access technology today and in the future, eclipsing both DataSets and LINQ to SQL. Furthermore, Silverlight implements only a subset of the .NET Framework, and conspicuously absent from that subset is the DataSet. Although there are several third-party vendors that provide a DataSet object for Silverlight, this omission on the part of Microsoft is another indication that DataSets are discouraged for new development.

Server Side Paging with SQL Server Code-Named “Denali”

Well, the captain has just approved the use of electronic devices on my return flight from Las Vegas, where I just spent an exciting week speaking at and attending Visual Studio Live! And I’m eager to share some juicy new SQL Server Denali features with you that I demonstrated in my SQL Server workshop on Friday. Denali, in case you don’t already know, is the code name for the next major version of SQL Server (version 11.0, but whether it will be branded as SQL Server 2011 or SQL Server 2012 is still undetermined).

In the next several posts, I’ll share some of the new T-SQL enhancements offered by Denali. You can start playing with these features right now by downloading the Community Technology Preview (currently, CTP1 is the only publicly available release). Although Microsoft’s greatest advances in Denali center on new Business Intelligence capabilities (which they’re now dubbing “Pervasive Insight”), there are still lots of goodies in store for developers in the relational engine. In this post, I’ll describe a new feature that makes it easier than ever to implement server-side paging.

One Page At A Time, Please

Your query may return hundreds, thousands, or even millions of rows, but users can only cope with so much data at a time. They need paged results; that is, they want to view one chunk at a time in “pages”. The page size can vary of course; 10, 25, 50, or 100 results at a time, but any more than that is just not reasonable. Sure you can allow your query to execute and return a complete resultset from the data access layer to your middle-tier, and then deliver only the subset of that resultset for a single page to display in the user interface. But that’s an incredibly wasteful and inefficient approach. Ideally, of course, if one page is all you want, then that one particular page is all you should need to retrieve from SQL Server when you execute your query.

Returning paged query results was difficult to achieve prior to SQL Server 2005. That release of SQL Server introduced a series of ranking functions, including the ROW_NUMBER function that made it feasible to return one page at a time from your query. I’ll first show how to use ROW_NUMBER in SQL Server 2005 and higher to implement server-side paging, and then show you how much easier it is to achieve the same goal using the new OFFSET/FETCH NEXT syntax introduced in SQL Server Denali.

Using ROW_NUMBER (SQL Server 2005)

The ROW_NUMBER function does just what its name implies; it generates a sequential number for each row in the resultset returned by your query. The value returned by the ROW_NUMBER function can then be used in your query’s WHERE clause to limit the resultset to just the desired page. Here’s an example from the AdventureWorks2008R2 database that demonstrates how to use ROW_NUMBER. It returns “page 3” of the query results, which (assuming a page size of 10) are rows 21 through 30:

-- Get 10 rows starting at row 21 (rows 21-30; i.e., page 3)
DECLARE @PageNum int = 3
DECLARE @PageSize int = 10
DECLARE @FirstRow int = ((@PageNum - 1) * @PageSize) + 1
DECLARE @LastRow int = @FirstRow + @PageSize - 1

SELECT *
 FROM
   (SELECT RowNum = ROW_NUMBER() OVER (ORDER BY LastName, FirstName), *
     FROM Person.Person
   ) AS a
 WHERE RowNum BETWEEN @FirstRow AND @LastRow
 ORDER BY LastName, FirstName

And here are the results:

Now this certainly works, but there are two undesirables here. First, it requires you to manufacture the row number as an additional column in your resultset, whether or not you want/need it. Second, the syntax is somewhat contorted; the required use of a nested SELECT statement and multiple ORDER BY clauses (as well as the required alias “AS a”) is both awkward and unintuitive.

Using OFFSET/FETCH NEXT (SQL Server Code-Named “Denali”)

Now take a look at how the same result can be achieved in Denali:

DECLARE @PageNum int = 3
DECLARE @PageSize int = 10
DECLARE @Offset int = (@PageNum - 1) * @PageSize

SELECT *
 FROM Person.Person
 ORDER BY LastName, FirstName
 OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY

I don’t see how the syntax could be any simpler than this, do you? Just specify your starting row with OFFSET and your page size with FETCH NEXT. Here are the results:

You can see that this query returns the same “page” as the previous version of the query that used the ROW_NUMBER function, yet it wasn’t necessary to manufacture a row number column to do it, nor did we need to code a subquery. It’s highly probable that your presentation layer is well aware of which row numbers are being display; it is after all in charge of managing the UI concerns of which page is being displayed to the user. Nevertheless, if you still want row numbers returned in your resultset, you can combine the new Denali syntax with ROW_NUMBER as follows if desired (you’ll still be able to achieve this with a single SELECT, but you’ll need to duplicate the ORDER BY clause with OVER):

DECLARE @PageNum int = 3
DECLARE @PageSize int = 10
DECLARE @Offset int = (@PageNum - 1) * @PageSize

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY LastName, FirstName), *
 FROM Person.Person
 ORDER BY LastName, FirstName
 OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY

So there you have it. Server side paging made as easy as one could hope for, thanks to the new OFFSET/FETCH NEXT syntax in SQL Server Denali.