Makale
07/26/2011

clr tablo değerli işlevler

Bir tablo değerli işlev, tablo döndüren kullanıcı tanımlı bir işlev olduğunu.

İle başlayan SQL Server 2005, SQL Server sağlayarak, yönetilen tüm dil. bir tablo değerli işlev tanımlamak tablo değerli işlevler işlevselliğini genişletirVeri bir tablo değerli işlev alanından döndürülür bir IEnumerable veya IEnumerator nesne.

Not

Tablo değerli işlevler için sütunları döndürülen tablo türü zaman damgası sütunları veya Unicode olmayan dize veri türü sütunlarındaki içeremez (gibi char, varchar, ve text).not null kısıtlaması desteklenmiyor.

Transact-sql ve clr tablo değerli işlevler arasındaki farklar

Transact-SQLTablo değerli işlevler materialize sonuçlar , bir ara tabloya işlevinin çağrılması.Bir ara tablo kullandığından bunlar kısıtlamaları ve benzersiz dizinleri üzerinden destekleyebilir sonuçlar.Büyük sonuçlar döndürülmediğinde bu özellikleri son derece yararlı olabilir.

Buna karşılık, clr tablo değerli işlevler akış alternatif temsil eder.Gerekmez tek bir sonuçlar kümesinin tamamını materialized tablo.The IEnumerable object returned by the managed function is directly called by the execution plan of the query that calls the table-valued function, and the results are consumed in an incremental manner.Bu akış modelini hemen ilk satır yerine tüm tablo doldurulmuş olması bekleniyor, kullanılabilir sonra sonuçlar tüketilebilir sağlar.Bir bütün olarak bellekte materialized olmadığı için çok sayıda satır döndürdü, varsa da daha iyi bir alternatif olduğunu.Örneğin, bir metin dosyasını ayrıştırma ve her satırı bir satır olarak dönmek için yönetilen bir tablo değerli işlev kullanılabilir.

Tablo değerli işlevler uygulama

Implement tablo değerli işlev olarak yöntemler bir sınıf içinde bir Microsoft ..NET Framework derleme.Tablo değerli işlev kodunuzu uygulamalıdır IEnumerable arabirim.The IEnumerable interface is defined in the .NET Framework.Diziler ve topluluklar içinde temsil eden türleri.net Framework zaten uygulamak IEnumerable arabirim.Bu bir topluluk veya bir diziye dönüştürme tablo değerli işlevler yazmak için kolaylaştırır bir sonuç küme.

Tablo değerli parametreleri

Tablo değerli parametreleri bir yordam veya işlev içinde iletilir ve birden çok veri satırı sunucusuna iletmek için bir yol sağlar, kullanıcı tanımlı tablo türleri şunlardır.Tablo değerli parametreleri parametre dizileri için benzer bir işlevsellik sağlar, ancak daha fazla esneklik ve daha yakın tümleştirme sunar Transact-SQL.Ayrıca olası en iyi performansı sağlarlar.Tablo değerli parametreleri durumsa sunucuya azaltılmasına da yardımcı olur.Birden çok istek sunucuya göndermek yerine, gibi skaler parametrelerinin bir listesi ile verileri sunucuya tablo değerli bir parametre olarak gönderilebilir.Kullanıcı tanımlı tablo türü bir yönetilen saklı yordam, iade edilmesi veya işlev içinde yürütmek için tablo değerli bir parametre olarak geçirilemez SQL Server işlem.Tablo değerli parametreleri hakkında daha fazla bilgi için bkz: Tablo değerli Parametreler (veritabanı altyapısı).

Çıkış parametreleri ve tablo değerli işlevler

Tablo değerli işlevler çıktı parametrelerini kullanarak bilgi döndürülebilir.Uygulama kodu tablo değerli işlev karşılık gelen parametresinde pass'e başvuru parametresi bağımsız değişken olarak kullanmanız gerekir.Not Visual Basic, Visual C# yapan aynı şekilde çıkış parametreleri desteklemiyor.Kayar parametresi tarafından başvuru gerekir ve uygulama <Out()> öznitelik, aşağıdaki gibi bir çıktı parametresi temsil etmek için:

Imports System.Runtime.InteropServices
…
Public Shared Sub FillRow ( <Out()> ByRef value As SqlInt32)

Bir tablo değerli işlev Transact-SQL'de tanımlama

Bir clr tablo değerli işlev tanımlamak için sözdizimi olarak benzer bir Transact-SQL birlikte, tablo değerli işlev EXTERNAL NAME yan tümce tümce tümce.Örneğin:

CREATE FUNCTION GetEmpFirstLastNames()
RETURNS TABLE (FirstName NVARCHAR(4000), LastName NVARCHAR(4000))
EXTERNAL NAME MyDotNETAssembly.[MyNamespace.MyClassname]. GetEmpFirstLastNames;

Tablo değerli işlevler, verileri daha ayrıntılı sorgular gibi işlemek için ilişkisel formunda göstermek için kullanılır:

select * from function();
select * from tbl join function() f on tbl.col = f.col;
select * from table t cross apply function(t.column);

Tablo değerli işlevler Tablo dönebilirsiniz olduğunda:

skaler girdi bağımsız değişkenlerini oluşturdu.Örneğin, virgül ile ayrılmış götüren bir tablo değerli işlev dize , numaralar ve bunları bir tabloya etrafında döner.
Dış veri kaynağından üretti.Örneğin, bir tablo değerli işlev, olay günlüğünü okur ve tablo olarak sunar.

Nottablo değerli işlev yalnızca veri erişim üzerinden gerçekleştirebilir bir Transact-SQL de sorgulamak InitMethod yöntem ve değil de FillRow yöntem.The InitMethod should be marked with the SqlFunction.DataAccess.Read attribute property if a Transact-SQL query is performed.

Örnek tablo değerli fonksiyon

Aşağıdaki tablo değerli işlev, sistem olay günlüğü ' bilgi verir.Olay günlüğü okumak için adını içeren tek bir dize değişkenine işlev alır.

Örnek kod

using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;

public class TabularEventLog
{
    [SqlFunction(FillRowMethodName = "FillRow")]
    public static IEnumerable InitMethod(String logname)
    {
        return new EventLog(logname).Entries;    }

    public static void FillRow(Object obj, out SqlDateTime timeWritten, out SqlChars message, out SqlChars category, out long instanceId)
    {
        EventLogEntry eventLogEntry = (EventLogEntry)obj;
        timeWritten = new SqlDateTime(eventLogEntry.TimeWritten);
        message = new SqlChars(eventLogEntry.Message);
        category = new SqlChars(eventLogEntry.Category);
        instanceId = eventLogEntry.InstanceId;
    }
}

Imports System
Imports System.Data.Sql
Imports Microsoft.SqlServer.Server
Imports System.Collections
Imports System.Data.SqlTypes
Imports System.Diagnostics
Imports System.Runtime.InteropServices

Public Class TabularEventLog
    <SqlFunction(FillRowMethodName:="FillRow")> _
    Public Shared Function InitMethod(ByVal logname As String) As IEnumerable
        Return New EventLog(logname).Entries
    End Function

    Public Shared Sub FillRow(ByVal obj As Object, <Out()> ByRef timeWritten As SqlDateTime, <Out()> ByRef message As SqlChars, <Out()> ByRef category As SqlChars, <Out()> ByRef instanceId As Long)
        Dim eventLogEnTry As EventLogEntry = CType(obj, EventLogEntry)
        timeWritten = New SqlDateTime(eventLogEnTry.TimeWritten)
        message = New SqlChars(eventLogEnTry.Message)
        category = New SqlChars(eventLogEnTry.Category)
        instanceId = eventLogEnTry.InstanceId
    End Sub
End Class

Bildirmek ve örnek tablo değerli işlev kullanma

Örnek tablo değerli işlev derlenmiş sonra onu içinde bildirilebilir Transact-SQL aşağıdaki gibi:

use master
-- Replace SQL_Server_logon with your SQL Server user credentials.
GRANT EXTERNAL ACCESS ASSEMBLY TO [SQL_Server_logon] 
-- Modify the following line to specify a different database.
ALTER DATABASE master SET TRUSTWORTHY ON

-- Modify the next line to use the appropriate database.
CREATE ASSEMBLY tvfEventLog 
FROM 'D:\assemblies\tvfEventLog\tvfeventlog.dll' 
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
CREATE FUNCTION ReadEventLog(@logname nvarchar(100))
RETURNS TABLE 
(logTime datetime,Message nvarchar(4000),Category nvarchar(4000),InstanceId bigint)
AS 
EXTERNAL NAME tvfEventLog.TabularEventLog.InitMethod
GO

İle başlayan, Not SQL Server 2005, üzerinde yönetilen kullanıcı tanımlı türleri, saklı yordamları, işlevleri, toplamları veya Tetikleyiciler oluşturamazsınız bir SQL Server veritabanı uyumluluk düzey "80".Bu clr tümleştirme özelliklerinden yararlanmak için SQL Server, kullanmanız gereken sp_dbcmptlevel saklı yordam için küme "100" için veritabanını uyumluluk düzeyini.

Visual c++ veritabanı nesnelerinin /clr ile derlenmiş: saf için yürütme üzerinde desteklemediği SQL Server 2005.Örneğin, tablo değerli işlevler gibi veritabanı nesneleri içerir.

Örnek sınamak için aşağıdakileri deneyin: Transact-SQL kodu:

-- Select the top 100 events,
SELECT TOP 100 *
FROM dbo.ReadEventLog(N'Security') as T
go

-- Select the last 10 login events.
SELECT TOP 10 T.logTime, T.Message, T.InstanceId 
FROM dbo.ReadEventLog(N'Security') as T
WHERE T.Category = N'Logon/Logoff'
go

Örnek: sql Server sorgu sonuçları döndürülüyor

Aşağıdaki örnek bir tablo değerli işlev sorguladığı gösterir bir SQL Server veritabanı.Bu örnek AdventureWorks2008R2 ışık veritabanını kullanır.Bkz: https://www.codeplex.com/sqlserversamples AdventureWorks2008R2 karşıdan yükleme hakkında daha fazla bilgi için.

FindInvalidEmails.cs veya FindInvalidEmails.vb kaynak kodu dosyası adı.

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions {
   private class EmailResult {
      public SqlInt32 CustomerId;
      public SqlString EmailAdress;

      public EmailResult(SqlInt32 customerId, SqlString emailAdress) {
         CustomerId = customerId;
         EmailAdress = emailAdress;
      }
   }

   public static bool ValidateEmail(SqlString emailAddress) {
      if (emailAddress.IsNull)
         return false;

      if (!emailAddress.Value.EndsWith("@adventure-works.com"))
         return false;

      // Validate the address. Put any more rules here.
      return true;
   }

   [SqlFunction(
       DataAccess = DataAccessKind.Read,
       FillRowMethodName = "FindInvalidEmails_FillRow",
       TableDefinition="CustomerId int, EmailAddress nvarchar(4000)")]
   public static IEnumerable FindInvalidEmails(SqlDateTime modifiedSince) {
      ArrayList resultCollection = new ArrayList();

      using (SqlConnection connection = new SqlConnection("context connection=true")) {
         connection.Open();

         using (SqlCommand selectEmails = new SqlCommand(
             "SELECT " +
             "[CustomerID], [EmailAddress] " +
             "FROM [AdventureWorksLT2008].[SalesLT].[Customer] " +
             "WHERE [ModifiedDate] >= @modifiedSince",
             connection)) {
            SqlParameter modifiedSinceParam = selectEmails.Parameters.Add(
                "@modifiedSince",
                SqlDbType.DateTime);
            modifiedSinceParam.Value = modifiedSince;

            using (SqlDataReader emailsReader = selectEmails.ExecuteReader()) {
               while (emailsReader.Read()) {
                  SqlString emailAddress = emailsReader.GetSqlString(1);
                  if (ValidateEmail(emailAddress)) {
                     resultCollection.Add(new EmailResult(
                         emailsReader.GetSqlInt32(0),
                         emailAddress));
                  }
               }
            }
         }
      }

      return resultCollection;
   }

   public static void FindInvalidEmails_FillRow(
       object emailResultObj,
       out SqlInt32 customerId,
       out SqlString emailAdress) {
      EmailResult emailResult = (EmailResult)emailResultObj;

      customerId = emailResult.CustomerId;
      emailAdress = emailResult.EmailAdress;
   }
};

Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Public Partial Class UserDefinedFunctions
   Private Class EmailResult
      Public CustomerId As SqlInt32
      Public EmailAdress As SqlString

      Public Sub New(customerId__1 As SqlInt32, emailAdress__2 As SqlString)
         CustomerId = customerId__1
         EmailAdress = emailAdress__2
      End Sub
   End Class

   Public Shared Function ValidateEmail(emailAddress As SqlString) As Boolean
      If emailAddress.IsNull Then
         Return False
      End If

      If Not emailAddress.Value.EndsWith("@adventure-works.com") Then
         Return False
      End If

      ' Validate the address. Put any more rules here.
      Return True
   End Function

   <SqlFunction(DataAccess := DataAccessKind.Read, FillRowMethodName := "FindInvalidEmails_FillRow", TableDefinition := "CustomerId int, EmailAddress nvarchar(4000)")> _
   Public Shared Function FindInvalidEmails(modifiedSince As SqlDateTime) As IEnumerable
      Dim resultCollection As New ArrayList()

      Using connection As New SqlConnection("context connection=true")
         connection.Open()

         Using selectEmails As New SqlCommand("SELECT " & "[CustomerID], [EmailAddress] " & "FROM [AdventureWorksLT2008].[SalesLT].[Customer] " & "WHERE [ModifiedDate] >= @modifiedSince", connection)
            Dim modifiedSinceParam As SqlParameter = selectEmails.Parameters.Add("@modifiedSince", SqlDbType.DateTime)
            modifiedSinceParam.Value = modifiedSince

            Using emailsReader As SqlDataReader = selectEmails.ExecuteReader()
               While emailsReader.Read()
                  Dim emailAddress As SqlString = emailsReader.GetSqlString(1)
                  If ValidateEmail(emailAddress) Then
                     resultCollection.Add(New EmailResult(emailsReader.GetSqlInt32(0), emailAddress))
                  End If
               End While
            End Using
         End Using
      End Using

      Return resultCollection
   End Function

   Public Shared Sub FindInvalidEmails_FillRow(emailResultObj As Object, ByRef customerId As SqlInt32, ByRef emailAdress As SqlString)
      Dim emailResult As EmailResult = DirectCast(emailResultObj, EmailResult)

      customerId = emailResult.CustomerId
      emailAdress = emailResult.EmailAdress
   End Sub
End ClassImports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Public Partial Class UserDefinedFunctions
   Private Class EmailResult
      Public CustomerId As SqlInt32
      Public EmailAdress As SqlString

      Public Sub New(customerId__1 As SqlInt32, emailAdress__2 As SqlString)
         CustomerId = customerId__1
         EmailAdress = emailAdress__2
      End Sub
   End Class

   Public Shared Function ValidateEmail(emailAddress As SqlString) As Boolean
      If emailAddress.IsNull Then
         Return False
      End If

      If Not emailAddress.Value.EndsWith("@adventure-works.com") Then
         Return False
      End If

      ' Validate the address. Put any more rules here.
      Return True
   End Function

   <SqlFunction(DataAccess := DataAccessKind.Read, FillRowMethodName := "FindInvalidEmails_FillRow", TableDefinition := "CustomerId int, EmailAddress nvarchar(4000)")> _
   Public Shared Function FindInvalidEmails(modifiedSince As SqlDateTime) As IEnumerable
      Dim resultCollection As New ArrayList()

      Using connection As New SqlConnection("context connection=true")
         connection.Open()

         Using selectEmails As New SqlCommand("SELECT " & "[CustomerID], [EmailAddress] " & "FROM [AdventureWorksLT2008].[SalesLT].[Customer] " & "WHERE [ModifiedDate] >= @modifiedSince", connection)
            Dim modifiedSinceParam As SqlParameter = selectEmails.Parameters.Add("@modifiedSince", SqlDbType.DateTime)
            modifiedSinceParam.Value = modifiedSince

            Using emailsReader As SqlDataReader = selectEmails.ExecuteReader()
               While emailsReader.Read()
                  Dim emailAddress As SqlString = emailsReader.GetSqlString(1)
                  If ValidateEmail(emailAddress) Then
                     resultCollection.Add(New EmailResult(emailsReader.GetSqlInt32(0), emailAddress))
                  End If
               End While
            End Using
         End Using
      End Using

      Return resultCollection
   End Function

   Public Shared Sub FindInvalidEmails_FillRow(emailResultObj As Object, customerId As SqlInt32, emailAdress As SqlString)
      Dim emailResult As EmailResult = DirectCast(emailResultObj, EmailResult)

      customerId = emailResult.CustomerId
      emailAdress = emailResult.EmailAdress
   End Sub
End Class

Bir dll dosyasının kaynak kodu derleyin ve dll c sürücüsünün kök dizinine kopyalayın.Daha sonra aşağıdaki yürütmek Transact-SQL sorgu.

use AdventureWorksLT2008
go

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'FindInvalidEmails')
   DROP FUNCTION FindInvalidEmails
go

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'MyClrCode')
   DROP ASSEMBLY MyClrCode
go

CREATE ASSEMBLY MyClrCode FROM 'C:\FindInvalidEmails.dll'
WITH PERMISSION_SET = SAFE -- EXTERNAL_ACCESS
GO

CREATE FUNCTION FindInvalidEmails(@ModifiedSince datetime) 
RETURNS TABLE (
   CustomerId int,
   EmailAddress nvarchar(4000)
)
AS EXTERNAL NAME MyClrCode.UserDefinedFunctions.[FindInvalidEmails]
go

SELECT * FROM FindInvalidEmails('2000-01-01')
go

Ayrıca bkz.

Kavramlar

Veritabanı uyumluluk düzeyi seçeneği

Aracılığıyla paylaş