MS SQL Server - Working Mechanism of Top clause 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&gt;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]
As well as the documentation MBuschi pointed to, Bren Ozars free &quot;how to think like the SQL engine&quot; 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 &gt;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 &quot;Worktable&quot;.