Anticipated new Data Types in Microsoft SQL Server 2008

The next release of Microsoft SQL server, version 2008, promises a number of significant changes over its previous 2005 version release.  From a Developers perspective, there is much anticipation over the new data types that we will get to play with in our stored procedures and other database objects. I’ll go through some of the new data types in this post.

New Date data types:
Date:-The new Date data type allows you to store dates without a time component from 0001-01-01 to 9999-01-01. This new type will lend itself well to situations where a date variable doesn’t need to have a time attached to it...like a date of birth field/variable. There is also the Time type, which as you might guess, stores a time value, minus a date portion. Already I'm seeing situations where these Data types would have saved me a few lines of code had hey been available earlier. Other new date types include DateTime2 and DatetimeOffset.

New HierarchyId data type
According to the Microsoft documentation, the Hierarchyid is a new data type that can store values that represent nodes in a hierarchy tree. This data type, which has a flexible programming model, can be implemented as a Common Language Runtime User-Defined Type (CLR UDT). The CLR UDT exposes several efficient built-in methods for creating and operating on hierarchy nodes. I am eagerly awaiting further details on the use of this new type, but at first glance, it seems we will now be able to store the hierarchical metadata that describes things like menu trees etc in a format that is easily retrievable. Building menus etc could be as easy as binding a treeview control to a field returned from a database or web-method call. I hope my understanding of this new type is not too far off from what it actually is. Time will tell.

UserDefined Table type:
Again, the Microsoft documentation described this new type as follows: A user-defined table type represents the definition of a table structure. You can use a user-defined table type to declare table-value parameters for stored procedures or functions. You can use this table type to declare table variables that are to be used in a batch or in the body of a stored procedure or function. To ensure that the data in the table type meets specific requirements, you should create unique constraints and primary keys on the table type. Actually, this is a neat addition. If I understand this correctly, we can pass in an array (table) of parameter items into a stored procedure as a unit. So instead of passing in 50 individual parameters into a Stored Procedure, It’s now possible to pass in a single UDT type that holds all 50 parameter bits that need to get to the Query.

FILESTREAM storage
Now here is where it gets interesting. It’s now possible to store data directly to the file system on the server via the FILESTREAM data type. It allows you to store unstructured data directly in the file system. You can use the new storage type VARBINARY(MAX) FILESTREAM to define table columns and store large binary data as files in the file system instead of storing them as Binary Large Objects (Blobs). In addition, you can use T-SQL statements—SELECT, INSERT, UPDATE, or DELETE—to query and modify FILESTREAM data. You can use the rich set of streaming APIs provided by Win32 for better streaming performance, while maintaining transactional consistency. You can also apply SQL Server functionalities to FILESTREAM data such as triggers, full-text search, backup and restore, SQL permissions, Database Console Command (DBCC) checks, and replication.

Sparse Columns
Typically, if you have a column/field in your database table that is infrequently used, and contains a null value in most cases, the new Sparse Columns feature provides a more efficient way to represent these.

In wrapping up, its also worth mentioning that the UDT types, introduced in SQL Server 2005, have undergone some changes in the 2008 release. Previously UDT types were limited a size of 8K....that limitation has been removed.