Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am working on an application in C# .Net, my backend is SQL Server 2005.

I am developing a sale point form, in my table tblInvoice and tblInvoiceDetail may be added 10000 rows per day .

This is a heavy volume of data, please guide me when 6 months data will be added in a table.

Then is it effects on the speed of data retrieving ? If yes then what should we do?

Please help me about this.

share|improve this question
2  
Read up on indexes, msdn.microsoft.com/en-us/library/ff650692.aspx –  Ash Burlaczenko Sep 14 '11 at 8:25
4  
A million rows?? That's nothing for SQL Server - if you have the proper indices in place. Also: if you have this kind of data, you might want to look into SQL Server table and index partitioning –  marc_s Sep 14 '11 at 8:26
3  
10k records per day isn't very high load. As long as your database is indexed and partitioned properly, and your queries are profiled and optimal, you should be able to handle many year's worth of data. –  StuartC Sep 14 '11 at 8:26
    
how many records a table can contains ? is there any limit in sql server ? –  Haider Ali Sep 14 '11 at 9:20
    
@Haider Ali - the size of a SQL server table is limited only by your storage - see msdn.microsoft.com/en-us/library/ms143432.aspx - however, that's not to say that queries against the table will perform if you allow it to grow to many billions of rows –  Ed Harper Sep 14 '11 at 12:57
show 1 more comment

migrated from stackoverflow.com Sep 14 '11 at 12:25

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

3 Answers

10,000 records per day, even spread out over an mere 8 hour work day, only amounts to one record every 2.8 seconds. That's just not a lot of volume. If you have good indexes on the table, retrieval will not be a problem, either. So don't worry about it just yet. Sql Server is well able to handle this kind of volume.

share|improve this answer
add comment

There will be no problem in data retrieval with good indices. You problem will be scalability problem.

Adding/removing column/index will take longer time, Backup will take more space and will take much longer time, Any unoptimized query will be deadly slow ... etc

share|improve this answer
add comment

You should provide a method of archiving data older than X to another table, or set of tables. With a search function capable of searching all records, not just active ones. That way in the future, IF/WHEN the table size is too large, there is already an elegant solution.

share|improve this answer
add comment

Your Answer

 
discard

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