1

I am trying to calculate total row size for a table, whose formula is

row_size = Fixed data size + variable data size + null bitmap +4.

for this I am trying to find the average variable data size through query in SQL. Is there a query that can be used in SQL which would achieve this?

Lewis Browne
  • 904
  • 7
  • 23
New_learner
  • 21
  • 1
  • how do you define variable data size? if is varchar instead of char? if it so then select the varchar column and use AVG(LEN(Column)) function to get the average length. – Adinugraha Tawaqal Sep 21 '18 at 09:44
  • Have a look at this: https://stackoverflow.com/questions/496413/determine-row-size-for-table and this http://www.sqlserver-dba.com/2013/07/calculate-sql-row-size.html – Alex Sep 21 '18 at 09:45

1 Answers1

0

not a complete answer, but how about something like this

build dynamic sql from the INFORMATION_SCHEMA.COLUMNS table - you could maybe introduce your own flag to flag variable/non-variable length fields.

Building the SQL using a cursor is an option too

--data lengths of each table

    DECLARE @SQLa as nvarchar(max) ='';
    select @SQLa = @SQLa + 
                        'SELECT COALESCE(DATALENGTH(['+ Column_Name +']), 0) as dlen, ''' 
                        + Column_Name 
                        + ''' colname, ''' 
                        + Table_name 
                        + ''' tabname  FROM [' 
                        +  Table_name 
                        + '] UNION ALL ' FROM   INFORMATION_SCHEMA.COLUMNS  

    SELECT @SQLa = LEFT(@SQLa, LEN(@SQLa) -10)

    SELECT @SQLa = 'SELECT DQ.tabname, SUM(DQ.dlen) as TotalLen FROM (' + @SQLa + ') DQ GROUP BY tabname'

    select @SQLa;
    EXEC sp_executesql @sqla;
Cato
  • 3,652
  • 9
  • 12