Java开发网 - [转][En]Yukon--下一代的SQL Server

Java开发网

您没有登录

» Java开发网 » Database/JDBC/SQL/JDO/Hibernate » MS SQL

打印话题 寄给朋友 订阅主题

作者

[转][En]Yukon--下一代的SQL Server [精华]

发贴: 0
积分: 0

于 2002-08-24 07:08

One of the pleasures of attending the Microsoft Professional Developer's Conference (PDC) is learning about upcoming technologies. The 2001 PDC was no exception. For several months, Microsoft has been leaking information about Yukon, the next release of SQL Server, which is due in mid-2003. The most tantalizing piece of information was the announcement that Yukon would be tightly integrated with the .NET Common Language Runtime (CLR), and would let you write stored procedures in any .NET language. In his talk at the PDC on Yukon, José Blakely, an architect on the SQL Server team, discussed how Microsoft plans to implement this functionality. In this article, we'll summarize the main points covered in this talk and discuss how they will affect the way you write code in the next version of SQL Server.

SQL Server and the CLR

You will no longer be required to use Transact-SQL to write SQL Server stored procedures, triggers, and user-defined functions. You'll be able to create these objects using any of the .NET languages -- VB.NET, C#, C++, or even COBOL.NET -- and compile them into .NET assemblies. These assemblies will be deployed inside a SQL Server database and run by the CLR, which will be hosted inside the SQL Server memory space.

One important benefit of relying on the .NET Common Language Runtime is that it can verify that all code hosted by SQL Server won't cause any memory usage errors that would bring down the server. In addition, SQL Server will benefit from the CLR's robust support for versioning and security. The goal of the SQL Server design team is that all .NET code executing in SQL Server will be at least as safe, secure, scalable, and fast as Transact-SQL, which will continue to be fully supported.

ADO.NET and SqlTypes

Data access in Yukon will be based on a new set of managed interfaces in ADO.NET. This new set of ADO.NET classes will be grouped within a namespace that is currently being called System.Data.SqlServer, and these classes will interact directly with SQL Server's internal query processing mechanisms. By running within the SQL Server process, ADO.NET code written using these interfaces will provide a level of performance previously unavailable outside Transact-SQL.

The .NET Framework already includes a namespace, System.Data.SqlType, that provides data types compatible with the intrinsic SQL Server types, such as int, money, varchar, etc. These managed types let your .NET code perform comparisons that yield the same results you would obtain with Transact-SQL. Also, they support null values, which aren't supported by the standard .NET types, and they let you set scale and precision for decimal types just as you would in SQL Server.

Here's a VB.NET example of a class called ShippingCosts that makes use of the existing SQL-Server-compatible types and gives a glimpse of what will be available in the new System.Data.SqlServer namespace. The FreightByShipper method returns a SqlMoney value, which maps to the money data type in SQL Server.

 
 Imports System.Data.SqlServer 
 Imports System.Data.SqlTypes 
  
 Public Class ShippingCosts 
  Public Shared Function FreightByShipper( _ 
     shipper As String) As SqlMoney 
    Dim cmd As SqlCommand = SqlContext.GetCommand( ) 
    cmd.CommandText = "select sum(o.freight) as freight " _ 
      & "from orders o join shippers s on o.shipvia = _ 
      s.shipperid " _ 
      & "where s.companyname = @CompanyName "  
    Dim param As SqlParameter = cmd.Parameters.Add( _ 
     "@CompanyName", SqlDbType.NVarChar, 40) 
    param.Value = shipper 
    Dim amount As SqlMoney = cmd.ExecuteScalar(); 
    Return amount 
  End Function 
 End Class

The most notable portion of this code is the call to SqlContext.GetCommand(), which lets your VB.NET code hook directly into the SQL Server query processing engine, running in the same memory space as your code.

Using System.Data.SqlTypes means you don't have to do anything special to handle null values. In the following code example, the Tax method returns a SQLDouble, and if any value in the calculation is null, the result will also be null:

 
 Imports System.Data.SQLTypes  
 Public Class Finances 
 Public Shared Function Tax(salary As SQLDouble) _ 
    As SQLDouble 
  If (salary < 50000.0 ) Then 
    Return salary * 0.15 
  ElseIf (salary >= 50000.0 And salary <= 90000.0) Then 
    Return salary * 0.23 
  Else 
    Return salary * 0.35 
  End If 
 End Function 
 End Class

Code safety and security

Every .NET assembly deployed inside SQL Server will be verifiable, which means it will contain code the CLR can verify to be safe in the way it writes to memory.

SQL Server will also leverage the Common Language Runtime's code access security model. By default, code doesn't have any permissions to create a graphical user interface, create threads, access the file system, or call unmanaged code. The only permissions implemented are those granted for in-process SQL Server data access.

You will still set permissions in SQL Server, and your managed code won't be able to do an end run around those permissions to access system resources. Administrators will control the permissions granted to assemblies using a standard .NET machine and user-level security policy. At runtime, any code accessing protected resources produces a stack walk that triggers a permissions check against that code and any code that called it.

To simplify security administration, SQL Server will support these three standard permission sets for .NET assemblies:

SAFE -- This is the default permission set. It allows internal computation and data access. There is no access to resources outside SQL Server. Calls to unmanaged code aren't allowed. Code must be verifiable.

EXTERNAL_ACCESS -- The caller has to have these added permissions to create tables, assemblies, or other objects.

UNRESTRICTED -- This is similar to running extended stored procedures today; code can access any resource. Only system administrators (members of the sysadmin fixed server role) can run unrestricted code. This level allows calls to unmanaged code, and can be unverifiable.

Loading an assembly

When you're creating .NET assemblies in SQL Server, the new Transact-SQL syntax CREATE ASSEMBLY lets you load an existing assembly by specifying a file path. The CREATE statement loads the bits from the specified DLL and stores them in the database, as shown in the following code:

 
 CREATE ASSEMBLY Geom FROM '\\m1\types\geometry.dll'  
  WITH PERMISSION_SET = SAFE 
  WITH AUTOREGISTER

Code permissions are assigned on a per-assembly basis, with SAFE being the default permission set for all assemblies. WITH AUTOREGISTER marks the assembly as a stored procedure, trigger, user-defined function, etc., based on custom attributes you add to your .NET code. Assemblies are stored in databases and are backed up and restored with the data. You can remove assemblies using the familiar Transact-SQL DROP statement:

 
 DROP ASSEMBLY Geom

Altering assemblies will not be allowed to invalidate persistent data or indexes. For example, suppose you have an indexed, computed column that relies on a .NET function to perform the computation. Changing or dropping this function would invalidate any data stored in that index. Dependencies are tracked, and you can't drop an assembly if dependencies exist. However, you can override this restriction by using the FORCE option, which lets you alter or drop assemblies with dependencies. You should, of course, use this option with caution, but it's there when you need it.

Creating functions, procedures, and triggers

The CREATE keyword creates functions, procedures, triggers, and types. You can even create custom aggregate functions to supplement the one built into Transact-SQL.

You can call both scalar and table-valued functions from queries. Class functions have to be shared, as in the previous examples. This means you can call the functions without creating an instance of the class. Here's an example of how you would create a user-defined function from a shared method called Distance, in the Point class, in the Geom assembly:

 
 CREATE FUNCTION Distance ( 
 @x1 int, @y1 int, @x2 int, @y2 int )  
 RETURNS float         
 EXTERNAL NAME 'Geom:Point.Distance'        
 DETERMINISTIC 
 RETURNS NULL ON NULL INPUT

The DETERMINISTIC option at the end of the statement specifies this function always returns the same output value given the same set of inputs, which lets you use it to create indexed columns. The last line of the statement specifies that the function should return a null value if any of the input values are null. Here's how you could then call the function from a query:

 
 SELECT s.name FROM Supplier s 
 WHERE dbo.distance( s.x, s.y, @x, @y ) < 3

The syntax for creating a stored procedure from a class method is just what you'd expect:

 
 CREATE PROCEDURE check_inventory  
  EXTERNAL NAME 'events:Inventory.check_level'

The following example creates an AFTER trigger named supplier_event on the supplier table. As with functions and stored procedures, triggers based on .NET class methods work the same way they do in SQL Server 2000 after you've created them.

 
 CREATE TRIGGER supplier_event ON supplier  
  AFTER INSERT, UPDATE  
  EXTERNAL NAME 'events:Notif.Supp_Event'

This trigger will run automatically after any inserts or updates to the supplier table, and it has access to the same inserted and deleted tables available to all triggers; these special virtual tables provide the trigger with the old and new values in rows that have been modified. Dropping the trigger uses the familiar DROP syntax:

 
 DROP TRIGGER supplier_event

Creating user-defined types

One of the most interesting CLR-based innovations in Yukon is the introduction of user-defined types based on .NET classes. The values stored in columns based on these types will serialize objects, complete with all the properties and methods defined for the class.

Here's how you create a type named Point, based on the Point class in the Geom assembly:

 
 CREATE TYPE Point  
  EXTERNAL NAME 'geom:Point'

You can then use the type to define a column of that type in a table, as shown in the following example, where the Location column is defined using the Point data type:

 
 CREATE TABLE Supplier ( 
  SupplierID INTEGER PRIMARY KEY, 
  CompanyName VARCHAR(20), 
  Location Point )

Here's how you could then call the Distance method of the Point object stored in the Location column -- in this case, finding all suppliers that are within a certain distance of a geographical point that's passed in as a parameter:

 
 SELECT s.name FROM Supplier s 
 WHERE s.Location::Distance(@point) < 3

Performance

In Yukon, you can encapsulate your middle-tier logic within server-side components and still have all the advantages of running as compiled machine code, not interpreted Transact-SQL. This won't make much of a difference for code that's primarily performing data access; the goal for Microsoft is that CLR data access code will execute as fast as the equivalent code written in Transact-SQL. However, for code with complex logic, or with many computations, compiled CLR code brings a significant performance gain.

Transact-SQL when many computations are performed.

Figure 1 shows a performance graph that was distributed at the conference representing current benchmarks from Microsoft's internal testing with pre-alpha versions of Yukon. The timings were based on 100,000 rows with a varying number of instructions executed for every row. The horizontal axis shows increasing complexity of the test functions, moving toward the right. The vertical axis shows query execution time; lower numbers indicate better performance. When there are few operations in the functions, Transact-SQL gives better or equivalent performance to .NET code. However, as the number of operations increases, Transact-SQL gets slower and slower, while the time required to execute the .NET code hardly changes. This is a direct result of running compiled rather than interpreted code.

Scalability

Relying on .NET code won't hamper the scalability of your SQL Server database operations. Yukon's ability to handle a given number of concurrent users with a given set of hardware resources will be as good as (or better) than that of SQL Server 2000.

Microsoft is developing tight collaboration between the memory management algorithms in SQL Server and in the CLR. For example, SQL Server can trigger CLR garbage collection when resources are scarce, and the garbage collector can call back into SQL Server to free up SQL Server memory when it needs to. The SQL Server team has significantly improved SQL Server's scalability in recent versions, and they've assured us these gains won't be sacrificed to integrate with .NET code.

Ongoing support for Transact-SQL

If you already have a significant investment in existing Transact-SQL code, there is no need to worry. Transact-SQL will continue to be fully supported. Transact-SQL itself will be enhanced to support new functionality not currently available. When asked about SQL-99 compatibility, Mr. Blakely stated it wasn't clear yet whether Transact-SQL would be SQL-99-compliant; it is SQL-92-compliant today.

Visual Studio.NET and development tools

It's too early to tell what the SQL Server tools such as the Query Analyzer and the Profiler, will look like a year or two from now. However, you can count on added support for SQL Server in Visual Studio.NET. This will let you take advantage of the rich debugging environment in Visual Studio.NET. Some traditional Visual Studio capabilities will be redesigned to support deployment of SQL Server assemblies.

There will be assisted creation of CLR routines, types, and aggregates, with templates for the basic starting points, similar to the way SQL Server 2000 handles templates today for creating new SQL Server objects. You'll be able to create both Transact-SQL and CLR code with IntelliSense dropdowns and command completion, including lists of the available tables and columns.

Visual Studio.NET today supports debugging of SQL Server stored procedures. You can test this by setting up a Server Explorer connection to SQL Server, setting breakpoints in your VB.NET or C# code, as well as in your stored procedure, and pressing the F5 key. If you have SQL Server debugging enabled in your project properties, you can seamlessly step through your .NET code and Transact-SQL, as shown in figure 2. In this example, a breakpoint was set in a VB.NET procedure, which stepped into a C# class, which called the Ten Most Expensive Products stored procedure in the Northwind SQL Server database.

A new developer tool called the SQL Server Workbench will support deployment of assemblies to multiple servers and will contain a powerful subset of Visual Studio.NET capabilities for code management. It's too early to tell what the SQL Server Workbench will actually look like, but it will most likely resemble the user interface currently available in Visual Studio.NET. For that matter, it's too early to tell what the full feature set of any of the SQL Server-specific tools will eventually be; they're still in the early stages of development. However, it's clear the SQL Server and Visual Studio teams are already working closely with each other to improve their integration.

Summing up

The next version of SQL Server will provide a much richer programming model than what's available today, supporting all the .NET languages. SQL Server will host the .NET Common Language Runtime, leveraging its support for object-oriented design, code safety, security, scalability, and speed. Data access will be provided by a new System.Data.SqlServer namespace in ADO.NET, which will hook directly into the query execution engine of SQL Server. You will be able to create and debug functions, stored procedures, triggers, and types.

This change will no doubt shake up a number of database administrators, and it will challenge many long-held convictions about the need for a division between middle-tier and data-tier objects. But these tiers have always been logical, not physical, and any development that improves performance, maintainability, and security will be hard to resist. So, if you've been wondering whether you need to learn about the new .NET languages, here's one more good reason to do so![img][/img]

九佰 edited on 2003-07-25 17:51

已读帖子

新的帖子

被删除的帖子