I know Jeff will shoot me for this... 🙂

I did some other tests and finally found a high performant solution with CLR. Due to the fact that CLR functions do not support table valued functions I had to handle with XML data type.

Since now the function is not really optimized but it beats all other solutions!

CLR function

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.IO;

using System.Xml;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static System.Data.SqlTypes.SqlXml ufn_clr_SplitLines(SqlChars textIn)

{

string text = new string(textIn.Value);

string[] lines = text.Split(new string[] { "\r" }, StringSplitOptions.None);

int capacity = text.Length * 2;

MemoryStream strm = new MemoryStream(capacity);

XmlWriter sw = XmlWriter.Create(strm);

sw.WriteStartDocument();

sw.WriteStartElement("Root");

for (int i = 0; i < lines.Length; i++)

{

string line = lines;

sw.WriteElementString("Item", line);

}

sw.WriteEndElement();

sw.WriteEndDocument();

sw.Flush();

strm.Position = 0;

SqlXml sqlXml = new SqlXml(strm);

// Put your code here

return sqlXml;

}

};

Additional test block in TSQL

DELETE FROM @result -- Clean up

-- //////////////////////////////////////////////////////////

-- -> Clr xml solution

PRINT 'Start clr xml solution'

SELECT @now = GETDATE()

-- Split text into lines

INSERT INTO @result

SELECT l.line

FROM @source s

CROSS APPLY (SELECT

ISNULL(T.C.value('(./text())[1]', 'nvarchar(1000)'), '') line

FROM (SELECT dbo.ufn_clr_SplitLines(s.definition) col1) x

CROSS APPLY x.col1.nodes('/Root/Item') T(C)

) l

-- Results

SELECT @duration = DATEDIFF(MILLISECOND, @now, GETDATE())

SELECT @count = COUNT(*) FROM @result

PRINT 'Milliseconds: ' + CONVERT(VARCHAR(10), @duration) + ' | Lines: ' + CONVERT(VARCHAR(10), @count)

-- <- Clr xml solution

-- //////////////////////////////////////////////////////////

Results

Start clr xml solution

Milliseconds: 783 | Lines: 28545

@jeff

The day to activate CLR on your servers is close 😀

Greets

Flo