PCB MS SQL table-valued functions vs. CLR table-valued Functions (example: string split-table)

It is often used to split a string into a table table, where SQL table-valued functions and CLR table-valued functions are implemented in two ways:

SELECT *  from FP_EMSDB_PUB.dbo.SqlSplit ('/','1oz/1.5oz/2oz/3oz')

The effect is as follows:

SQL implementation table-valued functions

CREATE FUNCTION [dbo].[Splitstr](@s   varchar(Max),--string to be split@split varchar(Ten)--Data Separators)RETURNS @re TABLE(IDint IDENTITY(1,1), colvarchar(Max))--Create a temporary table save the split character asBEGIN DECLARE @splitlen int SET @splitlen=LEN(@split+'a')-2  while CHARINDEX(@split,@s)>0 BEGIN  INSERT @re VALUES( Left(@s,CHARINDEX(@split,@s)-1))  SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,"') END INSERT @re VALUES(@s) RETURNEND

To create a CLR table-valued function

    --registry value function Sqlsplit    CREATE FUNCTION[dbo].[Sqlsplit](@separator NVARCHAR( +),@string NVARCHAR(MAX))    RETURNS TABLE(SerialNumberINT, StringValueNVARCHAR(1024x768)    )     with EXECUTE  asCALLER--for permission checking of referenced objects when the user executes a function     asEXTERNAL NAME[sqlfunctionassembly].[sqlclr.sqlfunction].[Sqlsplit]    --EXTERNAL Name Assembly name. Class name. Method Name    GO

CLR implementation table-valued functions

  Public Partial classSqlFunction {/// <summary>        ///SQL Server String Segmentation method/// </summary>        /// <param name= "separator" ></param>        /// <param name= "pendingstring" ></param>        /// <returns></returns>[Microsoft.SqlServer.Server.SqlFunction (DataAccess=Dataaccesskind.read, IsDeterministic=true, Name="Sqlsplit", FillRowMethodName="Sqlsplit_fillrow", TableDefinition="serialnumber int,stringvalue nvarchar (1024x768)")]         Public StaticIEnumerable sqlsplit (SqlString separator, SqlString pendingstring) {string_separator =string.            Empty; string_pendingstring =string.            Empty; if(Pendingstring.isnull)return NULL; _pendingstring=pendingstring.tostring (); if(string. IsNullOrEmpty (_pendingstring))return NULL; _separator= separator. IsNull?",": Separator.            ToString (); _separator=string. IsNullOrEmpty (_separator)?",": _separator; string[] STRs = _pendingstring.split (New string[] {_separator}, stringsplitoptions.removeemptyentries); List<ResultData> resultdatalist =NewList<resultdata>();  for(inti =0; I < STRs. Length; i++) {Resultdatalist.add (NewResultdata (i +1, Strs[i])); }            returnresultdatalist; }        /// <summary>        ///Populating data Methods/// </summary>        /// <param name= "obj" ></param>        /// <param name= "serialnumber" ></param>        /// <param name= "StringValue" ></param>         Public Static voidSqlsplit_fillrow (Object obj, outSqlInt32 SerialNumber, outSqlString StringValue) {Resultdata Resultdata=(resultdata) obj; SerialNumber=Resultdata.serialnumber; StringValue=Resultdata.stringvalue; }        /// <summary>        ///Defining return Types/// </summary>         Public classResultdata {/// <summary>            ///serial number, No./// </summary>             PublicSqlInt32 SerialNumber {Get;Set; } /// <summary>            ///each sub-string after splitting/// </summary>             PublicSqlString StringValue {Get;Set; }  PublicResultdata (SqlInt32 serialnumber, SqlString stringvalue) {serialnumber=serialnumber; StringValue=StringValue; }        }    }

PCB MS SQL table-valued functions vs. CLR table-valued Functions (example: string split-table)