Programming SQL Server 2008

SQL Server Katmai, now officially announced as SQL Server 2008, introduces a significant amount of new and improved functionality, including enhanced data types and greater programming flexibility for database application developers.

In this article I’ll look at the range of data access technologies available for leveraging the power of SQL Server 2008. After I show you how to select the right technology for your needs, I’ll dive into the improvements, first examining a new construct for programming data. Then I’ll turn to expanding the set of building blocks-data types-available to developers. Taking a brief pause, I’ll discuss how SQL Server 2008 removes limitations for some scenarios. Then building on what the article has covered, I’ll examine new out-of-the-box solutions for common application scenarios. The article will hopefully leave you with a new understanding of the possibilities for taking advantage of Katmai: SQL Server 2008.

Choosing a Data Access Technology

SQL Server offers a wide range of data access technologies to developers. The best place to start the discussion on taking advantage of new programmability features is how to choose the right one. For new application development, using the .NET Framework and specifically the SQL Server provider in ADO.NET, System.Data.SqlClient, will be the best choice in most cases. If you’re working within an existing application with business logic and associated data access code in native C/C++, you can choose from the SQL Server Native Client ODBC driver or the OLE DB provider. Both options allow you to take advantage of the full set of features of SQL Server 2008 and the choice will usually be based on your application requirements.

Additional data access options include Windows Data Access Components (WDAC)-new in Windows Vista, previously named Microsoft Data Access Components (MDAC)-and the Microsoft SQL Server 2005 JDBC Driver for Java environments. For purposes of this discussion, I’ll focus on SqlClient and SQL Server Native Client; for more information on these and other data access technologies, visit http://msdn.com/data.

To get the most out of the new functionality, you’ll need to use .NET Framework 3.5 or SQL Server Native Client 10.0 which works side-by-side with previous versions. One important takeaway is that you don’t have to rewrite your application from the ground up to take advantage of SQL Server 2008. Instead, you can gain significant value from incremental changes to your existing data access layer. With that said, let’s dive in.

New Programming Constructs: Table-Valued Parameters

One of the most requested new features by developers is the ability to cleanly encapsulate tabular data in a client application, ship it to the server in a single command, and then continue to operate on the data as a table in T-SQL. The simplest such use case is the long-desired array data type. Traditionally, applications solve this need by doing one of the following:

• Defining stored procedures with large numbers of parameters and pivoting the scalar parameter data into rows.
• Using an out-of-band bulk insert mechanism like SqlBulkCopy in ADO.NET to create a temporary table.
• Using parameter arrays in the application and repeatedly executing logic that operates on a scalar “row” of data.

None of these solutions is ideal. The pivoted parameter solution, in addition to being ungraceful, creates code that is difficult to maintain that’s also tough to move forward when the time comes to add a new “column” to the conceptual “table.” The bulk insert solution is clumsy when the application needs to do any additional filtering or apply more complex logic. The parameter array solution, while it may perform well for small data volumes, becomes untenable for larger batches both on the client where memory consumption may become a problem and on the server where per-row invocations of the procedure provide non-optimal performance.

Table-valued parameters (TVPs), believe it or not, address all of these problems. TVPs provide an improved programming model and significant performance benefits in certain scenarios.

Imagine a simple order processing scenario. Using TVP starts with defining a table type in T-SQL on the server:

Next, create a stored procedure that uses the table type you just created and additionally takes the customer who placed the order as a parameter:

This example is fairly simple, but it illustrates a big win for developers, which is that if you can update an application to implement its business logic to use set-based operations on a batch of data, it should see significant performance gains.

Here are the application changes needed to use table-valued parameters. When using table-valued parameters, client applications generally have two possible programming models:

• Bind in-memory table data as a parameter. This is usually the simplest and fastest to code at the expense of not being as scalable in the application for large batches of data due to increased memory consumption.
• Stream row data to the provider from a disk or network-backed data source. This model takes a bit more code to set up with the advantage of having a fixed memory usage profile in the application.

For small batches of data, the performance difference on the client will usually be negligible, so choosing the simpler programming model may be your best choice. On the server side there will be no performance difference between the models.

Diving into the details for a moment, ADO.NET accomplishes the first model by extending the SqlParameter class to take a DataTable as a value. DataTable’s ubiquity in data application programming makes it an ideal choice for this simple model. SQL Server Native Client OLE DB accomplishes the same model by leveraging the COM IRowset interface and by introducing an implementation that allows buffering. SQL Server Native Client ODBC uses a similar method modeled after parameter arrays where applications allocate and bind arrays of buffers. For the streaming model, ADO.NET supports specifying DbDataReader and IEnumerable<SqlDataRecord> as a parameter value, which provides a solution for both external and user-defined data sources. In much the same vein, SNAC OLE DB accepts a custom IRowset implementation and ODBC builds on the data-at-execution (DAE) paradigm by accepting DAE table-valued parameters. All providers also expose a rich set of services for discovering and describing table-valued parameter metadata.

When you can update an application to use table-valued parameters, it should gain the benefits of having cleaner, more maintainable code in both the client and server application tiers; faster performance, particularly when you use set-based operations on the server side by leveraging the power of the SQL query processor; and better scalability for large data volumes. In addition to enhancing the programming model, SQL Server 2008 introduces new intrinsic data types to better align with the precise needs of applications.

By: Vaughn Washington

Vaughn Washington currently leads the development team for native data access in the SQL Server division at Microsoft; Vaughn is responsible for building and maintaining Microsoft’s world class data providers for SQL Server: ODBC and OLE DB. His responsibilities include delivering scalable, performant, and secure client components that are consumed by Microsoft internal and external customers through APIs that adhere to industry standards and expectations while showcasing SQL Server’s unique value proposition.

vaughn.washington@microsoft.com