Efficient String Splitting in SQL Server Using CROSS APPLY and Table-Valued Functions

Problem Background and Performance Challenges

In database development, there is frequent need to split composite data from single fields into multiple row records. The original problem describes a typical scenario: a table containing ID and DATA fields where DATA stores concatenated strings of fixed-length (2-character) substrings that need to be separated into individual rows.

Traditional cursor methods, while functionally feasible, exhibit significant performance drawbacks when processing large datasets. Cursors require row-by-row processing, with each iteration involving context switching and resource locking, causing execution time to increase linearly with data volume. Particularly in SQL Server 2008 and later versions, this row-by-row processing approach severely limits system throughput.

Core Principles of Optimized Solutions

Set-based operations are fundamental to SQL optimization. By employing table-valued functions combined with the CROSS APPLY operator, string splitting operations can be transformed into set-based batch processing, fully leveraging SQL Server's query optimizer capabilities.

Table-valued functions can parse input strings into multi-row result sets, while the CROSS APPLY operator correlates each row from the main table with the function's returned result set, achieving data expansion effects similar to inner joins but with greater flexibility. This approach avoids cursor-based row-by-row processing in favor of batch operations, significantly reducing I/O overhead and context switching.

Complete Implementation Code and Analysis

First, create a table-valued function for string splitting:

CREATE FUNCTION dbo.SplitStringByLength (@InputString NVARCHAR(MAX), @SegmentLength INT)
RETURNS @Result TABLE (Segment NVARCHAR(50))
AS
BEGIN
    DECLARE @Index INT = 1
    DECLARE @TotalLength INT = LEN(@InputString)
    
    WHILE @Index <= @TotalLength
    BEGIN
        INSERT INTO @Result (Segment)
        VALUES (SUBSTRING(@InputString, @Index, @SegmentLength))
        
        SET @Index = @Index + @SegmentLength
    END
    
    RETURN
END

Next, use CROSS APPLY for querying:

SELECT t1.id, s.Segment AS data
FROM table1 t1
CROSS APPLY dbo.SplitStringByLength(t1.data, 2) s
WHERE s.Segment <> ''

Code analysis: The function iterates through the input string using a WHILE loop, extracting substrings of specified length using the SUBSTRING function. CROSS APPLY passes each row of data from the main table to the function and associates all segments returned by the function with the original row, generating the final result set. The WHERE condition ensures filtering of empty string segments.

Performance Advantages and Application Scenarios

Compared to cursor methods, this solution offers multiple advantages: the query optimizer can generate more efficient execution plans, reducing physical read operations; batch processing decreases lock contention and transaction log growth; memory usage is more efficient, avoiding cursor memory residency issues.

This method is particularly suitable for: fixed-length string splitting, log data parsing, encoded field decomposition, and similar scenarios. For variable-length delimiter cases, the function logic can be modified accordingly using functions like CHARINDEX for dynamic splitting.

Extended Applications and Considerations

In practical applications, function logic can be adjusted based on specific requirements. Examples include supporting different segment lengths, handling delimiters, adding data validation, etc. It's important to note that table-valued functions may incur some performance overhead when frequently called; in extremely high-performance scenarios, consider using inline table-valued functions or CLR integration solutions.

Additionally, while SQL Server 2016 and later versions provide the STRING_SPLIT function, custom functions still offer better flexibility and control for fixed-length splitting requirements.