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".