I will make a system that will have tables around 12million records in a year at least, to make my web system Pueba say my boards about how many rows should be for good performance tests:
could have a table of rows 4million to make my tests paging and all that, taking into consideration that these tables than 4 million should partition ....
Have a look at this thread:
how to manage 120,000,000 records in a single SQL SERVER Database
Hi,
You are plan to use partitioning on this table because of 1) better read/write performance and 2) because of better manageability (rebuild index, sliding window scenario, ...).
The second benefit will be accomplished for sure, but the first one depends on the queries being executed against the table. If they are aligned with the partitioning column, SQL Server will perform partition elimination and queries will be faster then on table without partitions. But if queries are not aligned then SQL server will have to scan all partitions to find the qualifying rows for given predicate. To avoid this you will need to implement indexes that are not partitioned which is what you had on not partitioned table.
Those are things to think about before implementing partitioning.