• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1233
  • Last Modified:

calculate actual row size?

Is there an easy way to find the length of the longest row in a table?  I'm hitting the maximum possible row size of 8060, but it seems like a *lot* more than I am actually using.  And if there isn't an easy way, is datalength(col1) + datalength(col2) ..... accurate?

Thanks
0
ottenm
Asked:
ottenm
  • 2
1 Solution
 
k_rasuriCommented:
first you have to make sure that the datatypes are all chatactors for all the fields..then you can use lenght function

select LEN(Col1+col2+col3+..............) from yourtable
0
 
ottenmAuthor Commented:
some are text, which is why I suggested datalength.

still hoping there's some way around listing every column in the table, it has around 75 fields.
0
 
k_rasuriCommented:
you can convert the data type and start adding them..something like

select len( cast(column1 as nvarchar)+col2+col3+cast(col4 as nvarchar)+..........) from your table
adding all 67 columns is a big task...

you may want ot use information_schema.columns and cursor to dynamically add these fields
0
 
David ToddSenior DBACommented:
Hi,

If you are looking for the easy way to calculate the length of the columns, consider this:

use AdventureWorks
go

select *
from Production.Product
go

select *
from information_schema.columns
where TABLE_SCHEMA = 'Production'
and TABLE_NAME = 'Product'

select
      sum( CHARACTER_MAXIMUM_LENGTH *
            case CHARACTER_SET_NAME
                  when 'unicode' then 2
                  else 1
            end
      )
from information_schema.columns
where TABLE_SCHEMA = 'Production'
and TABLE_NAME = 'Product'

Regards
  David

PS Of course this ignores the overhead of different columns, and doesn't take the numeric columns in this table into account.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now