Does performance of a MSSQL database drop with the more tables that are inside of it? Even if those tables contain little to no data (ie maybe only 100 rows), however there being hundreds of tables, possibly thousands added per day?
The particular version of MSSQL in question being used is 2008 standard edition.
The logic here is that each user signing up on a website gets it's own set of tables for storing their data separate from all other users. This would allow the user account to be moved between servers and completely deleted if need be leaving little to no traces behind. Plus providing the user added comfort in knowing their data is completely segmented and thus harder to get at.
The other issue is that user account data is stored in the tables similair to a vertical database, in that there is a content data table and that has only a few fields in it, but those entries can apply for any type of data, from small posts, calendar entries, private message and to large multipage articles written by the user. A single piece of content might add 10 to 15 rows to that table on average. So having a single large table for all users would seem to be very poor performance in that scenario I would imagine.
The data is only accessible via an api made for the site, which pulls data specific to the user and verifies access of the data as well for each call. Any types of data can be made in the future which is why the users have a content table along with several others for settings and the such. Each user having roughly around 15 tables for various management aspects of the site. For example the api allows you to request content block x from user y as user z. It verifies your security and pulls from the table all the data fields for block x.
So ultimately would the ever increasing massive amounts of tables slow the system down substantially? Or would having such a large amount of data in a single table be a larger slow down?
No, not by any relevant margin.
Your lgoic, though, is still faulty- you loose a lot of sql functionality or have to make tons of unions for cross user queries. I would fire anyone coming up with a concept like that unless he had a VERY special case, which you wont have with 99.99% confidentiallity. Similar level like saying you develop 16 bit software for windows today.
So, in short:
No, no significant performance drop.
Still a bad design decision.