calculate actual row size?

Posted on 2007-07-23
Last Modified: 2008-01-09
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?

Question by:ottenm
    LVL 8

    Expert Comment

    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

    Author Comment

    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.
    LVL 8

    Expert Comment

    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
    LVL 35

    Accepted Solution


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

    use AdventureWorks

    select *
    from Production.Product

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

                case CHARACTER_SET_NAME
                      when 'unicode' then 2
                      else 1
    from information_schema.columns
    where TABLE_SCHEMA = 'Production'
    and TABLE_NAME = 'Product'


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

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now