Funções com valor de tabela do CLR
Aplica-se a: 
SQL Server
Uma função com valor de tabela é uma função definida pelo usuário que retorna uma tabela.
A partir do SQL Server 2005 (9.x), o SQL Server estende a funcionalidade de funções com valor de tabela, permitindo que você defina uma função com valor de tabela em qualquer linguagem gerenciada. Os dados são retornados de uma função com valor de tabela por meio de um objeto IEnumerable ou IEnumerator .
Observação
Para funções com valor de tabela, as colunas do tipo de tabela de retorno não podem incluir colunas de carimbo de data/hora ou colunas de tipo de dados de cadeia de caracteres não Unicode (como char, varchar e text). Não tem suporte para a restrição NOT NULL.
Para obter mais informações sobre funções com valor de tabela CLR, confira Introdução ao MSSQLTips
Diferenças entre as funções com valor de tabela Transact-SQL e CLR
As funções com valor de tabela Transact-SQL materializam os resultados da chamada da função em uma tabela intermediária. Como elas usam uma tabela intermediária, podem dar suporte a restrições e índices exclusivos nos resultados. Esses recursos podem ser extremamente úteis quando resultados grandes são retornados.
Em contrapartida, as funções com valor de tabela do CLR representam uma alternativa de streaming. Não há nenhum requisito de que todo o conjunto de resultados deva ser materializado em uma única tabela. O objeto IEnumerable retornado pela função gerenciada é chamado diretamente pelo plano de execução da consulta que chama a função com valor de tabela e os resultados são consumidos de maneira incremental. Este modelo de streaming garante que os resultados possam ser consumidos imediatamente depois que a primeira linha estiver disponível, em vez de aguardar até que toda a tabela seja populada. Ele também será a melhor alternativa, se você tiver uma grande quantidade de linhas retornadas, pois elas não precisam ser totalmente materializadas na memória. Por exemplo, uma função com valor de tabela gerenciada pode ser usada para analisar um arquivo de texto e retornar todas as linhas como uma linha.
Implementando funções com valor de tabela
Implemente funções com valor de tabela como métodos em uma classe em um assembly do Microsoft .NET Framework. Seu código de função com valor de tabela deve implementar a interface IEnumerable . A interface IEnumerable é definida no .NET Framework. Os tipos que representam matrizes e coleções no .NET Framework já implementam a interface IEnumerable . Isso facilita a gravação de funções com valor de tabela que convertem uma coleção ou uma matriz em um conjunto de resultados.
Parâmetros com valor de tabela
Os parâmetros com valor de tabela são tipos de tabela definidos pelo usuário, transmitidos em um procedimento ou função e fornecem uma maneira eficiente de passar várias linhas de dados para o servidor. Os parâmetros com valor de tabela fornecem funcionalidade semelhante às matrizes de parâmetros, mas oferecem maior flexibilidade e integração mais próxima com o Transact-SQL. Eles também fornecem o potencial para melhor desempenho. Os parâmetros com valor de tabela também ajudam a reduzir o número de viagens de ida e volta para o servidor. Em vez de enviar várias solicitações ao servidor, como com uma lista de parâmetros escalares, os dados podem ser enviados ao servidor como um parâmetro com valor de tabela. Um tipo de tabela definido pelo usuário não pode ser passado como um parâmetro com valor de tabela ou ser retornado de um procedimento armazenado gerenciado ou de uma função em execução no processo do SQL Server. Para obter mais informações sobre parâmetros com valor de tabela, confira Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados).
Parâmetros de saída e funções com valor de tabela
As informações podem ser retornadas de funções com valor de tabela que usam parâmetros de saída. O parâmetro correspondente na função com valor de tabela do código de implementação deve usar um parâmetro de passagem por referência como o argumento. Observe que o Visual Basic não suporta parâmetros de saída da mesma maneira que o Visual C#. Você deve especificar o parâmetro por referência e aplicar o <atributo Out()> para representar um parâmetro de saída, como no seguinte:
Imports System.Runtime.InteropServices
...
Public Shared Sub FillRow ( <Out()> ByRef value As SqlInt32)
Definindo uma função com valor de tabela no Transact-SQL
A sintaxe para definir uma função com valor de tabela CLR é semelhante à de uma função com valor de tabela Transact-SQL, com a adição da cláusula EXTERNAL NAME . Por exemplo:
CREATE FUNCTION GetEmpFirstLastNames()
RETURNS TABLE (FirstName NVARCHAR(4000), LastName NVARCHAR(4000))
EXTERNAL NAME MyDotNETAssembly.[MyNamespace.MyClassname]. GetEmpFirstLastNames;
As funções com valor de tabela são usadas para representar dados em formato relacional para processamento posterior em consultas como:
select * from function();
select * from tbl join function() f on tbl.col = f.col;
select * from table t cross apply function(t.column);
As funções com valor de tabela podem retornar uma tabela quando:
Criadas a partir de argumentos de entrada escalar. Por exemplo, uma função com valor de tabela que usa uma cadeia de caracteres de números delimitada por vírgulas e converte-os em uma tabela.
Geradas de dados externos. Por exemplo, uma função com valor de tabela que lê o log de eventos e o expõe como uma tabela.
Observação Uma função com valor de tabela só pode executar o acesso a dados por meio de uma consulta Transact-SQL no método InitMethod e não no método FillRow . O InitMethod deve ser marcado com a propriedade de atributo SqlFunction.DataAccess.Read se uma consulta Transact-SQL for executada.
Um exemplo de função com valor de tabela
A função com valor de tabela a seguir retorna informações do log de eventos do sistema. A função adota um único argumento de cadeia de caracteres que contém o nome do log de eventos a ser lido.
Exemplo de código
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
Declarando e usando a função com valor de tabela de exemplo
Depois que a função com valor de tabela de exemplo tiver sido compilada, ela poderá ser declarada no Transact-SQL da seguinte forma:
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
Não há suporte para objetos de banco de dados do Visual C++ compilados com /clr:pure para execução no SQL Server 2005 (9.x). Por exemplo, esses objetos de banco de dados incluem funções com valor de tabela.
Para testar o exemplo, tente o seguinte código Transact-SQL:
-- 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
Exemplo: Retornando os resultados de uma consulta do SQL Server
O exemplo a seguir mostra uma função com valor de tabela que consulta um banco de dados do SQL Server. Este exemplo usa o banco de dados AdventureWorks Lightweight do SQL Server 2008 (10.0.x), consulte os bancos de dados de exemplo AdventureWorks
Nomeie seu arquivo de código-fonte como FindInvalidEmails.cs ou 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",
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
Compile o código-fonte em uma DLL e copie a DLL no diretório raiz de sua unidade C. Em seguida, execute a seguinte consulta Transact-SQL.
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