Are the table indexes used in the select on the resultset of a subquery in the FROM clause?

I have a table in the SQL Server with more than 50 millions rows, so I have some compound indexes in my table. Indexes with 3, 4 columns. There is a situation where I really need to use a subquery in the FROM clause.

example of the structure( the index is: column2, column3, column4 ):

select column1 
from
    (select 
         column1, column2, column3, column4 
     from 
         myTable) as MYSUBQUERY
where 
    column2 = 99 and column3 = 999
order by 
    column4

The example is only to illustrate the query in the FROM. My question is generic in relation to the use of table indexes in the results of subquery in the FROM clause and any performance issues.

My question: will the compound indexes of myTable (column2, column3, column4) work in the subquery used in FROM clause? Will work like a regular select direct in the table without lose performance?

Best Regards, Luis

Answer

SQL is a descriptive language, not a declarative language. A SELECT query describes the results to be produced, not how they are produced. The SQL query is transformed into a set of operators, which you can see as the “explain” plan. This set is structured as a directed acyclic graph and is often called a DAG.

The optimizer optimizes this graph of operations, not the original query. In fact, there is no such thing as a “subquery” in the DAG.

So, the subquery is irrelevant in SQL Server. The conditions will be applied to the subquery, if the optimizer can detect that they are used and no other operations affect the results.

Other operations, such as joins, aggregations, and window functions could change the meaning of the query and affect whether or not the index gets used.

But for the simple example in your question, SQL Server, should use the index.