CODEWITHSUNDEEP
sql-server
Achilles
Achilles

Reputation: 1129

Does SQL Server expand a view's sql inline during execution?

Let's say I have a (hypothetical) table called Table1 with 500 columns and there is a view called View1 which is basically 

 select Column1, Column2,..., Column500, ComputedOrForeignKeyColumn1,...     
 from Table1 
 inner join ForeignKeyTables .....

Now, when I execute something like 

Select Column32, Column56 
from View1

which one of the below 3 does SQL Server turn it into?

Query #1:

select Column32, Column56 
from 
    (select 
         Column1, Column2,..., Column500, ComputedOrForeignKeyColumn1,... 
     from 
         Table1 
     inner join 
         ForeignKeyTables ......) v

Query #2:

Select Column32, Column56 
from Table1

Query #3:

select Column32, Column56 
from 
      (select Column32, Column56 
       from Table1) v

The reason I'm asking this is that I do have a very wide table and a view sitting on top of it (that basically inner joins to bring texts from all foreign key ids) and I can't figure out if SQL Server fetches all columns and then selects the ones that are needed or fetches only those that are needed (while also ignoring unnecessary joins etc)...if it is former then a view would not be the best for performance.

Upvotes: 4

Views: 1058

Answers (3)

dean
dean

Reputation: 10098

The view definition is merged with the outer query in very early stage of compilation. You may or may not get the same execution plan for query on a view vs an equivalent query touching base tables, depending on complexity of the view and given the limitations of QO.

For your particular case it's worth noting that an inner join doesn't only fetch data from joined tables, but it also limits the result (in the same way as an IF EXISTS check does). If there is a declarative FK between the tables, the QO will be smart enough not to check the referenced tables, as the existence is guaranteed by the constraint, but otherwise it has to.

Upvotes: 1

Sergei Patiakin
Sergei Patiakin

Reputation: 276

SQL Server query compilation can be split into phases:

  1. Parsing
  2. Binding
  3. Optimization

View resolution is performed during binding. At this stage the view reference is replaced with its definition. At this point, unused view columns will be present.

The next stage is optimization, where the bound syntax tree is transformed into an execution plan. The optimizer considers many kinds of manipulations on the execution plan to increase efficiency, and removing unused columns is one of the most basic. At this point, the unused column references will be removed.

So to answer your question, unused columns in the view definition will not impact performance, since the optimizer will be smart enough to remove them.

Note: this answer assumes the view is not indexed. For indexed views, the resolution process works differently, and there is view maintenance overhead for UPDATEs of the base tables.

Upvotes: 2

Ricardo C
Ricardo C

Reputation: 2244

None of the above. SQL Server will parse the query and it will create and execution plan. The resulting execution plan is calculated based on many factors, like indexes joins, etc.

Your question cannot be truly answered by anyone other than you, examining such execution plan.

See How do I obtain a Query Execution Plan? for more information.

Upvotes: 1

Related Questions