How to Design and Handle a Table with 30 Million rows

[mvkishorereddy]

Hi

my requirement is like this - I need to maintain a subscription DB with more then 30 million rows in sql server thet keeps on increasing. I need to Update ,Insert and select from the Table. Problem is this has to be integrated with existing application where performance should not get effected. In this table application id and user number are unique.

Can anyone suggest what should be the approach.with minimum effect on performance of existig application.

Thanks

[iburyak]

If you have proper indexes it should work with no problem.

Writing new queries could be challenging and will require more indexes. But with proper indexing you can manage it too.

Don't create clustered index if possible it will slow down update\insert and will require plenty free space on a Server.

Control updated number of records update, never update all table at once, it could bust transaction log and stop a Server.

[pallavi29]

Can i select distinct values of a column from such a large table without using select distinct. Because this query runs very slowly on such a large table.

[iburyak]

Try to put index over this column.
In this case only index will be searched and not table itself.

Good Luck.