none
Max number of rows in a table

    Question

  • Hey

    I'm doing a bit of research on which database to use for a new project.

    And one of the factors I have to take into consideration, is that we will have some tables with a high amount of records. I originally intended to use mysql (because it's free), but I have found that it has a cap of 4.2 billion rows per table. And it is a possibility that we will exceed that number in a few of our tables.

    So far, from looking around here, and on the web, I've only be able to find, that is no such cap on tables in mssql, and that table sizes is only limited by available storage. The thing is, 99% of the info I found regarding tablesize limits in mysql, also stated that it was only limited by the storage size (4gb, not all available). I had to look very thouroughly to find that last 1% where someone mentioned the 4.2 bil. upper limit.

    I hope someone here can confirm that, there is indeed no limit on the number of records in a single table in mssql, no hidden cap, however unlikely to be an issue.

    I find, that there's a ton reasons to pick mssql over mysql. But this factor will be a big selling point, in convincing the higher-ups that mssql is the better choice for my project.

    Wednesday, September 15, 2010 1:52 PM

Answers

  • The maximum row count is all available storage.  SQL Server maximums are listed here: http://msdn.microsoft.com/en-us/library/ms143432.aspx

    However, depending on your table design, you can have problems much sooner.

    If you use an integer-valued unique key value (primary key or alternate key) then once you have used all 4.2 billion integer values you will not be able to insert more data.  This is caused by the UNIQUE constraint, not a lack of ability to store more data.  (Most people start such values at 1 and ascend, so they can only use 2.1 billion values.)

    Of course, by designing the table with a bigint value, you would be able to store numbers from -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807.  Or you could do something else altogether.  The thing is, your design is likely to limit you long before the basic SQL Server maximum sizes become important.

    RLF

    • Marked as answer by MikalJ Thursday, September 16, 2010 6:42 AM
    Wednesday, September 15, 2010 2:55 PM
  • see http://msdn.microsoft.com/en-us/library/ms143432.aspx  There is no max rows.  You can populate the table until you run out of disk space
    SQL DBA
    • Proposed as answer by Chirag Shah Wednesday, September 15, 2010 2:20 PM
    • Marked as answer by MikalJ Thursday, September 16, 2010 6:42 AM
    Wednesday, September 15, 2010 2:05 PM

All replies

  • see http://msdn.microsoft.com/en-us/library/ms143432.aspx  There is no max rows.  You can populate the table until you run out of disk space
    SQL DBA
    • Proposed as answer by Chirag Shah Wednesday, September 15, 2010 2:20 PM
    • Marked as answer by MikalJ Thursday, September 16, 2010 6:42 AM
    Wednesday, September 15, 2010 2:05 PM
  • The maximum row count is all available storage.  SQL Server maximums are listed here: http://msdn.microsoft.com/en-us/library/ms143432.aspx

    However, depending on your table design, you can have problems much sooner.

    If you use an integer-valued unique key value (primary key or alternate key) then once you have used all 4.2 billion integer values you will not be able to insert more data.  This is caused by the UNIQUE constraint, not a lack of ability to store more data.  (Most people start such values at 1 and ascend, so they can only use 2.1 billion values.)

    Of course, by designing the table with a bigint value, you would be able to store numbers from -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807.  Or you could do something else altogether.  The thing is, your design is likely to limit you long before the basic SQL Server maximum sizes become important.

    RLF

    • Marked as answer by MikalJ Thursday, September 16, 2010 6:42 AM
    Wednesday, September 15, 2010 2:55 PM
  • Thanks to both of you.

    I had previously found the page you referenced, but apparently missed the bit I was looking for.

    Thursday, September 16, 2010 6:46 AM