2

I would like to know if there's a way to find out the total number of bytes used by a specific row in SQL Server. I am able to get the maximum row size by calculating the sum for all nvarchar columns, but it creates an issue when we have a nvarchar(MAX) or binary column. I am looking for the exact no. of bytes used by a row on hard disk.

Is something like this possible?

Nitesh
  • 2,286
  • 2
  • 43
  • 65

2 Answers2

2

You can use DATALENGTH Funtion for varbinary in sql.

Although it is column specific, you can use it for all columns using where clause to get your row

SELECT DATALENGTH(column1), DATALENGTH(column2)... DATALENTH(columnN) FROM Table where <condition to get your row>;
Dheeraj Kumar
  • 3,917
  • 8
  • 43
  • 80
-1

Use this, will do datalength on all columns for a row

select
    SomeOtherColumn,
    Bytes = datalength((select x.* from (values(null))data(bar) for xml auto))
from Table
Gabriel Grünberg
  • 36
  • 2
  • 1
    Welcome to Stack Overflow. Unfortunately it seems you're answering a question that was asked 4.5 years ago, and providing basically the same answer that was there already. Did you mean to add something that wasn't already addressed? – Dennis Mar 12 '21 at 21:05
  • The base function is the same, however my way is dynamic and you dont have to specify the columns which makes it much easier to use in dynamic code or even making an SP that does it for any input. – Gabriel Grünberg Mar 13 '21 at 15:10