Best way to use SQL Server TOP clause in queries? Tips and tricks?

0 votes
asked Jun 17, 2013 in SQL Server by Aadhira (1,213 points)

What is the use of SQL Server TOP keryword and how to use it in SELECT, INSERT, UPDATE and DELETE Statement.

Share

1 Answer

+1 vote
answered Jun 17, 2013 by anonymous
selected Jun 18, 2013 by administrator
 
Best answer

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'

Your answer

Preview

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
Anti-spam verification:
To avoid this verification in future, please log in or register.
site design / logo / content © 2013 - 2015 pinfaq.com
...