Determining the bytesize of a row from a SQL query

Question

I'm looking for a simpler way to determine the bytesize of a returned row from a query in SQL Server. I need this overall bytesize to debug a specific issue I'm running into.

For example, lets assume that I have the following query

SELECT 
COl1, 
COL2, 
COL3, 
COL4, 
COL5
FROM TABLEA
JOIN TABLEB ON TABLEA.COL1 = TABLEB.COL1

I've figured out that I can select the specific bytesizes of each column in the record with the DATALENGTH() function like this

SELECT 
DATALENGTH(COl1), 
DATALENGTH(COL2), 
DATALENGTH(COL3), 
DATALENGTH(COL4), 
DATALENGTH(COL5)
FROM TABLEA

And if I want to determine the overall length of the returned record, the datalengths can be added up like so

SELECT 
DATALENGTH(COl1)+ 
DATALENGTH(COL2)+ 
DATALENGTH(COL3)+ 
DATALENGTH(COL4)+ 
DATALENGTH(COL5)
FROM TABLEA

My issue is that the actual query is a little over two thousand columns, and some of the resulting columns consist of sub queries. The complexity would make converting the existing query to summing up the bytesizes a complex manual task that would be prone to user error, and would take a considerable amount of time.

Are there any good ways to convert the overall returned record into a bytesize for analysis?

Answer 1

Not datalength() but this will give you total len() per record without having to list all of the columns.

Select A.ID
      ,B.Bytes
 From YourTable A  -- (or Your complex query) A
 Cross Apply (
              Select Bytes=sum(len(value)) 
               From  OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper ) ) 
             ) B