Post reply

Table Size

  • September 28, 2007 at 3:39 am

    #198409

    Hi,

    One of my table is having around100 columns and most of which are Numeric, NULL. Out of 100 columns only 10 columns are having data and others are null. But the table is growing big in a size wise. Actual data size is only 2 GB. But table size is showing 60 GB. Can anyone let me know how much size of null integer occupy the space?

  • September 28, 2007 at 3:47 am

    #737058

    SreenivasReddy (9/28/2007)

    Hi,

    One of my table is having around100 columns and most of which are Numeric, NULL. Out of 100 columns only 10 columns are having data and others are null. But the table is growing big in a size wise. Actual data size is only 2 GB. But table size is showing 60 GB. Can anyone let me know how much size of null integer occupy the space?

    A null integer will still occupy 4 bytes, independently from its value (whether it is null or not null).

    The information about it being null is stored separately on a single bit.

    SQL Server 2008 will help this situation with sparse columns, but that is still far away.

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • September 28, 2007 at 5:29 am

    #737077

    NULL of not, integer columns take up the same amount of space in SQL 2005.

    You have a design problem. You should not normally have that may columns, and if most of them tend to be NULL, your data is not stored very efficiently.

    If you have the opportunity, you should go back and try to come up with a design that better suits your needs.

  • September 29, 2007 at 9:21 pm

    #737467

    Normalize 'til it hurts! It'll save lot's of pain later on... kinda like the pain you're going through right now... 😉

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

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

  • October 18, 2007 at 9:04 am

    #743174

    Is there any alternate solutions?

  • October 18, 2007 at 4:46 pm

    #743336

    "Sister" table for the normally null columns... proper normalization of the table would be better.

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

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

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

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