My answer will focus almost exclusively on SQL Server just because I'm going to give a fairly detailed answer and I don't have the same level of expertise in other platforms.
First it's important to realize that the query optimizer doesn't directly work against the SQL you write. It gets transformed into an internal format before optimization. What you have listed for potential query 1 and query 2 is pretty much the same thing, except for a view subtle differences. A similar question about the difference between query 1 and query 2 was asked and answered here. If you'd like to learn more about the internal format SQL Server uses, you can go through an excellent series of blog posts by Paul White. However, most of the time it's sufficient to just compare the query plans of two queries that you suspect might be optimized in the same way.
There are a few ways that using a view might improve performance:
It's possible to define views which are implemented as physical structures on the database. In SQL Server these are called indexed views. In Oracle these are called materialized views. In Oracle it's possible for a query not written against a materialized view to still use the materialized view. Further discussion is outside the scope of this answer.
Sometimes the same SQL query needs to run on multiple RDBMS platforms. With a view we can use syntax that is unique to each platform but the same query is sent to the databases. Without a view we might have to use user defined functions which can be bad for performance.
Sometimes people put really clever code in views. If it's better than what you would have written you can improve performance by using the view.
In general, a query written against a view will be as efficient or less efficient than a query written directly against the base tables. This is because a view definition often contains extra columns and joins that might not be needed for the specific question a query against the view is asking. To get good performance against a complex view we're hoping that three things happen:
Column elimination. If a column is presented in a view but not mentioned in the query against the view then the value shouldn't be calculated.
Join elimination. If a table is used in a view that could be safely eliminated without changing the results then it should be eliminated. Sometimes this could happen if the optimizer had more information. For example a foreign key might not be declared in the database. Other times the rule to implement join elimination might not cover a certain scenario. For example, in Oracle join elimination cannot happen for a multi-column join but it can in SQL Server.
Predicate pushdown. If I add a filter to the view and there's an index on the underlying column then I should be able to use that index. I believe that this is what your example is hinting at. Even without an index I still want filters to be pushed down as far as possible into the plan to avoid unnecessary work.
In my experience these rules are implemented pretty well by the query optimizer which is of course a good thing, but it can be bad for SE demos. However, if we write sneaky code we can end up with examples that show all of the above optimizations failing. This is because the rules that implement the optimizations are not designed to cover every possible scenario.
First I'll create some simple sample data. The data itself isn't that important, but the table definitions are.
DROP TABLE IF EXISTS dbo.BASE_TABLE;
CREATE TABLE dbo.BASE_TABLE (
ID INT NOT NULL,
ID2 INT NOT NULL,
FILLER VARCHAR(50),
CONSTRAINT BASE_TABLE_ID CHECK (ID > 0),
PRIMARY KEY (ID)
);
INSERT INTO dbo.BASE_TABLE WITH (TABLOCK)
SELECT TOP (1000000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('Z', 50)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;
DROP TABLE IF EXISTS dbo.EXTRA_TABLE;
CREATE TABLE dbo.EXTRA_TABLE (
ID INT NOT NULL,
ID2 INT NOT NULL,
FILLER VARCHAR(50),
PRIMARY KEY (ID, ID2)
);
INSERT INTO dbo.EXTRA_TABLE WITH (TABLOCK)
SELECT TOP (1000000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('Z', 50)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;
DROP TABLE IF EXISTS dbo.EMPTY_TABLE;
CREATE TABLE dbo.EMPTY_TABLE(
ID INT NOT NULL,
PRIMARY KEY (ID)
);
DROP TABLE IF EXISTS dbo.EMPTY_CCI;
CREATE TABLE dbo.EMPTY_CCI (
ID INT NOT NULL
, INDEX CCI_EMPTY_CCI CLUSTERED COLUMNSTORE
);
GO
CREATE FUNCTION dbo.THE_BEST_FUNCTION () RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
RETURN NULL;
END;
GO
Here is my sneaky view definition:
CREATE VIEW dbo.SNEAKY_VIEW
AS
SELECT
ABS(t.ID) ID
, COUNT(*) OVER (PARTITION BY ABS(t.ID)) CNT
, dbo.THE_BEST_FUNCTION() FUNCTION_VALUE
FROM dbo.BASE_TABLE t
LEFT OUTER JOIN dbo.EXTRA_TABLE e ON CAST(t.ID AS VARCHAR(10)) = CAST(e.ID AS VARCHAR(10)) + '|' + CAST(e.ID2 AS VARCHAR(10))
LEFT OUTER JOIN dbo.EMPTY_CCI ON 1 = 0
LEFT OUTER JOIN dbo.EMPTY_TABLE e2 ON t.ID = e2.ID;
GO
The view is unfortunately a mess. As a human optimizer it's possible to simplify that query quite a bit. The join against EXTRA_TABLE can be eliminated because we're joining against the full primary key so the number of rows won't change. It's also not possible for any rows to match, but it's an outer join so nothing will be eliminated. The join against EMPTY_CCI can be eliminated because the join condition will never match. The join against EMPTY_TABLE can be eliminated because we're joining against the full primary key. The function also always returns NULL so there's no need to include that. So we can simplify to this:
SELECT
ID
, 1 CNT
, NULL
FROM dbo.BASE_TABLE t;
However, we can do even better than that. ID is always positive due to the constraint and ID is always unique because it's the primary key. So the COUNT window function will always be 1. The query can be rewritten like this:
SELECT
ID
, 1 CNT
, NULL
FROM dbo.BASE_TABLE t;
Will the query optimizer be able to simplify a query against the view to that? Let's find out by comparing the plans. Here's the plan for the simple query:
Here's the plan for SELECT * against the view:
They're quite a bit different. Let's go through more example queries to see examples of the optimizations above perhaps not working out exactly as expected.
First, scalar user-defined functions are bad for performance in SQL Server. Among other issues they force the entire query to have a serial plan. This query is eligible for a parallel plan and I get one on my machine:
SELECT *
FROM (
SELECT
ABS(t.ID) ID
, COUNT(*) OVER (PARTITION BY ABS(t.ID)) CNT
FROM dbo.BASE_TABLE t
LEFT OUTER JOIN dbo.EXTRA_TABLE e ON CAST(t.ID AS VARCHAR(10)) = CAST(e.ID AS VARCHAR(10)) + '|' + CAST(e.ID2 AS VARCHAR(10))
LEFT OUTER JOIN dbo.EMPTY_CCI ON 1 = 0
LEFT OUTER JOIN dbo.EMPTY_TABLE e2 ON t.ID = e2.ID
) derived_table;
The plan:
However, even if I don't select the column based on the user defined function in the view I still get a forced serial plan:
SELECT ID, CNT
FROM dbo.SNEAKY_VIEW;
So there's one difference between using a view and a derived table. Sometimes column elimination won't work in the same way.
For the second example, the EMPTY_CCI table won't affect the results of the query so let's get remove it from the derived table:
SELECT *
FROM (
SELECT
ABS(t.ID) ID
, COUNT(*) OVER (PARTITION BY ABS(t.ID)) CNT
FROM dbo.BASE_TABLE t
LEFT OUTER JOIN dbo.EXTRA_TABLE e ON CAST(t.ID AS VARCHAR(10)) = CAST(e.ID AS VARCHAR(10)) + '|' + CAST(e.ID2 AS VARCHAR(10))
LEFT OUTER JOIN dbo.EMPTY_TABLE e2 ON t.ID = e2.ID
) derived_table;
Here is the query plan:
However, there's a difference in the query plan against the view:
SELECT ID, CNT, FUNCTION_VALUE
FROM dbo.SNEAKY_VIEW;
The view is able to use batch mode which is a special way of implementing query execution when clustered columnstore indexes are involved in a query. Even though the EMPTY_CCI table doesn't show up in the plan the query is still eligible for batch mode. Note that EXTRA_TABLE is needlessly queried in both queries. That's because the join condition was too complicated for SQL Server to determine that the join was safe to elimination. Also note that the EMPTY_TABLE table does not show up in either query plan. The query optimizer was able to eliminate it in both queries.
For the third example, let's look at predicate pushdown. Let's suppose that I want to filter to just include rows where ID = 500000. If I do that somewhat directly outside of the view:
SELECT
ABS(t.ID) ID
, COUNT(*) OVER (PARTITION BY ABS(t.ID)) CNT
FROM dbo.BASE_TABLE t
LEFT OUTER JOIN dbo.EXTRA_TABLE e ON CAST(t.ID AS VARCHAR(10)) = CAST(e.ID AS VARCHAR(10)) + '|' + CAST(e.ID2 AS VARCHAR(10))
WHERE ABS(t.ID) = 500000
OPTION (MAXDOP 1);
I cannot use the index against BASE_TABLE.ID due to the ABS() function. However, the filter is pushed down into the scan and only one row is expected to be returned. That can improve performance:
With this query:
SELECT ID, CNT
FROM dbo.SNEAKY_VIEW
WHERE ID = 500000;
We get a less efficient plan. The filter on ID = 500000 is implemented at the very end of the plan, so the window function will be evaluated against almost a million unnecessary rows:
That may have been a bit more of a deep dive than what you were looking for. Going back to your original question, I would say that the query against the view is most similar to potential query 1. I have heard rumors regarding some cases in which that isn't true. For example, supposedly if you have many nested views then the query optimizer can have difficulty unpacking them and you can end up with a poorly optimized query for that reason alone. I don't know if that's true in a current version of SQL Server but it's worth avoiding simply so that other people trying to understand your code have an easier time with it.
