Examples of TOP syntax

This article is just a short summary of TOP clause variants we can use. Especially the last one with a subquery isn’t so common and can be something new for you. Consider official documentation for more details on TOP usage and if you have any other interesting example don’t hesitate to put it in comments.

-- Using constant value
SELECT TOP 10 * -- (10)
FROM sys.messages
GO

-- Using percents
SELECT TOP 10 PERCENT * -- (10) PERCENT
FROM sys.messages
GO

-- Using variable
DECLARE @Cnt INT
SET @Cnt = 10

SELECT TOP (@Cnt) * FROM sys.messages
SELECT TOP (@Cnt) PERCENT * FROM sys.messages
GO

-- Using subquery
SELECT TOP (SELECT COUNT(*)/10 FROM sys.messages) *
FROM sys.messages