Yukon: a story of love (and not so much love)

I'm heading for Helsinki at the moment to deliver another Workshop on SQL Server 2005 (formerly known as SQL Server Yukon) so I have some time to reflect on the previous deliveries of this workshop.

The topics that I'm delivering myself are the developer related topics. One could categorize them as follows:

• Yukon – CLR Integration
• XML support in Yukon
• T-SQL improvements
• ADO .NET 2.0
• Objectspaces
• I’m obviously missing some topics here like the SQL Server Service Broker, Notification Services…

The most loved topic is without any doubt the Yukon CLR.

• People just see the productive boost this will bring for some types of procedural code they are writing to day in T-SQL.
• Many people also see that this will enable a lot of scenarios that were previously impossible if one didn’t want to fall back on extended stored procs.
• User defined aggregates are welcomed
• User Defined types are something different. Many people don’t see where they could apply this after I tell them they shouldn’t use UDT’s to store for instance their customer objects. When thinking about a UDT, please think about new scalar types you might need. Classical example already is a location type with longitude/latitude and some methods to compare two points.
• THE DANGER of offering the users to write .NET code and deploy it in SQL Server, lies in the fact that people are tempted to wrap any trivial SQL Statement in .NET code. That is NOT what it’s meant for! It’s not a rip (T-SQL) and replace (.NET code) story! It’s about using .NET code where it makes sense:
  • There are things that can only be done in .NET (UDT’s and User Defined Aggregates)
  • When you have more business logic in you procedure than raw data access
  • When using functions from the .NET Framework makes you more productive as well as your code more performing.

The topic adored by some while invoking a big yawn from others: XML Integration

• People who are in a vertical industry that requires them to work with XML schema (for instance e-learning) love it.
• People who work with XML data today, love it.
• But apparently there are still a lot of people who don’t want to work with XML in an explicit fashion. If a protocol uses it (soap), fine. If a tool uses it (for instance RDL in Reporting Services) fine. If they need to directly work with XML data, XPath, XQuery, SAX, etc… they rather not. Last week in Istanbul, nobody was really running warm for XML support. A big surprise to me. Although…I can also see why. XML isn’t simple. SOAP isn’t simpleJ It’s a great thing to have for interop, for tool builders, for storing hierarchical data and semi structured data and maybe a million other reasons, but it’s not simple. I too am longing for the day I won’t see a single tag again… (hopefully before I die).
• Nevertheless, we are taking big strides with XML integration in .NET. Yukon will be the store for all types of data. Will of some tool support like for instance the XQuery builder and will allow SQL Procedure to be exposed directly as Web Services. This is fundamental. SQL Server will understand SOAP as well as will understand TDS. Works on Windows 2003 and Windows XP SP2. No IIS needed.

The most wrongly ignored topic: T-SQL improvements

• There is so much goodness here!
  • Common Table Expressions (hierarchical queries)
  • Improved WAITFOR offers queuing in the database.
  • Pivot and Apply
  • Out from insert update and delete statements
    For instance:
    insert into MyTable

output inserted.id
values (x,y)

• Ranking functions
• TRY CATCH for transaction abort handling
• ‘Execute as’ to have a stored proc run using a fixed identity you can specify.
• DDL Triggers: Trigger on CREATE/ALTER/DROP statements
• Too much goodness to sum up. I need to spend more time here!

The most debated topic: Objectspaces

This is a real hot debate. Java developers have this debate and now I’m afraid Microsoft developers will have the debate as well. I know it’s not a SQL Server Yukon only topic but it’s hard not to discuss data access when talking databases.

·         My take: a good designed DAL and framework can be as efficient as an Object Relation Mapping Framework. Optimizing data access when using an OR Framework will be a challenge or will require effort comparable to building a proprietary DAL in many cases. I would only use frameworks like this if:

o        There’s a lot of business logic running on the server with little user involvement

o        I need to build an app on a database I don’t own with a very complex schema (let’s take SAP as an example).

o        My boss tells me to

·         Some ISV’s take on this:

o        Way cool! Every SQL statement I don’t have to write is saving me money.

o        Hey, I wrote this already years ago!