A Preview of SQL Server Yukon From a Developer's Perspective...

The intention of this article is to provide a brief overview of the main features of the new version of SQL Server (Yukon) that will be of interest to developers.

By: Chris Sully Date: May 13, 2004

Introduction

The intention of this article is to provide a brief overview of the main features of the new version of SQLServer (Yukon) that will be of interest to developers. Note that the information presented is based on the Beta 1 release. This is not available as of the time of writing, and has no fixed date for release, but is likely to be available in a month or two to MSDN Universal subscribers, and to the rest of us a little later.

With the Yukon release of SQLServer Microsoft have looked at the database engine again and have targeted developer productivity in particular. The results have primarily been:

• encouragement for organisations to leverage existing development skills by integrating .NET into SQLServer
• increased XML integration, targeting the developer with increased XQuery and XPath support, as well a new XML data type
• support for Web Services

We're going to take a look at these areas in the remainder of this article via a consideration under the following topics:

• CLR/ .NET Integration
• TSQL Enhancements in Yukon
• XML Support
• Web Services Support
• Other Application Services

CLR/ .NET Integration

The .NET Framework is integrated into Yukon in the form of the Common Language Runtime. This is good news because:

1. This provides us developers with a much enhanced programming model with full access to the power of the Base Class Library from SQLServer. You are no longer restricted to TSQL.
2. Along with the CLR come its benefits, in particular safety and security. Previously componentised functionality (e.g. system stored procedures) was delivered as in process DLLs, which meant if they failed they could potentially take SQLServer with them. With the CLR comes increased robustness.
3. User defined types and aggregates can be created in .NET languages.
4. Developers have access to a common (and much better) development environment (Visual Studio): tools are shared amongst the tiers from client through now to the data tier with authoring, debugging, deployment and profiling support.
5. Performance and scalability: for processor-centric tasks .NET is better in these regards with 3-4 times performance increase. Note that this is for computational rather than query tasks, where (T)SQL remains the preferred option – this is what it is designed for after all.

Taking up this last point: the .NET languages are by no means meant to replace TSQL. It’s horses for courses: TSQL is still best suited for data access for which it is optimised and also has the benefits of familiarity and a relative simple programming model. Managed code is all about procedural programming and is suited to computational tasks and also functionality that cannot be delivered (easily) by TSQL.

As you are no doubt aware a .NET class in VS.NET is compiled to an assembly/ DLL. There is a new TSQL command that allow cataloging of this assembly into SQLServer (CREATE ASSEMBLY). This effectively stores the assembly in a particular SQLServer system table. E.g.

CREATE ASSEMBLY name
FROM path
WITH permission_set

with permission sets (see below) allowing granular control of what exactly the code is allowed to do.

The assembly is then placed in sys.assemblies and related tables (sys.assembly_files, etc.)

Once the assembly is thus effectively 'registered' with SQLServer the developer can then further link to methods of the assembly and use these in stored procedures, for example. In this way you can create functions (via the new CREATE FUNCTION syntax, for example), triggers, types (data types), procedures and aggregates (like min, max, average, etc.).

The SQLServer Yukon environment hosts the CLR runtime in process with the database engine. This hooks into APIs provide by the Whidbey release of the .NET Framework for supporting facilities such as memory management, etc.

To support these new capabilities there is a new SQLServer project template in VS.NET Whidbey for Yukon. This has server debug integration and single step support (between languages and deployment tiers) and creates a standard .NET assembly.

CLR Security: permissions sets

Three permission sets are currently defined, the idea being that you should apply the least level possible for the application to work. In particularly you should try not to use 'unsafe', as the name suggests.

Safe

• Execute and data access permissions
• No access to resources outside SQLServer
• No unmanaged calls
• Must be verifiable (can verify the type safety of code)

External_Access

• Safe plus access to external resources
• Access to external resources via managed APIs
• SQLServer impersonates the caller for external access
• Must be verifiable
• Requires new external access permission to create

Unsafe

• Can call unmanaged code, can be unverifiable
• Only system admin can create

TSQL Enhancements in Yukon

• New data types: VARCHAR(max), NVARCHAR(max) and VARBINARY(max) which replace the text and image data types. Additionally there is the XML data types which we’ll return to in the next section
• Common Table Expressions (CTEs): these are recursive queries/ parent child relationships expressed like views
• PIVOT and APPLY operators: provide cross tab query capabilities
• Exception handling (a simplified version of .NET’s Try – Catch)
• Statement level recompile
• DDL triggers (as opposed to just DML - insert, update or delete - as previously)

XML Support

Yukon provides the native xml data type that can be used for columns, variables or parameters. By providing the xml data type, Yukon allows you to query portions of an XML document, validate that the document conforms to an XML schema, and even modify the contents of the XML document in-situ. XML data is stored as binary large objects (BLOBs) for easier retrieval.

An XML schema can be associated with a column of type xml. This provides validation for constraints, inserts, XML data typing against a schema, as well as optimizations for storage and query processing. Yukon also provides several Data Definition Language (DDL) statements for managing schemas on the server.

Further you can also:

• index xml columns, creating indexes on tags, values and paths
• use TSQL or XQuery to query against xml data
• create XML views of relational data

Web Services Support

In SQLServer Yukon developers can develop Web Services in the database tier. Support includes:

• SQL and stored procedures calls via HTTP/SOAP: thus allowing standards based connectivity from other operating system platforms, for example
• Native HTTP listening provided
• Standards based: SOAP 1.1/1.2, WSDL1.1, XSD
• Windows and SQL authentication (via SSL)
• Stored procedures can return results as a DataSet: this providing extra compatibility with the programming model of other application tiers

Other Application Services

The following are new features within Yukon:

• Service broker: allows internal or external processes to send and receive guaranteed, asynchronous messages using extensions to T-SQL with support for queues
• Notification services: scalable event based notification system with a variety of delivery options
• Reporting services: a rich additional presentational service offering processing of data from a wide variety of data sources and to a wide variety of output formats

There we conclude our brief overview of new features in SQLServer Yukon of particular interest to developers.

References

SQL Server Yukon Engine Webcast
Tim Sneath

An Overview of SQL Server "Yukon" for the Database Developer
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_ovyukondev.asp