0

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?

0

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.

-1

I think your view solution could be appropriate in a scenario where you would vertically partition the data, i.e. where all tables contain the same columns and the view would merely UNION those rows with check constraints.

However, what you're proposing is a view that joins a large number of tables together, i.e. partitioning horizontally. Even if we assume that all tables have matching primary key definitions, making joins a lot more efficient, the server still has to join each and every one of them, every time, even if you use just a single column. Here's why:

Example:

CREATE VIEW dbo.everything
AS
SELECT a.a1, a.a2, a.a3, a.a4, b.b5, b.b6, b.b7, b.b8
FROM dbo.tableA AS a
INNER JOIN dbo.tableB AS b ON a.key1=b.key1 AND a.key2=b.key2

If you query this view, the server will still need to check that there is a matching record in dbo.tableA for each row in dbo.tableB and vice-versa. The more tables you add, the more joins. You would work around this by using FULL JOIN, but I doubt that this will have the desired effect once you add a lot of tables.

In my opinion, designing your queries to use just the needed tables (as they do currently) will be your best bet. If you need to improve query performance, I would look at creating specifically targeted indexes on frequently used tables/columns.

Basically, it comes down to performance vs simplicity of use, but the performance penalty for your view solution may be severe. Building a stored procedure to actually update the view is a fairly simple matter.

  • Thanks for your response Daniel! Does the SQL Server query optimizer actually go through all those joins though if the data from those tables or columns isn't in the result set of the query? It seems to me that it recognizes those tables and columns aren't referenced and drops them from the execution plan. – Ian Joyce Feb 2 '15 at 21:37
  • BTW - you are correct that the primary key definitions are matching – Ian Joyce Feb 2 '15 at 21:37
  • 1
    This is factually wrong in SQL Server. The appropriate solution is to left join the associated tables to the primary table, so that **only tables supplying those columns actually used by a query are added to the query plan. @Aaron Bertrand has demonstrated this succinctly in several demonstration available on the web. – Pieter Geerkens Feb 2 '15 at 22:51
  • 2
    Hey, we're all human. While I do point this out these days, I myself was corrected about the same thing not all that long ago. – Aaron Bertrand Feb 2 '15 at 23:18
  • 1
    So, it seems like the answer to the question is, it's not necessarily a bad idea to create such an "Uber"-view (left joining each of the tables to one primary table) for the sake of simplifying some tasks, but one should be careful with how it is referenced in the future, taking care not to have views referencing views referencing the uber-view, and so on. Does that sound correct? I would upvote the comments here, but think I don't have the cred. :) – Ian Joyce Feb 3 '15 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.