Select command returning different column values

Question

I am just starting SQL and am using version SQL server 2016(few days in to learning)

I am using the WideWorldImporters DB from microsoft.

The basics of my question is I don't understand some results I am getting and why? I run this command: use WideWorldImporters select top (2) OrderID From Sales.Orders;

and it returns a table with 2 lines as expected but the OrerID column values for the two rows of 1 and 16

However when I run the command: select top (2) OrderID, CustomerID From Sales.Orders;

I get the additional column for customerID with 1 and 1, and for the ORDERID column it now returns row values of 2934 and 3482.

I don't understand why OrderID would return the two different results. Am I running the command incorrectly?

I am trying to learn the sum and avg aggregate commands on just the top X rows of the column OrderID with the GROUP BY CUSTOMERID for testing, but ultimately want to do below

This is what I was trying to imagine doing: If you imagined that OrderID was in fact an total order price, I was trying to sum up each customers order total from all purchases. So sum up every row with customerid = N and then list all the totals for every customerid. My select top 2 was for testing purposes so it wasn't final results, but led me to discrepancy.

Answer 1

The query you wrote is basically asking the database to give you any two rows of data (any two orders). If you wrote the query with an ORDER BY clause then your results should be the same each time.

SELECT TOP 2 OrderID FROM Sales.Orders ORDER BY OrderID ASC;

This would give you the two rows with the lowest OrderID and you will still get the same two rows even when you add other fields to the SELECT list.

You might be thinking that TOP means that the database will give you the first two, but it actually means that it should stop returning rows after it has returned two rows. And since you didn't have an ORDER BY it could just return whichever two rows are most convenient.

Answer 2

SQL server does it's best to guess which top n rows you are looking for, when no order by is specified. It will use whatever index it thinks is best for the task, usually the primary key of the table. My guess is that since your customer ID was 1,1 there is a nonclustered index on that table that the optimizer decided was the best index for your particular query. Throw an order by on the same query, and you should see your first result set.