SQL Server TOP clause is used to select set of rows that at the top of the result set. In the beginning, top clause was only used with the literal number.
SELECT TOP 5 * FROM Customer
But it has been expanded to use TOP (Expression). So, in place of literal, now we can use expression. Expression will accept, literal number, Scalar value, Scalar Variable, SELECT query which returs a single value.
SELECT TOP(SELECT PageCount FROM Pagination) * FROM Customer
If you are using stored procedure, then you may be able to pass the value from client application and directly use in the SELECT query.
If you have value in a variable, then that variable can also be used with TOP clause.
DECLARE @PageCount int
SET @PageCount = 5
SELECT TOP(@PageCount) * FROM Customer
Apart from this, TOP clause can be used in INSERT, UPDATE and DELETE Query as well.
For Some reason, if you want to update a table based on some condition, but you want to update only Top 5 rows from the result, then you can easily do it with Top Clause
UPDATE TOP(5) Associate
SET Salary = Salary + 200
WHERE Experience BETWEEN (2, 4)
The above query will update only top 5 associates, even though the WHERE condition returns more than that.
If you need to copy top 10 record from a table and insert into a temp table, then you can do as below
INSERT TOP(10) INTO #CustomerTemp
SELECT * FROM Customer
WHERE Age > 30
The same way you can use it in DELETE statement as well.
DELETE TOP(5) Customer
WHERE EndDate < '06/172013'
