SQL Server 2000 tables

Posted by user40766 on Server Fault See other posts from Server Fault or by user40766
Published on 2010-04-28T17:38:01Z Indexed on 2010/04/29 1:27 UTC
Read the original article Hit count: 560

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.

My questions:

  1. Does anyone have any experience with a SQL Server (2000/2005) installation with millions of tables?

  2. What are the implications of this architecture with regards to maintenance and access using Query Analyzer, Enterprise Manager etc.

  3. What are the implications to having such a large number of indexes in a database instance.

All comments are appreciated.

Thanks

© Server Fault or respective owner

Related posts about sql-server

Related posts about sql-server-2000