Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011
    Posts
    1

    Unanswered: Maximum number of rows in a Partitioned SQL table

    Hello

    I am currently looking to purchase Microsoft SQL Server 2008 (Enterprise Edition) but before I do, I would like to know what the maximum number of rows is, that can be stored in a partitioned SQL table (and non-partitioned for my understanding and completeness).

    I have read on more unofficial internet MS SQL sources, that you are limited only by the storage hardware available to you. Is this correct? Finding an answer to this question is not that easy (on the internet at least).

    I am looking to store (and query on) up to 40 terabytes of transaction data (20,000 million rows approx.) in 2 SQL tables (partitioned of course) which equates to 20 years of data.

    Can this be done in Microsoft SQL Server 2008?

    If this answer is ‘yes’, can any recommendations be made regarding common sense approach, storage hardware required and D/R contingency measures for this size of database...

    Thanks in advance

    Mark.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    yes it's possible. you might look here for tips on VLDB best practices: SQL Server Customer Advisory Team - SQL Server Best Practices

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    yes, definitely possible, I have tables with rows in the billions w/ no partitioning scheme in place. Funny how you wrote 20 billion as 20,000 million

    40TB still sounds rather large for a 20 billion row table, that comes out to a little over 2KB per row. Is your data normalized?

    Some SQL Server 2008 specs for you:

    Maximum database size: 524,272 terabytes
    Maximum file size (data): 16 terabytes
    Maximum file size (log): 2 terabytes
    Rows per table: Limited by available storage
    Tables per database: Limited by number of objects in a database*
    Partitions per partitioned table or index: 1,000
    ___________
    * Database objects include objects such as tables, views, stored procedures, user-defined functions, triggers, rules, defaults, and constraints. The sum of the number of all objects in a database cannot exceed 2,147,483,647.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •