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.

One of my favorite new features of SQL Server 2008 is extended events. I've written a bunch of blog entries on 'em (use the search, type in Extended Events). So a few days ago, I recieved an email from Jonathan Kehayias directing me to his new program on Codeplex, the SQL 2008 Extended Events Manager, asking for my opinion and suggestions.

Well, my opinion is "I like it a lot". And one of my first suggestions was a starter help file, because those of us who are sometimes "GUI challanged" might miss features upon first glance. And every new dialog I discover enforces my appreciation for the program's usefulness.

So here's a short starter walkthrough.

1. When you bring up the program, an empty window appears. Choose File-New Connection from the menu to get a connection to an instance. You can change connection but you can only have one connection open at a time.
2. A treeview appears in the lefthand pane of the main window. It shows information about your current event sessions. An event session consists of one or more events. Events contain event fields, actions, and can contain predicates. Each event session has a target with options related to the target specified.
3. Each event session has a context menu. You can Edit or Drop the event session, Script the event session for CREATE or DROP, and Stop and Start the event session.
4. Choosing New Event Session from the context menu on the (top-level) instance node of the tree or choosing Edit Event Session on an existing event session brings you to the Session Editor dialog.
  a. For a new session, you need to enter the session name.
  b. Clicking the hyperlink for Add Event brings you to the Event Editor dialog. Here you can choose events, actions, and predicates with the help of "search terms" that help you locate the event you want. There's even a Predicate Editor.
  c. Clicking the hyperlink for Add Target brings you to the Target Editor.
  d. Saving an event session in the Session Editor creates it immediately or you can script the event session.
5. The Extended Events Metadata Viewer is available from the content menu of the (top-level) instance node as well. This dialog lets you browse graphically through the Extended Event metadata.

BTW, the program consists of two pieces, the GUI program and the ExtendedEventsManager library. The library is meant to be as SMO-like as possible (there currently are no SMO classes for Extended Events). This means that you could even load the library into...let's say PowerShell..and use it there also.

Post enhancement requests, bug reports, etc to the Codeplex project page.

Folks have always had trouble with the fact that ring orientation is required with spatial instances if you're using SQL Server 2008's geography data type, but not with the geometry data type. For an explanation of the need for this, reference Isaac Kunen's blog entry here and Ed Katibah's blog entry (link in next paragraph).

In Ed's blog entry, he provides a neat way to fix spatial instances that have the wrong ring orientation for geography by using the geometry type and calling a method that forces the required ring orientation for geography. You should read his entry from yesterday for background.

I thought this was such a cool mechanism that I coded up a SQLCLR UDF that encapsulates this behavior. It accepts almost any WKT and produces a "proper" geography type, regardless of the ring orientation in the WKT. The code is included with this post. The function is called GeographyFromAnyWKT. Well, almost any. There are certain edge conditions (such as a ring that actually would exceed a single hemisphere) that will produce and error, but at least you shouldn't have to worry about ring orientation. Thanks Ed!

As an aside, although this is a Visual Studio SQLCLR autodeploy database project, the function can't be autodeployed because it returns a SqlGeograhy type and this is not covered by the autodeployer code. So I've included a deploy script and a couple of test cases in the project. Although I define the UDF as RETURNS NULL ON NULL INPUT, I've also included (redundant) null checking in the function itself, just in case you want to change the function a bit. Enjoy!

I came across the following interesting behavior while testing a SQLCLR table-valued function that did work in SQL Server 2005 SP2 but doesn't work at all in SQL Server 2008. It appears to be by design, because the error message (in 2008) clearly indicates what's wrong. But the code worked in SQL Server 2005 and if you depend on this code behavior, it's a breaking change. And I haven't seen this in a readme file or BOL "What's New" section.

First, some background. .NET procedures are allowed to do any kind of "data access" including getting the Windows Identity, accessing the local database instanace, etc. .NET user-defined functions, however, are not permitted data access unless they are marked with a SqlFunction attribute specifying DataAccess=DataAccessKind.Read. Accessing certain session information requires SystemDataAccess=SystemDataAccessKind.Read as well.

.NET table-valued functions consist of a UDF function method and also a FillRowMethod. The UDF function method must return an instance of a .NET type that implements IEnumerable or IEnumerator. This can be a class that you provide or one of the build-in .NET types such as System.Array. SQL Server will call its Enumerator and call back to FillRowMethod once for every time the enumerator returns true.

In SQL Server 2005, you can do "data access" in the UDF method, the FillRowMethod, or any of the other methods in the class (like the enumerator's MoveNext method). Only the UDF method must be marked DataAccess=DataAccessKind.Read and only the UDF method CAN be marked with the SqlFunction attribute and produce the desired effect.

In SQL Server 2008, attempting to do data access in the FillRowMethod now throws an exception. Perhaps the behavior change was required to implement a new SQLCLR feature, ordered TVFs, but I'm only guessing that ordered TVFs are the reason. Perhaps it was never intended to work. The error message in 2008 is pretty clear:

"System.InvalidOperationException: Data access is not allowed in this context.  Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method."

There's no workaround for this if you choose a table-valued function, except to do all your data access in the TVF method only. But SQLCLR provides another way to way to stream a rowset of data you synthesize yourself, using SqlMetaData, SqlDataRecord, and SqlPipe methods in a SQLCLR stored procedure. You can get almost the same result (streamed rowset) in such a stored procedure and "data access" is always allowed in SQLCLR stored procedure code.