SELECT in VIEW generates subquery?

Navigation
3

I would like to know if I make a VIEW with a simple query, if the call of the view generates a new SELECT, that is, a sub-SELECT totalizing 2 SELECTS or if it only points to the SELECT from within VIEW ?

I only executed SELECT from within VIEW and executed VIEW , even with some variations and the execution plan generated only SELECT .

I would like to better understand how the execution of SELECT is executed in relation to the same execution through a VIEW ...

Does the execution plan optimize the query in these cases?

Basic structure of tests: 

CREATE VIEW teste AS
SELECT * FROM Produtos WHERE estoque = 1; 

SELECT * FROM Produtos WHERE estoque = 1; 

SELECT * FROM teste

SELECT nome FROM teste

SELECT nome + 'produto' FROM teste

When I make a SELECT in a VIEW (which already contains a SELECT in):

  • Only a SELECT is executed? (Which is within VIEW )
  • Or SELECT of VIEW plus internal query, generating two SELECTs ?

In my understanding, the database only makes a link from SELECT from VIEW direct to SELECT % from VIEW , since VIEW is not a table, is this correct? p>

I'm currently using SQL Server

    
asked by anonymous 27.07.2017 / 20:07

1 answer

3

As stated in the BOL documentation regarding unindexed views, "(...) a view does not exist as a stored set of data values in a database. Data rows and columns come from referenced tables in queries that define the view and are dynamically produced when the view is referenced. " That is, it is a virtual table defined by a query and used as if it were a table.

When you create the view, you specify the name and the code of the query. For non-indexed views, SQL Server then stores only display metadata, such as object description, columns, security, dependencies, and so on. When an unindexed view is called, the query processor overrides the view reference by its definition; that is, it expands the view definition and then generates the execution plan.

As an example, we have table of products. 

-- código #1
CREATE TABLE Produto (
  Código int primary key,
  Seção char(1) not null,
  Descrição varchar(80) not null,
  Fornecedor varchar(30) not null
); 
go

We can create a view that returns only the products in the apparel section. 

-- código #2
CREATE VIEW dbo.Produto_Vestuário 
     with SCHEMABINDING as 
     SELECT Código, Descrição, Fornecedor
       from dbo.Produto
       where Seção = 'V';
go

Now, to list the products of the clothing section we have the following code: 

-- código #3
SELECT Código, Descrição, Fornecedor
  from Produto_Vestuário;

To list garment items from a single vendor, we have 

-- código #4
SELECT Código, Descrição
  from Produto_Vestuário
  where Fornecedor = 'ITA';

What does the query processor do when parsing code # 4? Initially it expands the display code in the call code: 

-- código #5
SELECT Código, Descrição
  from (SELECT Código, Descrição, Fornecedor
          from Produto
          where Seção = 'V') as PV
  where Fornecedor = 'ITA';

And then the query optimizer and execution plan generation goes into action.

For code # 4 at the end it is generated something similar to 

-- código #6
SELECT Código, Descrição
  from Produto
  where Seção = 'V'
        and Fornecedor = @1;

This can be confirmed by parsing the generated predicate for code # 4: 

#4: [Produto].[Fornecedor]=[@1] AND [Produto].[Seção]='V'

Some applications

  • security mechanism to allow users to access data by half of the display, without granting them permissions to access directly the underlying base tables of the display;
  • provide backward compatible interface to emulate a table that exists, but whose schema has changed;
  • develop modular solutions. You can solve each step of the problem with a query, then creating a view based on that query. Query. This process can simplify the solution by focusing on one step at a time.

References

30.07.2017 / 01:54
create sequence that increases and decreases monotonically SQL Server query error