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.

  • 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. – StuartLC 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

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.

| improve this answer | |

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

| improve this answer | |

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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