Simplify SQL Server Query by Eliminating Views

Question

I am currently working on a very complex view that in turn retrieves data from several other views. I am worried that if someone changes one of my source views, my query will stop working.

Is there a way on how SQL Server can provide me with a query that achieves the same result but uses source tables (instead of views), thus eliminating the need for intermediary views and improving performance by omitting redundant joins?

Nested views are harder to understand and debug for us and optimizer as well — Neeraj Prasad Sharma, Aug 12 '15 at 11:12
What do you mean "is there a way"? It's called writing code. Am I missing something? — Tab Alleman, Aug 12 '15 at 13:14

score 1 · Answer 1 · answered Aug 12 '15 at 11:18

1

Nope.... You'll have to have a workaround.

What I suggest is to copy the views that you have to use and create them with new names, indicate "do not change" in comments and history section in the view definition you created.

answered Aug 12 '15 at 11:18

d_luffy_de

967
1
9
24

score 1 · Accepted Answer · answered Aug 12 '15 at 11:34

Look at the view code in design view in SQL server management studio. Copy the SQL code and assign the select output of a view into a temp table in your SQL Stored procedure. Now you have a copy inside SP and you do not need to depend on view.

Based on my experience I've found that often view has redundant/repeated data columns which need complex calculations and are usually not needed by everything that consumes those views.

Also by removing nesting, you will get a performance gain.

Alternatively, if you are not worried about performance, you can duplicate a view and label it differently too.

score 1 · Answer 3 · answered Aug 12 '15 at 11:35

To avoid breaking changes to the underlying views, you could specify the WITH SCHEMABINDING option. That will ensure dependent views cannot be changed without recreating the views in the correct dependency order.

I'm not aware of a tool that can refactor nested views into a single nested one or query. Ideally, the SQL Server optimizer would do the needed optimization with the existing nested views but it currently does a poor job of that in some cases.

I suggest you start by taking the outmost view query and replacing each referenced view with a derived table, and nest as needed. That will result in a really ugly query you can then refactor until performance is adequate.

score 0 · Answer 4 · answered Aug 12 '15 at 11:37

"Is there a way on how SQL Server can provide me with a query ..."

No. Magically producing the combined SQL for you isn't available that I'm aware of.

But there is nothing stopping you from using the sql of the other views within your new view. You may find more opportunity to optimize this way as well.

Simplify SQL Server Query by Eliminating Views

4 Answers4