Find Space occupied by a Row

  • T.Ashish

    SSCarpal Tunnel

    Points: 4246

    Hi All,

    Do we have any way to find out the space occupied by individual records in a table? My objective is to find out the space occupied by each record in a table where columns names can vary from int, float, varchar, nvarchar(max) etc.

    For example, in below table, where content of both the records is very much different:

    records

    Thanks in advance.

    Regards.

     

  • Jeff Moden

    SSC Guru

    Points: 996655

    T.Ashish wrote:

    My objective is to find out the space occupied by each record in a table ...

    To what end?  What will such information be used for?  I ask because the ultimate usage of such information may make a difference in how such a thing might be done.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719935

    You can calculate this easily, using the datatypes and accounting for overhead. I believe varchar is 2 bytes overhead, and nvarchar is likely 2 or 4. It's in the docs somewhere.

    However, I agree with Jeff. What's the point here?

  • frederico_fonseca

    SSChampion

    Points: 14686

    and... if using compression space used will vary a lot depending on the compression type and on the data.

  • Mr. Brian Gale

    SSC-Insane

    Points: 22926

    To add to Steve's comment, there is more overhead than just the dataype overhead on a row.  On top of the overhead by VARCHAR and NVARCHAR, you also have the row header, null block, etc.

    A good read on that is available here:

    https://www.c-sharpcorner.com/article/calculate-data-row-space-usage-in-sql-server/

    If you need the min, max, and average,

    DBCC SHOWCONTIG('<table name>') WITH SHOW TABLERESULTS

    will give you that.

    If you need an estimate on row sizes, you can use DATALENGTH(<column name>) and sum up that value for every column.  You'll be missing the overhead (both datatype and the SQL magic stuff), but will give you a rough estimate.  Did that on a 3 column table I had and the table had 11 bytes of overhead.  I figured this out by using the DATALENGTH calculation I suggested, grabbed the smallest value and compared that to the MIN value of the SHOWCONTIG command.  Probably not 100% accurate, but got me a value that worked for getting the max value that matched up with the SHOWCONTIG results.

    Would be a huge pain in the behind to make this work for all tables, but if you are only working with a single table, it gives you a rough idea...

  • Jeff Moden

    SSC Guru

    Points: 996655

    Rather than using DBCC SHOWCONTIG, use sys.dm_db_index_physical_stats.  The row header information is included as a part of the Min, Avg, and Max row sizes.

    I'd still like to know why someone needs it for every row, though.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply