One solution for SQL Server to reference a maximum of 256 tables

A problem occurred recently during bug fixing. The system dynamically creates a view based on the number of projects to combine some data of all projects. When the number of projects in the system exceeds 256, the problem occurs. SQL Server throws the following exception:

MSG 106, Level 15, state 1, procedure vsp_t, line 256

Too partition table names in the query. the maximum allowable is 256.

 

Of course, it is a poor design to Union the data of all projects into one view. However, it is really difficult to change this design,What should we do?

 

Tests showed that SQL Server T-SQL batch processing fully supports more than 256 union256 tables, and stored procedures also support referencing more than tables.

 

Therefore, the statement used to create a view is changed from create view to create procedure without any parameters. The SQL statement originally called in select * from view is changed to Exec.

 

In this way, the number of system projects is no longer limited to 256.

 

However, when there are as many as 10000 projects in the system, it takes more than a dozen seconds to create a data stored procedure for all the Union projects, and it takes more than four minutes to execute the stored procedure. Therefore, to fundamentally solve this problem, we should avoid the case of multi-Table union or join in design!