SNAC'ing on the new data types

With a new database server comes a new client.  SNAC (SQL Native Access Client) 10.0 introduces new functionality that developers should be aware of, to ensure backwards compatibility and "Y2K-style" issues don't crop up with calculations.

For new types with unlimited size the maximum size is represented as SQL_SS_LENGTH_UNLIMITED (value 0) in ODBC and ~0 in OLE DB. The APIs uses different values because each API has its own convention for representing values of unlimited size (and these conventions pre-date SNAC). Applications which do arithmetic based on the size returned in parameter and result metadata and which don't take account of this special case may misbehave. This is one reason why we recommend that applications moving to new driver/provider versions should not be deployed without thorough testing. Other version differences are described in SQL Server Books Online.

Microsoft SQL Server Native Client team blog : Microsoft SQL Server Native Client and Microsoft SQL Server 2008 Native Client

As it relates to HierarchyID below, in < SQL 2005 it is read as a binary blob image column, in SQL 2005 it is a varbinary column, in SQL 2005 it's a User-defined Type. 

More info on the new SQL 2008 features and UDTs.

UDT and CLR changes

SQL Server 2005 has a maximum length of 8k bytes for all user defined types (UDTs).  SQL Server 2008 has upped this limit to varbinary(max) – 2 GB.  This was needed for the TVP custom types.  You can now build up the structure on the client and pass it to the server in a single call as a Table Value Parameter.  Of course older clients will not know this, and they will be told the type is a varbinary(max) rather than the new TVP. The CLR runtime (the Dot Net runtime hosted inside SQL Server) was previously limited to 8k of state space as well.  This has been changed to also be 2GB.

The idea being that more stored procs will be written in C#/VB.Net in the future and the complex needs could not be met with the smaller state.  Now through TVPs you can pass GB’s of data to a stored proc (is that REALLY such a good idea?). Static methods in managed assemblies are now treated as User Defined Functions (UDFs).  This is something we have been working on within VistaDB as well.  And users can now create multi column user defined aggregates; you were previously limited to a single column.

http://www.vistadb.net/blog/articles/sql-2008-katmai-overview/