Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com May 25 '12 at 13:49

This question came from our site for professional and enthusiast programmers.

    
@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 May 25 '12 at 13:44
    
What's the maximum size of each row? –  Nick Chammas May 25 '12 at 15:47

3 Answers 3

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).

share|improve this answer
    
I guess this can be achieved by partitioning the table and havening the partitioning to use different file groups, if I'm correct ? –  GAP May 25 '12 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 May 25 '12 at 15:29

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/

share|improve this answer
    
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 May 25 '12 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 May 25 '12 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 May 25 '12 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 May 26 '12 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 May 27 '12 at 6:23

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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