Skip to content

Latest commit

 

History

History
465 lines (361 loc) · 17.8 KB

clr-table-valued-functions.md

File metadata and controls

465 lines (361 loc) · 17.8 KB
titledescriptionauthorms.authorms.datems.servicems.subservicems.topichelpviewer_keywordsdev_langs
CLR Table-Valued Functions
A table-valued function returns a table. In SQL Server CLR integration, you can write table-valued functions in managed code.
rwestMSFT
randolphwest
12/27/2024
sql
clr
reference
Transact-SQL table-valued functions
table-valued functions [CLR integration]
TVFs [CLR integration]
TSQL
VB
CSharp

CLR table-valued functions

[!INCLUDE SQL Server]

A table-valued function is a user-defined function that returns a table.

[!INCLUDE ssNoVersion] extends the functionality of table-valued functions by allowing you to define a table-valued function in any managed language. Data is returned from a table-valued function through an IEnumerable or IEnumerator object.

For table-valued functions, the columns of the return table type can't include timestamp columns or non-Unicode string data type columns (such as char, varchar, and text). The NOT NULL constraint isn't supported.

Differences between Transact-SQL and CLR table-valued functions

[!INCLUDE tsql] table-valued functions materialize the results of calling the function into an intermediate table. Since they use an intermediate table, they can support constraints and unique indexes over the results. These features can be useful when large results are returned.

In contrast, common language runtime (CLR) table-valued functions represent a streaming alternative. There's no requirement that the entire set of results be materialized in a single table. 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. This streaming model ensures that results can be consumed immediately after the first row is available, instead of waiting for the entire table to be populated. It's also a better alternative if you have large numbers of rows returned, because they don't have to be materialized in memory as a whole. For example, a managed table-valued function could be used to parse a text file and return each line as a row.

Implement table-valued functions

Implement table-valued functions as methods on a class in a [!INCLUDE dnprdnshort-md] assembly. Your table-valued function code must implement the IEnumerable interface. The IEnumerable interface is defined in the [!INCLUDE dnprdnshort-md]. Types representing arrays and collections in the [!INCLUDE dnprdnshort-md] already implement the IEnumerable interface. This makes it easy for writing table-valued functions that convert a collection or an array into a result set.

Table-valued parameters

Table-valued parameters are user-defined table types that are passed into a procedure or function and provide an efficient way to pass multiple rows of data to the server. Table-valued parameters provide similar functionality to parameter arrays, but offer greater flexibility and closer integration with [!INCLUDE tsql]. They also provide the potential for better performance.

Table-valued parameters also help reduce the number of round trips to the server. Instead of sending multiple requests to the server, such as with a list of scalar parameters, data can be sent to the server as a table-valued parameter. A user-defined table type can't be passed as a table-valued parameter to, or be returned from, a managed stored procedure or function executing in the [!INCLUDE ssNoVersion] process. For more information about table-valued parameters, see Use table-valued parameters (Database Engine).

Output parameters and table-valued functions

Information might be returned from table-valued functions using output parameters. The corresponding parameter in the implementation code table-valued function should use a pass-by-reference parameter as the argument. [!INCLUDE visual-basic-md] .NET doesn't support output parameters in the same way that [!INCLUDE c-sharp-md] does. You must specify the parameter by reference and apply the <Out()> attribute to represent an output parameter, as in the following example:

ImportsSystem.Runtime.InteropServices...PublicSharedSubFillRow(<Out()>ByRefvalueAsSqlInt32)

Define a table-valued function in Transact-SQL

The syntax for defining a CLR table-valued function is similar to that of a [!INCLUDE tsql] table-valued function, with the addition of the EXTERNAL NAME clause. For example:

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

Table-valued functions are used to represent data in relational form for further processing in queries such as:

SELECT*FROM func(); SELECT*FROM tbl INNER JOIN func() AS f ONtbl.col=f.col; SELECT*FROM tbl AS t CROSS APPLY func(t.col);

Table-valued functions can return a table when:

  • Created from scalar input arguments. For example, a table-valued function that takes a comma-delimited string of numbers and pivots them into a table.

  • Generated from external data. For example, a table-valued function that reads the event log and exposes it as a table.

Note

A table-valued function can only perform data access through a [!INCLUDE tsql] query in the InitMethod method, and not in the FillRow method. The InitMethod should be marked with the SqlFunction.DataAccess.Read attribute property if a [!INCLUDE tsql] query is performed.

A sample table-valued function

The following table-valued function returns information from the system event log. The function takes a single string argument containing the name of the event log to read.

Sample code

C#

usingSystem;usingSystem.Data.Sql;usingMicrosoft.SqlServer.Server;usingSystem.Collections;usingSystem.Data.SqlTypes;usingSystem.Diagnostics;publicclassTabularEventLog{[SqlFunction(FillRowMethodName="FillRow")]publicstaticIEnumerableInitMethod(Stringlogname){returnnewEventLog(logname).Entries;}publicstaticvoidFillRow(Objectobj,outSqlDateTimetimeWritten,outSqlCharsmessage,outSqlCharscategory,outlonginstanceId){EventLogEntryeventLogEntry=(EventLogEntry)obj;timeWritten=newSqlDateTime(eventLogEntry.TimeWritten);message=newSqlChars(eventLogEntry.Message);category=newSqlChars(eventLogEntry.Category);instanceId=eventLogEntry.InstanceId;}}

Visual Basic .NET

ImportsSystemImportsSystem.Data.SqlImportsMicrosoft.SqlServer.ServerImportsSystem.CollectionsImportsSystem.Data.SqlTypesImportsSystem.DiagnosticsImportsSystem.Runtime.InteropServicesPublicClassTabularEventLog<SqlFunction(FillRowMethodName:="FillRow")>_PublicSharedFunctionInitMethod(ByVallognameAsString)AsIEnumerableReturnNewEventLog(logname).EntriesEndFunctionPublicSharedSubFillRow(ByValobjAsObject,<Out()>ByReftimeWrittenAsSqlDateTime,<Out()>ByRefmessageAsSqlChars,<Out()>ByRefcategoryAsSqlChars,<Out()>ByRefinstanceIdAsLong)DimeventLogEnTryAsEventLogEntry=CType(obj,EventLogEntry)timeWritten=NewSqlDateTime(eventLogEnTry.TimeWritten)message=NewSqlChars(eventLogEnTry.Message)category=NewSqlChars(eventLogEnTry.Category)instanceId=eventLogEnTry.InstanceIdEndSubEndClass

Declare and using the sample table-valued function

After the sample table-valued function is compiled, it can be declared in [!INCLUDE tsql] like this:

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.ALTERDATABASE 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 CREATEFUNCTIONReadEventLog (@logname NVARCHAR (100)) RETURNS TABLE ( logTime DATETIME, Message NVARCHAR (4000), Category NVARCHAR (4000), InstanceId BIGINT) AS EXTERNAL NAME tvfEventLog.TabularEventLog.InitMethod; GO

Visual C++ database objects compiled with /clr:pure aren't supported for execution on [!INCLUDE ssnoversion-md]. For example, such database objects include table-valued functions.

To test the sample, try the following [!INCLUDE tsql] code:

-- Select the top 100 events,SELECT TOP 100*FROMdbo.ReadEventLog(N'Security') AS T; GO -- Select the last 10 login events.SELECT TOP 10T.logTime, T.Message, T.InstanceIdFROMdbo.ReadEventLog(N'Security') AS T WHERET.Category= N'Logon/Logoff'; GO

Examples

[!INCLUDE article-uses-adventureworks]

A. Return the results of a SQL Server query

The following sample shows a table-valued function that queries a [!INCLUDE ssNoVersion] database.

Name your source code file FindInvalidEmails.cs or FindInvalidEmails.vb.

C#

usingSystem;usingSystem.Collections;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Data.SqlTypes;usingMicrosoft.SqlServer.Server;publicpartialclassUserDefinedFunctions{privateclassEmailResult{publicSqlInt32CustomerId;publicSqlStringEmailAddress;publicEmailResult(SqlInt32customerId,SqlStringemailAddress){CustomerId=customerId;EmailAddress=emailAddress;}}publicstaticboolValidateEmail(SqlStringemailAddress){if(emailAddress.IsNull)returnfalse;if(!emailAddress.Value.EndsWith("@adventure-works.com"))returnfalse;// Validate the address. Put any more rules here.returntrue;}[SqlFunction(DataAccess=DataAccessKind.Read,FillRowMethodName="FindInvalidEmails_FillRow",TableDefinition="CustomerId int, EmailAddress nvarchar(4000)")]publicstaticIEnumerableFindInvalidEmails(SqlDateTimemodifiedSince){ArrayListresultCollection=newArrayList();using(SqlConnectionconnection=newSqlConnection("context connection=true")){connection.Open();using(SqlCommandselectEmails=newSqlCommand("SELECT "+"[CustomerID], [EmailAddress] "+"FROM [AdventureWorksLT2022].[SalesLT].[Customer] "+"WHERE [ModifiedDate] >= @modifiedSince",connection)){SqlParametermodifiedSinceParam=selectEmails.Parameters.Add("@modifiedSince",SqlDbType.DateTime);modifiedSinceParam.Value=modifiedSince;using(SqlDataReaderemailsReader=selectEmails.ExecuteReader()){while(emailsReader.Read()){SqlStringemailAddress=emailsReader.GetSqlString(1);if(ValidateEmail(emailAddress)){resultCollection.Add(newEmailResult(emailsReader.GetSqlInt32(0),emailAddress));}}}}}returnresultCollection;}publicstaticvoidFindInvalidEmails_FillRow(objectemailResultObj,outSqlInt32customerId,outSqlStringemailAddress){EmailResultemailResult=(EmailResult)emailResultObj;customerId=emailResult.CustomerId;emailAddress=emailResult.EmailAddress;}};

Visual Basic .NET

ImportsSystem.CollectionsImportsSystem.DataImportsSystem.Data.SqlClientImportsSystem.Data.SqlTypesImportsMicrosoft.SqlServer.ServerPublicPartialClassUserDefinedFunctionsPrivateClassEmailResultPublicCustomerIdAsSqlInt32PublicEmailAddressAsSqlStringPublicSubNew(customerId__1AsSqlInt32,emailAddress__2AsSqlString)CustomerId=customerId__1EmailAddress=emailAddress__2EndSubEndClassPublicSharedFunctionValidateEmail(emailAddressAsSqlString)AsBooleanIfemailAddress.IsNullThenReturnFalseEndIfIfNotemailAddress.Value.EndsWith("@adventure-works.com")ThenReturnFalseEndIf' Validate the address. Put any more rules here.ReturnTrueEndFunction<SqlFunction(DataAccess:=DataAccessKind.Read,FillRowMethodName:="FindInvalidEmails_FillRow",TableDefinition:="CustomerId int, EmailAddress nvarchar(4000)")>_PublicSharedFunctionFindInvalidEmails(modifiedSinceAsSqlDateTime)AsIEnumerableDimresultCollectionAsNewArrayList()UsingconnectionAsNewSqlConnection("context connection=true")connection.Open()UsingselectEmailsAsNewSqlCommand("SELECT "&"[CustomerID], [EmailAddress] "&"FROM [AdventureWorksLT2022].[SalesLT].[Customer] "&"WHERE [ModifiedDate] >= @modifiedSince",connection)DimmodifiedSinceParamAsSqlParameter=selectEmails.Parameters.Add("@modifiedSince",SqlDbType.DateTime)modifiedSinceParam.Value=modifiedSinceUsingemailsReaderAsSqlDataReader=selectEmails.ExecuteReader()WhileemailsReader.Read()DimemailAddressAsSqlString=emailsReader.GetSqlString(1)IfValidateEmail(emailAddress)ThenresultCollection.Add(NewEmailResult(emailsReader.GetSqlInt32(0),emailAddress))EndIfEndWhileEndUsingEndUsingEndUsingReturnresultCollectionEndFunctionPublicSharedSubFindInvalidEmails_FillRow(emailResultObjAsObject,ByRefcustomerIdAsSqlInt32,ByRefemailAddressAsSqlString)DimemailResultAsEmailResult=DirectCast(emailResultObj,EmailResult)customerId=emailResult.CustomerIdemailAddress=emailResult.EmailAddressEndSubEndClassImportsSystem.CollectionsImportsSystem.DataImportsSystem.Data.SqlClientImportsSystem.Data.SqlTypesImportsMicrosoft.SqlServer.ServerPublicPartialClassUserDefinedFunctionsPrivateClassEmailResultPublicCustomerIdAsSqlInt32PublicEmailAddressAsSqlStringPublicSubNew(customerId__1AsSqlInt32,emailAddress__2AsSqlString)CustomerId=customerId__1EmailAddress=emailAddress__2EndSubEndClassPublicSharedFunctionValidateEmail(emailAddressAsSqlString)AsBooleanIfemailAddress.IsNullThenReturnFalseEndIfIfNotemailAddress.Value.EndsWith("@adventure-works.com")ThenReturnFalseEndIf' Validate the address. Put any more rules here.ReturnTrueEndFunction<SqlFunction(DataAccess:=DataAccessKind.Read,FillRowMethodName:="FindInvalidEmails_FillRow",TableDefinition:="CustomerId int, EmailAddress nvarchar(4000)")>_PublicSharedFunctionFindInvalidEmails(modifiedSinceAsSqlDateTime)AsIEnumerableDimresultCollectionAsNewArrayList()UsingconnectionAsNewSqlConnection("context connection=true")connection.Open()UsingselectEmailsAsNewSqlCommand("SELECT "&"[CustomerID], [EmailAddress] "&"FROM [AdventureWorksLT2022].[SalesLT].[Customer] "&"WHERE [ModifiedDate] >= @modifiedSince",connection)DimmodifiedSinceParamAsSqlParameter=selectEmails.Parameters.Add("@modifiedSince",SqlDbType.DateTime)modifiedSinceParam.Value=modifiedSinceUsingemailsReaderAsSqlDataReader=selectEmails.ExecuteReader()WhileemailsReader.Read()DimemailAddressAsSqlString=emailsReader.GetSqlString(1)IfValidateEmail(emailAddress)ThenresultCollection.Add(NewEmailResult(emailsReader.GetSqlInt32(0),emailAddress))EndIfEndWhileEndUsingEndUsingEndUsingReturnresultCollectionEndFunctionPublicSharedSubFindInvalidEmails_FillRow(emailResultObjAsObject,customerIdAsSqlInt32,emailAddressAsSqlString)DimemailResultAsEmailResult=DirectCast(emailResultObj,EmailResult)customerId=emailResult.CustomerIdemailAddress=emailResult.EmailAddressEndSubEndClass

Compile the source code to a DLL and copy the DLL to the root directory of your C drive. Then, execute the following [!INCLUDE tsql] query.

USE AdventureWorksLT2022; GO IF EXISTS (SELECT name FROM sysobjects WHERE name ='FindInvalidEmails') DROPFUNCTION FindInvalidEmails; GO IF EXISTS (SELECT name FROMsys.assembliesWHERE name ='MyClrCode') DROP ASSEMBLY MyClrCode; GO CREATE ASSEMBLY MyClrCode FROM'C:\FindInvalidEmails.dll' WITH PERMISSION_SET = SAFE; GO CREATEFUNCTIONFindInvalidEmails (@ModifiedSince DATETIME) RETURNS TABLE ( CustomerId INT, EmailAddress NVARCHAR (4000)) AS EXTERNAL NAME MyClrCode.UserDefinedFunctions.[FindInvalidEmails]; GO SELECT*FROM FindInvalidEmails('2000-01-01'); GO
close