none
Table's max number of rows

    Question

  • Hi,
    I am a newbie to MSSQL and I would like to get your comment on the following questions:
    1. What is the maximun number of rows that a table should have in order to be adequately handled in the sense of Create, Read, Update and Delete?

    2. To be more specific , let say I have the following table:
    file_id (INT)
    senetence_id (INT)
    sentence (CLOB)

    Primary key (file_id, senetence_id)

    The actions I would like to do are:
    select - select all sentences for a certain file_id
    insert - insert a new sentence (for a specific file_id)
    delete - delete a sentence for a specific file_id.
    search - get all sentences that contain a specifc string

    The table's size is going to grow and could get to millions and even more.
    Could i get into a point where I couldn't adequately "work" with the table because of it's size

    Thank you in advance for your assitance
    Monday, March 15, 2010 12:43 PM

Answers

  • there is no limitation about the number of rows in SQL server tables.
    but if you want to make a composite key with 2 interger columns like "Primary key (file_id, senetence_id)"
    you can get 2^32 * 2^32 = 2^64 combinations and this could be a limitation for you.
    in SQL server we are only limited with database sizes.

    you can get much more information about maximum capacity specifications in SQL Server at :

    http://msdn.microsoft.com/en-us/library/ms143432.aspx
    Monday, March 15, 2010 9:42 PM

All replies

  • G M,

    Based on the schema you have provided, it looks like you'll run into issue when you reach (assuming you only use positive numbers) 2,147,483,647 rows (also assuming re-use of deleted identity values). If you are expecting this to run for a little while and are expecting massive growth you may want to look into using BIGINT.

    Edit: Forgot to add that the INT data type ranges from -2,147,483,648 TO 2,147,483,647
    Link: http://msdn.microsoft.com/en-us/library/ms187752(SQL.90).aspx

    Hope this helps,
    Sean
    Monday, March 15, 2010 1:42 PM
    Answerer
  • I am in agreement with Sean,   ***there is no number of rows limitations in SQL Server ***


    Yes, when you use Identity fields you will run into limitations of int datatype but

    you can start with - 2,147,483,648  (negative number) and you could have upto 4 billion rows before you need bigint.


    Monday, March 15, 2010 2:00 PM
  • there is no limitation about the number of rows in SQL server tables.
    but if you want to make a composite key with 2 interger columns like "Primary key (file_id, senetence_id)"
    you can get 2^32 * 2^32 = 2^64 combinations and this could be a limitation for you.
    in SQL server we are only limited with database sizes.

    you can get much more information about maximum capacity specifications in SQL Server at :

    http://msdn.microsoft.com/en-us/library/ms143432.aspx
    Monday, March 15, 2010 9:42 PM