My team has proposed to store session state data for each open client session in a table in the database. The application server creates a new table for each client session as the user logs in via web browser. The data that needs to be kept for the session is created along with the table, and is not modified (except in rare circumstances). When the user logs out, the table is dropped. Orphan tables are purged regularly.
The application server manages these short-term tables. There won't be any database code that manages the tables or the data.
The data in these tables has relational links to other tables in the database. The tables will be joined in queries to other tables in the database.
I'd like to find out the pro's and con's of this design, in contrast to others. For example, all of the same functionality could be implemented in a single table that stored state data for all sessions. If done this way, there would be one clustered index on the session ID. Each access to the state data would read all of the data for exactly one session ID.
What are the scalability concerns for creating/dropping multiple tables rapidly? Assume this occurs on the order of 1K-10K time per hour.