Stairway to Advanced T-SQL Level 4: Using Views to Simplify Your Query

Question

Stairway to Advanced T-SQL Level 4: Using Views to Simplify Your Query

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

jay 87680 SSC Enthusiast Points: 137 More actions · Answer 1

Actually we went back to the original topic which was my disagreement with the following statement:

"In this case SQL Server will still have to join all the tables in the view to return the data from a single table"

As you mention, this is "Table Elimination" which was exactly the point and you are right that it doesn't only apply to views, but it also applies to them.

A lot of people are not aware of this, and its a reason why some may have the misconception of the above statement. I just wanted to clarify that its not always the case.

Anyway, I like to try to apply as many "Best Practices" to the design of the database and have the necessary views so that the queries are consistent by using the views that should provide the most efficient access to the underlying data.

I thank you all for your comments.

Jose

Jeff Moden SSC Guru Points: 1004149 More actions · Answer 2

jay 87680 (4/8/2014)
Actually we went back to the original topic which was my disagreement with the following statement:
"In this case SQL Server will still have to join all the tables in the view to return the data from a single table"
As you mention, this is "Table Elimination" which was exactly the point and you are right that it doesn't only apply to views, but it also applies to them.
A lot of people are not aware of this, and its a reason why some may have the misconception of the above statement. I just wanted to clarify that its not always the case.
Anyway, I like to try to apply as many "Best Practices" to the design of the database and have the necessary views so that the queries are consistent by using the views that should provide the most efficient access to the underlying data.
I thank you all for your comments.
Jose

But it doesn't work the way you originally described it... not at all. Of course, you also admitted to that. I'll try your code example tomorrow. Thanks for posting it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004149 More actions · Answer 3

Ray Herring (4/8/2014)
The direction of the discussion has strayed a little bit from the original topic:-P
The behavior you guys are discussing is not specific to Views. it is general behavior of the query engine. The query engine will eliminate tables from any query when Referential Integrity is full declared and the various constraints are trusted. Table Elimination is a feature of most DB engines (you can find web references to Oracle and others).
I am not saying the discussion is not interesting, I just want to point out that the behavior is not specifically caused by Views. It is more related to fully implemented DRI.

Absolutely agreed but I wouldn't call it "strayed". Views are queries, too! 🙂 Like Jose said, this sidebar came up because table elimination not only wasn't mentioned but a statement to the contrary was.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)