Bounced over to the session on programming Yukon with Managed code (decided to skip the technical overview of ASP.NET 2.0, since that information will be publicly available soon enough).
By now, most people are aware that Yukon will allow them to write CLR code and host it inside the database. From the opening remarks of this presentation, it’s pretty apparent that MS is keen on this new ability to host business logic code inside the database – which is an interesting departure from architectural best practices of the past several years (which were, basically, “don’t put business logic in stored procedures”). There’s definitely a performance benefit to keeping code close to the data – and security gets a lot easier, too. The question is, how scalable is this deployment scenario?
From a code perspective, it seems pretty straightforward. Whidbey has a SqlServer project type, which automatically adds a reference to SqlAccess.dll. Inside this .dll is the implementation of the ADO.NET In-Process provider. Through this provider, the managed code inside of Yukon can talk to ADO.NET using standard ADO.NET syntax – except that the in-proc provider has no remoting boundaries to cross, so getting data in and out of this provider is bloody fast.
There’s a new [SqlFunction] attribute that you can use to expose instance methods to SQL Server – and the integration between Yukon and Whidbey will take care of installing the output assembly into Yukon. It’s unclear to me how those bits get shipped onto the local SQL server if you’re developing on a remote SQL server – I don’t know if the code gets shipped and then compiled on the server, or if the compiled assembly is somehow downloaded to the SQL Server instance.
Interesting things you need to do to make your own SQL UDT:
1) Slap a [SqlUserDefinedType] attribute
2) Implement INullable (since everything can be nullable in SQL)
3) Implement a Parse() and ToString() methods.
4) Compile.
Once the assembly is installed, it’s integrated into T-SQL. The CONVERT() function will delegate to your type’s Parse() method. You can call other methods on your type from within a SELECT statement via C++-style scope resolution, e.g:
use MyAssembly.MyNamespace;
declare @foo AS MyUDT
SET @foo = CONVERT( “HelloWorld”, MyUDT)
SELECT @foo::GetMessage
Other nifty things: streaming Table-Valued functions build in managed code (provide SQL Server with an iterator and it can stream rows from an arbitrary datasource and other SQL statements can consume this data as if it was a table). For example, you might create a custom managed TVF implementation backed by a data file in an arbitrary format and use the streaming API’s in System.File.IO to read in your data. This TVF can then be used in other SQL statements, just like any other TVF implemented in T-SQL.
