We currently have an SQL Server 2000 database with one table containing data for multiple users. The data is keyed by memberid which is an integer field. The table has a clustered index on memberid.
The table is now about 200 million rows. Indexing and maintenance are becoming issues. We are debating splitting the table into one table per user model.
This would imply that we would end up with a very large number of tables potentially upto the 2,147,483,647, considering just positive values.
Does anyone have any experience with a SQL Server (2000/2005) installation with millions of tables?
What are the implications of this architecture with regards to maintenance and access using Query Analyzer, Enterprise Manager etc.
What are the implications to having such a large number of indexes in a database instance.
All comments are appreciated.
Edit: I do not agreed to this question being migrated to Serverfault. This is a programming related question.