We have a number of tables (about 40) with approximately 2500 columns between them that all have matching primary keys, but different unique columns. For various procedures and views, we'll draw on any number of them – sometimes hundreds of them – to build the necessary results.
I've been considering creating a view that ties all these tables and columns together primarily for the sake of simplicity in referring to the tables and columns in code for subsequent procedures and views, but I wanted to check that I'm not making a major mistake in doing so. Unfortunately, I don't directly have the ability to change the tables, although there are circumstances where the tables will have columns added or removed, so I need to account for that.
My first question is, I would expect for any subsequent query against the view that the SQL Server optimizer would exclude any tables or columns from the actual execution that aren't actually used in the result set, correct?
I would also need to create a stored procedure to update the definition of the view so that it adjusts the columns included in the view as they change, correct? Are there any features or possible hang-ups I'm not considering here?
Best Answer
To summarize discussion on the topic, creating this sort of "Uber" view with several tables that have matching primary keys and similar (if not the same) row counts is not necessarily a bad idea for the purpose of streamlining code.
Additionally, any tables or columns not used in subsequent queries on such a view are dropped from the execution plan in most cases, so performance is most likely unharmed by unnecessary joins in the view definition.
However, how the view is used down the road is an important consideration to bear in mind and should be avoided in multi-layer view within view, or view within stored procedure calls.
One interesting little note I learned while testing this out is there is a View width limit of 1,024 columns in SQL Server, so my particular case is simply not possible to accomplish.