Unanswered: Maximum number of rows in a Partitioned SQL table
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...
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.