Artykuł
05/11/2010

Tabela Valued funkcje środowiska CLR

A funkcja zwracająca tabela jest funkcja zdefiniowana przez użytkownika, która zwraca tabela.

Zaczyna się od SQL Server 2005, SQL Server rozszerza funkcjonalność funkcja zwracająca tabelę s umożliwia zdefiniowanie funkcja zwracająca tabelę w dowolnym zarządzanych języka. Dane są zwracane z funkcja zwracająca tabelę za pośrednictwem IEnumerable lub IEnumerator obiekt.

Uwaga

Wartości tabela funkcji kolumny Typ zwrotu tabela nie może zawierać kolumny sygnatury czasowej lub kolumny Typ danych ciąg nie obsługujących kodu Unicode (np. char, varchar, a text). Ograniczenie NOT NULL nie jest obsługiwana.

Różnice między języka Transact-SQL i CLR funkcje Valued tabela

Transact-SQL wartości tabela funkcja materialize wyniki wywołania funkcja do tabela programu pośrednich.Ponieważ korzystają już pośrednich tabela one może obsługiwać nad wyniki ograniczenia oraz unikatowe indeksy.Te funkcje mogą być bardzo użyteczne, gdy duża wyniki są zwracane.

Z drugiej strony funkcje wycenione tabela CLR reprezentują zamiast przesyłania strumieniowego.Nie jest wymagane czy cały zestaw wyniki można materialized w jednej tabela.The IEnumerable object returned by the managed funkcja is directly called by the execution plan of the query that calls the tabela-valued funkcja, and the wyniki are consumed in an incremental manner. Ten model strumieniowych daje pewność, że wyniki może zostać zużyta, natychmiast po pierwszym wierszu jest dostępny, zamiast oczekiwanie na całą tabela mieć.Jest ona również lepszym, jeśli masz bardzo dużą liczbę wierszy zwracanych, ponieważ nie mają być materialized w pamięci jako całości.Na przykład, zarządzane funkcja zwracająca tabelę można stosować do analizowania pliku tekstowego i zwracają każdego wiersza w wierszu.

Tabela Valued funkcje wykonywania

Wdrożenie wycenione tabela funkcjonuje jako metody dla klasy w Microsoft wirtualny plik dziennika .NET framework. Twoje funkcja zwracająca tabelę kod musi implementować IEnumerable interfejs. The IEnumerable interfejs is defined in the .NET Framework. Typy reprezentujących tablic i kolekcji w programie .NET Framework już wdrożenie IEnumerable interfejs. Dzięki takiemu modelowi stosunkowo łatwo do pisania funkcji wycenione tabela konwersji kolekcja lub w tablicy zestaw wyników.

Tabela Valued parametry

Wartości tabela parametry są typy zdefiniowane przez użytkownika tabela, które są przekazywane do procedury lub funkcja i zapewnia skuteczny sposób na przekazywanie wielu wierszy danych na serwerze.Wartości w tabela parametrów funkcjonalność podobnymi do tablic parametrów, ale oferują większą elastyczność i ściślejszej integracja z Transact-SQL. Zapewniają także możliwości, aby zapewnić lepszą wydajność.Wartości w tabela parametrów również zmniejszyć liczba round podróży do serwera.Zamiast wysyłać wiele żądań do serwera, takie jak z listą parametrów wartość skalarna, dane mogą być wysyłane do serwera jako parametru wartości w tabela.Typ zdefiniowany przez użytkownika tabela nie mogą być przekazywane jako Wycenione tabela parametr, zwracane z zarządzanych procedura przechowywana lub funkcja w SQL Server proces. Aby uzyskać więcej informacji na temat parametrów wycenione tabela zobacz Tabela Valued parametry (aparat bazy danych).

Tabela Valued funkcji i parametry wyjściowe

Informacje mogą być zwracane z funkcji wycenione tabela przy użyciu parametrów wyjściowych.Odpowiadającego mu parametru w kodzie wykonania funkcja zwracająca tabelę parametr przekazywany przez odwołanie powinien być używany jako argumentu.Należy zauważyć, że program Visual Basic nie obsługuje parametrów wyjściowych w taki sam sposób, w jaki program Visual C# jest.Należy określić parametr odniesienie i zastosować <Out()> atrybut do reprezentowania parametru wyjściowego, zgodnie z następujących czynności:

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

Definiowanie funkcja zwracająca tabelę w języku Transact-SQL

Składnia służąca do definiowania CLR funkcja zwracająca tabelę jest podobny do Transact-SQL funkcja zwracająca tabelę, z uwzględnieniem EXTERNAL NAME Klauzula. Na przykład:

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

Wartości tabela funkcje są używane do reprezentowania danych relacyjnych formularza do dalszego przetwarzania w kwerendach takie jak:

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

Wartości tabela funkcje mogą być zwracane tabela, gdy:

Utworzone na podstawie wartość skalarna argumenty wejściowe.Na przykład funkcja zwracająca tabelę, pobiera ciąg rozdzielany przecinkami liczb i obracając je do tabela.
Generowane na podstawie danych zewnętrznych.Na przykład funkcja zwracająca tabelę, odczytuje dziennika zdarzeń i udostępnia go jako tabelę.

Uwaga A funkcja zwracająca tabelę może wykonać tylko dostęp do danych za pośrednictwem Transact-SQL kwerendy w InitMethod Metoda, a nie w FillRow Metoda. The InitMethod should be marked with the SqlFunction.DataAccess.Read atrybut właściwość if a Transact-SQL query is performed.

Próbka funkcja zwracająca tabelę

Następujące funkcja zwracająca tabelę zwraca informacje z dziennika zdarzeń systemowych.Funkcja przyjmuje jako argumentu jeden ciąg znaków zawierający nazwę dziennika zdarzeń do odczytania.

Przykładowy 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

Deklarowanie i korzystanie z próbki funkcja zwracająca tabelę

Po tej próbki funkcja zwracająca tabelę zostało opracowane, go może być zadeklarowana w Transact-SQL postać:

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

Uwaga

Zaczyna się od SQL Server 2005, na SQL ServerBaza danych o poziom zgodności „ 80"nie można tworzyć zarządzane typów zdefiniowanych przez użytkownika, procedura przechowywana, funkcje, zagregowanych lub wyzwalaczy. Aby korzystać z tych CLR funkcje integracja SQL Server, należy użyć sp_dbcmptlevel przechowywane procedury, aby ustawić poziom zgodności bazy danych "100".

Uwaga

Zarządzane obiekty bazy danych Visual C++, który został wcześniej skompilowany z /clr: na nie są obsługiwane przez wykonanie czystego opcja kompilatora Visual C++ SQL Server 2005. Na przykład takich obiektów bazy danych dołączyć wycenione tabela funkcji.

Aby przetestować próbki, spróbuj wykonać następujące czynności Transact-SQL kod:

-- 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

Przykład: Zwracanie wyniki SQL Server Query

Następujący przykład przedstawia funkcja wartości w tabela tej kwerendy SQL Server Baza danych. W przykładzie użyto bazy danych AdventureWorks światło z SQL Server 2008. Zobacz https://www.codeplex.com/sqlserversamples , aby uzyskać więcej informacji na temat pobierania AdventureWorks.

Nazwa użytkownika urządzenie źródłowe pliku kodu FindInvalidEmails.cs lub FindInvalidEmails.vb.

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")]
   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 Microsoft.SqlServer.Server
Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Runtime.InteropServices

Public Class UserDefinedFunctions
   <SqlFunction(DataAccess:=DataAccessKind.Read, FillRowMethodName:="FindInvalidEmails_FillRow")> _
   Public Shared Function FindInvalidEmails(ByVal modifiedSince As SqlDateTime) As IEnumerable
      Dim resultCollection As New ArrayList
      Using connection As SqlConnection = New SqlConnection("context connection=true")
         connection.Open()
         Using selectEmails As SqlCommand = New SqlCommand("SELECT [CustomerID], [EmailAddress] FROM [AdventureWorksLT2008].[SalesLT].[Customer] WHERE [ModifiedDate] >= @modifiedSince", connection)
            selectEmails.Parameters.Add("@modifiedSince", SqlDbType.DateTime).Value = modifiedSince
            Using emailsReader As SqlDataReader = selectEmails.ExecuteReader
               Do While emailsReader.Read
                  Dim emailAddress As SqlString = emailsReader.GetSqlString(1)
                  If UserDefinedFunctions.ValidateEmail(emailAddress) Then
                     resultCollection.Add(New EmailResult(emailsReader.GetSqlInt32(0), emailAddress))
                  End If
               Loop
            End Using
            Return resultCollection
         End Using
      End Using
   End Function

   Public Shared Sub FindInvalidEmails_FillRow(ByVal emailResultObj As Object, <Out()> ByRef customerId As SqlInt32, <Out()> ByRef emailAdress As SqlString)
      Dim emailResult As EmailResult = DirectCast(emailResultObj, EmailResult)
      customerId = emailResult.CustomerId
      emailAdress = emailResult.EmailAdress
   End Sub

   Public Shared Function ValidateEmail(ByVal 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
      Return True
   End Function

   Private Class EmailResult
      Public Sub New(ByVal customerId As SqlInt32, ByVal emailAdress As SqlString)
         Me.CustomerId = customerId
         Me.EmailAdress = emailAdress
      End Sub

      Public CustomerId As SqlInt32
      Public EmailAdress As SqlString
   End Class
End Class

Kompilowanie kodu źródłowego do biblioteki DLL i skopiuj biblioteki DLL w katalogu głównym na dysku C.Następnie należy wykonać następujące czynności Transact-SQL kwerendy.

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

Historia zmian

Microsoft Learning
Dodaje próbki, która zawiera kwerendy programu SQL Server.

Udostępnij za pośrednictwem

Tabela Valued funkcje środowiska CLR

Różnice między języka Transact-SQL i CLR funkcje Valued tabela

Tabela Valued funkcje wykonywania

Tabela Valued parametry

Tabela Valued funkcji i parametry wyjściowe

Definiowanie funkcja zwracająca tabelę w języku Transact-SQL

Próbka funkcja zwracająca tabelę

Przykładowy kod

Deklarowanie i korzystanie z próbki funkcja zwracająca tabelę

Przykład: Zwracanie wyniki SQL Server Query

Historia zmian

See Also

Other Resources

Dodatkowe zasoby