MS SQL Server - Working Mechanism of Top clause

Question

Intro

This question is basically asked to understand how the TOP clause works in SQL.

Example

There is a table Table1 in which there are 10 Million records. If I fire the following query SELECT Top 10 Id from Table1 where Id>0 order by Id it returns the first Top 10 Id in the table.

Question

How the data is actually retrieved?

Assumption 1: Does it get all the 10 Million records from the database and searches the Top 10 records from it? [More CPU, RAM intensive]

OR

Assumption 2: Does it directly searches for Top 10 records based on the where clause on the table and returns only those 10 records? [Less CPU, RAM intensive]

Answer 1

As well as the documentation MBuschi pointed to, Bren Ozars free "how to think like the SQL engine" is a good resource as you start to dive into how SQL Server works in more depth to help understand how to optimise things. Also Use The Index, Luke is an excellent free resource, and not just for MS SQL Server.

To answer your question specifically:

If there is an index on Id, which is likely as it is probably the table's primary key, SQL server can use that for both the ORDER BY and WHERE clauses: it will use the index to seek to the first instance of Id being >0 only needing to read a couple of index pages to do so, then it will walk down the index to pick off the next 9 rows too. As you are only asking for Id to be output, it doesn't need to look for any more information after that.

If there is no index on Id then it has to work harder. It will scan the table from the beginning until it finds ten rows that match the where clause (which could be the first ten it reads, or it could have to scan all the way to the end, if there are not 10 there for it to find then it will have to scan all the way to the end). Once it has found those rows it will sort them before output. For such a small number this will be done in memory, but if you'd asked for a lot of rows it would have to spill the data to disk to perform the sort. In terms of complexity you can think of it as essentially creating a temporary table with an index on Id and put the matching rows into that to sort them then reading them back out - in fact if you look at the IO stats (add SET STATISTICS IO ON before your query, or click the relevant toolbar button in SSMS) you'll see this internal entity referenced as "Worktable".

Answer 2

Plase, check out the documentation for the SELECT statement,

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

in particular this section:

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#logical-processing-order-of-the-select-statement

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. The actual physical execution of the statement is determined by the query processor and the order may vary from this list.

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

Answer 3

To add a little bit to MBuschi's answer:

The logical order defines how the language work. But as stated and emphasized in the text, SQL server is free to do it any way it want, as long as it returns data as if it executed as per the logical order. This later part is what we see in the execution plan.

So, for instance, SQL Server can use an index on the Id column (if such exist) to find the "last row" (based on your WHERE clause) and traverse this index backwards to find your rows - which in that case would be an extremely cheap operation.