Programming SQL Server 2008 (3)

Breaking Barriers: Removing Size Limitations

Here is a close look at new support for large common language runtime (CLR) user-defined types (UDT) and the introduction of storing large object (LOB) column data transactionally in the file system.

Support for .NET Framework CLR UDTs first appeared in SQL Server 2005 with the goal of providing database application developers a more flexible programming model. UDTs gave developers a way to create complex types and also to express and encapsulate computationally expensive business logic alongside that data. Since the release of SQL Server 2005, customers have been adopting the technology and using it in interesting ways; however, the current 8,000-byte maximum size has limited the set of scenarios UDTs can address.

SQL Server 2008 removes this restriction allowing a CLR UDT to be “unlimited” length. In practice the storage size is actually limited at the SQL large object (LOB) limit of 2^31 -1 bytes, or about 2 GB, in much the same fashion as the varbinary(max), varchar(max), and nvarchar(max) types that were introduced in SQL Server 2005. Theoretically, you can expose any .NET Framework CLR class or structure as a SQL CLR UDT as long as it meets several requirements involving constructors, annotations, and implementing a set of interfaces. Large UDTs in particular must implement a user-defined, serialization-to-bytes method that SQL Server relies on both when consuming parameters of that type from clients and when sending column result sets. A value of Format.UserDefined in the SqlUserDefinedTypeAttribute annotation indicates that the UDT has user-defined serialization and also introduces the requirement for the developer to implement the IBinarySerialize interface.

The analogy for other “max” types remains useful when describing how to take advantage of large versions of UDT in client applications. Integrating into applications currently using UDTs should be seamless, with only minor changes in metadata returned for columns and parameters of these types to allow discriminating applications to distinguish large versions from their less-than-8,000-byte counterparts. To see this difference, ADO.NET applications for result sets will invoke the SqlDataReader GetSchemaTable() method that returns a DataTable and examine the ColumnSize column where a value of “-1” indicates a large UDT. For parameters, they’ll examine or set the SqlParameter Size property where “-1” has the same meaning. In ODBC, you specify and distinguish large UDTs by the use of the SQL_SS_LENGTH_UNLIMITED macro originally introduced for “max” types. In OLE DB, you represent the large UDT column or parameter length as “~0”.

In addition to being able to fully utilize the functionality of the CLR UDT on both server and the application tier when working in ADO.NET, using user-defined serialization can allow native applications to access a UDT as a stream of bytes with a well-defined format. This, in turn, enables scenarios where applications interpret these bytes by parsing or by overlaying structure and provide similar business logic in middle tiers or client applications to what exists as CLR methods in the assembly. While these techniques can solve a range of scenarios involving structured data, a different limitation removal assists the growing number of document management-style applications.

On the unstructured data side of the fence, SQL Server 2008 also enables applications to store unstructured data directly on the file system-outside of the database file-leveraging the rich streaming APIs and performance of the Windows file system. Though accessible through the file system, the data also remains compatible with the T-SQL programming model. Using this dual programming model-T-SQL and Win32-applications can maintain transactional consistency between unstructured data stored in the file system and structured data stored in the relational database.

You use this functionality-SQL Server Filestream-by adding a new storage attribute, FILESTREAM, on varbinary(max) columns, a type introduced in SQL Server 2005. The beauty of the feature is that other than removing the 2 GB LOB storage limitation, filestream columns work seamlessly in other operations, including data markup language (DML) operations like SELECT, INSERT, DELETE, and MERGE. In case you haven’t already heard, MERGE is yet another new SQL Server 2008 programmability feature that allows expressing multiple other DML operations in one statement, but that’s a detail for another day.

Getting back to Filestream, take a look at how this is exposed in different data access stacks. Because the only real change from the database application perspective is the difference in maximum size, existing programming patterns continue to work unchanged. Having said that, once you’ve made the transition to storing large data sizes, streaming data in your application both into and out of the server becomes more important for scalability. Even though coding patterns for streaming haven’t changed in SQL Server 2008, it’s worth doing a quick refresher. For ODBC, the application binds parameters using SQLBindParameter with ColumnSize set to SQL_SS_LENGTH_UNLIMITED and sets the contents of StrLen_or_IndPtr to SQL_DATA_AT_EXEC before it calls SQLExecDirect or SQLExecute. You must unbind and retrieve result columns via SQLGetData. For OLE DB, the application uses DBTYPE_IUNKNOWN in parameter and result bindings. For parameters, the consumer must implement an ISequentialStream; for results, the provider returns an ISequentialStream interface. For optimal streaming performance, applications can use a Win32 file access API to read or write data using a UNC path returned by new server built-in function PathName() available from filestream columns. The advantage of streaming using Win32 API over T-SQL grows as the data grows in size and the benefit can be seen as early as data 1 MB in size. SQL Server Native Client also includes a new method, OpenSqlFilestream, that combines and simplifies the operations of opening the file and associating it with an existing SQL Server transaction context using another new server built-in function, Get_filestream_transaction_context().

Building on New Foundations: System Types

Beyond the plumbing and infrastructure improvements discussed so far, SQL Server 2008 introduces two new CLR system types as out-of-the-box solutions for common application scenarios:

• HierarchyId. Allows database applications to model tree structures like organizations or file systems in a more efficient way than currently possible. The type includes a rich set of functions for answering questions about relationships between nodes in the tree and reorganizing the tree.
• Geometry. Implements the Open Geospatial Consortium (OGC) geometry type and encapsulates the entire OGC type hierarchy.

These CLR system types are available in every database and the CLR assemblies are available as a standalone redistributable package for applications to install with classes named SqlHierarchyId and SqlGeometry respectively. This allows .NET Framework applications in the middle tier or client to interact with the type like any other class. Non-.NET Framework applications will generally rely on server-side conversion or serialization to a documented well-known format. In the case of the Geometry type, applications will be able to retrieve the serialized bytes in a well-known format by using a new built-in function when issuing queries, choosing between STAsText() or STAsBinary(). These provide, respectively, the Open Geospatial Consortium (OGC) well-known text and well-known binary format. Alternatively, for all system types, developers can create user-defined functions on the server that operate on the data providing access to the full functionality of the type.

Putting the Pieces Together

In summary, SQL Server 2008 introduces a new programming construct, table-valued parameters, to more rationally model scenarios where applications are operating as tables of data. Table-valued parameters provide better performance and scalability than existing solutions, particularly when you can implement server-side logic with set-based operations. Along with this new programming construct, new intrinsic data types more efficiently handle date and time data and will help developers reduce custom code in applications by choosing a type that closely matches their needs. This comes with the benefit of aligning database storage costs to those same needs. For existing types, SQL Server 2008 removes two limitations: lifting the 8,000-byte size limit on CLR user-defined types and allowing you to store and access binary large object columns through the file system. Building on enhanced CLR type support, SQL Server 2008 also introduces two system types to provide out-of-the-box solutions for planar spatial and hierarchal data. This is far from an exhaustive list of new programming features, but it should start to get you thinking about how you can get the most out of SQL Server 2008.