Post reply

SQL Server Table Size Limitations and Performance Impacts

  • August 12, 2004 at 11:05 am

    #164700

    Hi :

     

    I have two questions:

    1. Is there a table size limitation on SQL Server Tables in terms of number of

    rows, columns and total table size.

    2. From performance perspective, is it better to have more coulmns in the table or more rows in the table ? I am creating a table which, if use a more column approach can have around 2M rows and 124 columns but in more row approach can have upto 3.5 M rows and 84 columns.

    Thanks for your help.

  • August 13, 2004 at 3:29 am

    #518821

    It seems to me you do not really normalize when you have this. Is this so?

    It is better to normalize for data consistency...

  • August 13, 2004 at 6:10 pm

    #518987

    There are some limitations.

    Columns per base table: 1024 

    Rows per table: Limited by available storage

    For more info, please refer to BOL with title "Maximum Capacity Specifications"

    Regarding table designing, it really depends on your needs. You can completely normalize your table or you can compromise a little bit if you want to use the table as a report base table.

    Still I recomend you to normalize your table as completely as possible and you can build another table for report.

     

  • August 13, 2004 at 6:30 pm

    #518988

    From performance reasons you want to have indexes on the table.

    ( but probably not 84 or 124!, which columns are searched )

    Why 1 big table ?

    How do You present the data ?

    /rockmoose


    You must unlearn what You have learnt

  • August 16, 2004 at 11:06 pm

    #519246

    Unless you have TEXT or BINARY fields, you're also limited to (if I remember correctly) only 8096 bytes per row no matter how many VARCHAR(8000) fields you have.  So, my answer would have to be I'd rather have a large number of rows than a large number of columns.  The real fact of the matter is like Rockmoose said... "Why 1 big Table"?  Look up the "3rd normal form" of database normalization for info on how to structure a database.

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

  • August 16, 2004 at 11:07 pm

    #519247

    One more thing... again, I don't remember the exact number but if you end up with over something like 254 columns in a table, IT CANNOT BE REPLICATED if replication is one of the things you intend to do.

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