How to combine binary chunks in SQL across rows?

Question

We have a requirement to concatenate chunks of binary data in a SQL Server 2014 database. Our requirement for this is due to slow replication of varbinary(max) data over a WAN link, as described here - Slow merge replication over a WAN link - only downloads so we're having to reduce our varbinary(max) columns to smaller widths.

Given a table and some dummy data:

DECLARE @testTable TABLE
(
    [ItemIdx] int,
    [ChunkIdx] int,
    [BinaryData] varbinary(8000)
)
INSERT INTO @testTable ([ItemIdx], [ChunkIdx], [BinaryData]) VALUES (1, 1, 0x00112233), (1, 3, 0x44556677), (1, 2, 0x8899AABB)
INSERT INTO @testTable ([ItemIdx], [ChunkIdx], [BinaryData]) VALUES (2, 1, 0xFFEEDDCC), (2, 3, 0xBBAA9988), (2, 2, 0x77665544)

I can write T-SQL to concatenate data for the rows corresponding to a single ItemIdx:

DECLARE @concatData varbinary(max)
SELECT @concatData = COALESCE(@concatData, 0x) + BinaryData FROM @testTable
WHERE ItemIdx = 1
ORDER BY ChunkIdx

which results in 0x001122338899AABB44556677, the intended result for a single ItemIdx.

Is there any way to concatenate for all ItemIdx entries in a neat way? My desired result is something like:

ItemIdx | Data
1       | 0x001122338899AABB44556677
2       | 0xFFEEDDCC77665544BBAA9988

CLR would be a very sensible alternative, although the lack of support for table valued parameters makes any approach I can think of awkward. I've had various attempts using GROUP BY, COALESCE, FOR XML, CTEs and a couple of of others but no luck.

I wrote a CLR function. Search for `group_concat` for SQL Server. Here is one example: https://groupconcat.codeplex.com/ It is for aggregating strings, but the same approach will work for `varbinary` as well. Alternatively, it could be done on the client side. — Vladimir Baranov, Mar 02 '16 at 11:26
Thanks Vladimir, not keen to do this client side as we have multiple clients & it in our case that would mean code repetition. I had a look and all the `group_concat` snippets I've found are string based. I'm not sure modifying the Codeplex example is viable as its CLR based? (and the CLR source isn't provided) — Graham Fletcher, Mar 03 '16 at 13:37
What do you mean "the CLR source of the Codeplex example is not provided"? There is full source code there. You can take it as an example and write your variant that works with `varbinary` instead of `varchar`. — Vladimir Baranov, Mar 03 '16 at 22:03
Sorry, you're right. Initially I only looked in the ZIP download which has only SQL (with CLR binary embedded within). Dived in a bit further & found the C# source. — Graham Fletcher, Mar 04 '16 at 23:17

Lukasz Szozda · Accepted Answer · 2016-03-02T11:54:54.863

You could join it using FOR XML:

DECLARE @ItemIdx INT = 1;

SELECT CONVERT(VARBINARY(MAX),
    (
     SELECT CONVERT(VARCHAR(MAX), BinaryData,2) AS [text()]
     FROM @testTable
     WHERE ItemIdx = @ItemIdx
     ORDER BY ChunkIdx
     FOR XML Path('')
    ),2)

LiveDemo

For all rows at-once you could use:

SELECT DISTINCT ItemIdx, CONVERT(VARBINARY(MAX),
       (
         SELECT CONVERT(VARCHAR(MAX), BinaryData,2) AS [text()]
         FROM @testTable t2
         WHERE t1.ItemIdx = t2.ItemIdx
         ORDER BY ChunkIdx
         FOR XML Path('')
       ),2) AS Data
FROM @testTable t1;

LiveDemo2

And final check in varchar form:

LiveDemo3

Output:

╔═════════╦════════════════════════════╗
║ ItemIdx ║            Data            ║
╠═════════╬════════════════════════════╣
║       1 ║ 0x001122338899AABB44556677 ║
║       2 ║ 0xFFEEDDCC77665544BBAA9988 ║
╚═════════╩════════════════════════════╝

Thanks, nice working answer. Do you know if the 'texty' nature of XML use imposes much CPU overhead? — Graham Fletcher, Mar 03 '16 at 13:38

How to combine binary chunks in SQL across rows?

1 Answers1