SELECT in VIEW generates sub-query?

Question

I wonder if I make one VIEW with a simple query, whether the view call generates a new SELECT, ie a sub-SELECT totaling 2 SELECTS or whether it only points to SELECT from within the VIEW?

I only ran SELECT from within the VIEW and executed the VIEW, including some variations and the implementation plan generated only one SELECT.

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

The implementation plan shall optimise consultation in such cases?

Basic structure of the 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 within):

• Only one SELECT is executed? (What is inside the VIEW)
• Or the SELECT of VIEW plus the internal consultation, generating two SELECTs?

In my understanding the database only makes a link to SELECT of VIEW straight to the SELECT internal to the VIEW, for the VIEW is not a table, this is correct?

I am currently using SQL Server

Answer 1

As stated in the BOL documentation regarding exhibitions (views) not indexed, "(...) a display does not exist as a stored set of data values in a database. The rows and columns of the data come from tables referred to in queries that define the display and are produced, dynamically, when the display is referenced".

When creating the view, the name and code of the query are specified. For non-indexed views SQL Server then stores only display metadata, such as object description, columns, security, dependencies etc.When calling an unendexed view, the query processor replaces the display reference by its definition; that is, expands the display definition and after generates the execution plan.

As an example, we have product table.

-- 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 display that returns only the products from the garment 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 clothing items from a single supplier, 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 into 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 comes into action the query optimizer and the generation of the execution plan.

For code #4 at the end is generated so 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 means of display, without granting them permission to access directly the underlying underlying display tables;
• provide interface compatible with previous versions to emulate a table that exists but whose scheme has been changed;
• develop solutions in a modular way. Each step of the problem with a query, then creating display based on that query. This process can simplify the solution allowing focus one step at a time.

References

• Exhibitions
• Ben-Gan, Itzik. Chapter 1 - Views. In:T-SQL Programming.