11

I have an application which inserts more than 1 billion rows annually into a table. This table contains some varchar and bigint columns and one blob column as well.

The 1 billion rows consist of history data which are kept for tracking purpose. So I was wondering whether there will be a table capacity limitation if I continue in this structure according to this MSDN article about maximum table size.

Does the data file size mentioned in that link refer to the table data file group?

Improve this question
2
  • @marc_s thanks for catching that. feel free to join us in The Heap where, among other things, we bring collective attention to these
    – JNK
    Commented May 25, 2012 at 13:44
  • What's the maximum size of each row?
    – Nick Chammas
    Commented May 25, 2012 at 15:47

3 Answers 3

Reset to default
6

There is no practical limit except disk space. I read the table you linked to entirely and checked it.

If you need to go above 16TB you need multiple files (a simple procedure).

Improve this answer
2
  • I guess this can be achieved by partitioning the table and havening the partitioning to use different file groups, if I'm correct ?
    – GAP
    Commented May 25, 2012 at 15:19
  • 1
    That's not even necessary. Just add a new file (to the existing file group). SQL Server will start to fill all files evenly. If one file cannot grow anymore it will just grow the other file.
    – usr
    Commented May 25, 2012 at 15:29
2

a table in sql server 2008 can handle large number of records and as @usr mentioned it depends on disk space but its recommended that if your table has many rows and it keeps on growing that you use Partitioned Table http://technet.microsoft.com/en-us/library/dd578580(v=sql.100).aspx

When a database table grows in size to the hundreds of gigabytes or more, it can become more difficult to load new data, remove old data, and maintain indexes

more info about it

http://msdn.microsoft.com/en-us/library/ms190787.aspx

and how to implement it http://blog.sqlauthority.com/2008/01/25/sql-server-2005-database-table-partitioning-tutorial-how-to-horizontal-partition-database-table/

Improve this answer
5
  • You need to be really careful about partitioning though. The function and key need to be carefully considered, as well as the use case. The logical field to partition on may never be used in any of the queries, which would kill performance.
    – JNK
    Commented May 25, 2012 at 17:27
  • True but billions of rows in a single table is also going to effect performance, there is also the option of spliting ur data in many tables example a separate table for each year and if u want to view all data u can use A view but at least the unsert and update will be faster on each table
    – AmmarR
    Commented May 25, 2012 at 17:36
  • inserts on a huge table aren't necessarily slow, it depends on keys and indexes. I do monthly loads of around 30m rows into a table that has 700m existing rows, and we don't do any partitioning. I did try partitioning but it caused more problems than it solved. This is actually a question if you want to check it out.
    – JNK
    Commented May 25, 2012 at 17:40
  • I was thinking about moving my history data to a seperate table and create a union view so it can be used by the application when need query history + latest data which is around less than 25% of the queries that i have in the system. Will this be more efficient than having multiple data files or partioning the table based on the column which mark data as latest ? From IO operations which will be more efficient ? cause my doubt is it will be same from IO perspective in both solutions.
    – GAP
    Commented May 26, 2012 at 3:16
  • any approach you take has its best practices that can make it good or bad, i mean if you have many tables your query's will be complicated and it will be hard to maintain, if you have one table and use table partitioning there is difference considerations like your sql edition should be enterprise etc, having many data files is a recommended for better IO operations but it also has its best practices, for sql performance there is no straight forward way...
    – AmmarR
    Commented May 27, 2012 at 6:23
0

Perhaps a Partitioned View would work.

From the Using Partitioned View MSDN Article:

Partitioned views allow the data in a large table to be split into smaller member tables. The data is partitioned between the member tables based on ranges of data values in one of the columns. The data ranges for each member table are defined in a CHECK constraint specified on the partitioning column. A view that uses UNION ALL to combine selects of all the member tables into a single result set is then defined. When SELECT statements referencing the view specify a search condition on the partition column, the query optimizer uses the CHECK constraint definitions to determine which member table contains the rows.

I'm not sure how it differs from a Paritioned Table that AmmarR provided info about in his answer.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.