SQL SERVER – TOP and DISTINCT – Epic Confusion – SQL in Sixty Seconds #141

Recently during  Comprehensive Database Performance Health Check I was caught in a very interesting scenario where two of the DBAs from the client’s organization ended up an argument about how T-SQL works. Let us discuss that today. The discussion was about the epic confusion about TOP and DISTINCT.

When a query involves TOP and DISTINCT keywords, often the confusion arises that which of the keyword is applied first? TOP or DISTINCT? The reason for the confusion is also valid as when you think of it, either of the keywords when applied first can change the result.

I have discussed this confusion in detail in the video with the help of the demonstration and also provided a clear answer.

Here is the script which is used in the query.

-- Script 1
SELECT StockItemID
FROM [WideWorldImporters].[Sales].[OrderLines]
ORDER BY StockItemID;
-- Script 2
SELECT TOP 10 StockItemID
FROM [WideWorldImporters].[Sales].[OrderLines]
ORDER BY StockItemID;
-- Script 3
SELECT DISTINCT StockItemID
FROM [WideWorldImporters].[Sales].[OrderLines]
ORDER BY StockItemID;
-- Script 4
SELECT DISTINCT TOP 10 StockItemID
FROM [WideWorldImporters].[Sales].[OrderLines]
ORDER BY StockItemID;

If you want to change anything in the query and run your own test, you can easily run that with the script above. If you have any questions, you can also reach out to me via Twitter or LinkedIn. I have provided the link for the same in the header of the blog.

If you like the blog post, you can always follow my YouTube channel where you can see many similar videos.

Reference: Pinal Dave (https://blog.sqlauthority.com)