This setup worked fine until recently, as Galwegian predicted, performance issues are springing up. Since then, I have been experimenting with indexes and decided on a clustered index on the first two columns (Hospital code and Department code) on which we would have partitioned the table had we had Enterprise Edition. Following the advices below, I created a handful of huge tables. Update: It has now been about half a year since we first created the tables. My question, will we be able to handle this table in the future, or is it better to split this table up into smaller tables for each customer? I am already noticing performance issues and users being temporarily locked out. A lot of queries will be done on this table. Each customer has approximately 20.000.000 rows of data, and there will soon be 30 customers in the database (if not more).
Recently, I have become worried with the fact that one table in particular will be getting very big. The goal of the database is to acquire aggregate benchmark numbers over several customers. The database will hold data gathered from several customers. Currently I am designing a database for use in our company.
