Visual Studio 2008 SP1 and SQL Server 2008 SQLCLR features

When SQL Server 2008 shipped, Visual Studio 2008 SP1 and .NET 3.5 SP1 shipped a few days later. Visual Studio SP1 contains some neat enhancements that allow you to use SQL Server 2008 databases in Server Explorer and the related designers. There's support for SQL Server change tracking in the Sync Services designer. The EDM and LINQ to SQL designers know about DateTimeOffset (datetimeoffset) and TimeSpan (time) in SQL Server 2008. Server Explorer also knows about geometry, geography, and hierarchyid. Very nice.

I often use the SQLCLR projects (that's Database Project under your programming language) that provide templates, auto-deployment of assemblies and CLR-based database objects, and an "Add Reference" dialog that allows adding references only to system libraries that are classified as "safe to use" (i.e. have been tested with SQL Server) and assemblies that already exist in your auto-deploy target database. Makes the base functionality more accessible to programmers. When I wrote samples of SQL Server 2008 functionality I didn't use those projects, because they didn't yet support some of the new functionality. So I thought I'd go back in and check.

Another reason I checked is because I sometimes get feedback from programmers on newsgroups/forums that, if its not exposed in Visual Studio (or in SSMS Object Explorer to give another example) the base functionality must not actually exist. You CAN program SQLCLR objects that use ALL of these features…just use an ordinary Class Library project and write your DDL by hand.

First thing I noticed is that there is no special SQLCLR project for C++. In VS2005 it was called "SQL Server Project" but in VS2008 it's gone. That leaves us with projects for C# and VB.NET. But not a lot of people coded SQLCLR in managed C++.

Next, I tried out my SQL Server 2008-specific features. Here's a rundown.
  The new supported libraries System.Core and System.Xml.Linq don't appear in Add References dialog.
  Autodeploy doesn't recognize the system .NET-based data types SqlHierarchyId, SqlGeometry, SqlGeography.
  Autodeploy knows about the mapping of DateTimeOffset in SQL Server 2008, but doesn't know about TimeSpan to SQL Server's time data type.
  Can't autodeploy a multi-input user-defined aggregate.
  Can't autodeploy a large user-defined aggregate (in fact it doesn't seem to like any UDAgg with Format.UserDefined).
  Autodeploy always maps the .NET type DateTime to SQL Server's datetime. But in 2008 DateTime can map to SQL Server's datetime, datetime2, or date. Perhaps an enhancement to SqlFacet.
  I can't specify "order by" for an ordered TVF during autodeploy.
  Using a nullable type as a parameter fails in autodeploy.

Bear in mind that these SQL Server 2008 features do work as advertised if you use Class Library projects and manual deployment (ie, roll your own DDL).

I did file a bug on Connect for these. Maybe it should have been an enhancement request instead, but I really thought SQL Server 2008 support would include these and they just weren't in the beta yet.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.