TOP clause in SQL Server 2005/2008

TOP was introduced in SQL Server 7. Until SQL Server 2005, the TOP clause allowed the user to specify the number or percent of rows to be returned in a SELECT statement. In SQL Server 2005, the TOP clause can be used also for INSERT, UPDATE, and DELETE (in addition to SELECT), and the syntax is as follows: TOP (expression) [PERCENT]. Notice the parentheses around the expression; this is required when TOP is used for UPDATE, INSERT, and DELETE.


The following code shows some examples of using TOP.
—create a table and insert some data
CREATE TABLE #tmpTopSample (Name VARCHAR(150))
INSERT INTO #tmpTopSample (Name)  VALUES('Harpreet1'),('Harpreet2'),('Harpreet3'),('Harpreet4'),('Harpreet5')

TOP with SELECT statement: 
SELECT TOP(2) * FROM toptest

The above query will return 'Harpreet1' and 'Harpreet2'

TOP with UPDATE statement:
UPDATE TOP(2) #tmpTopSample SET Name= 'hi'

The above query will update 'Harpreet1' and 'Harpreet2' to 'hi'

TOP with DELETE statement:

DELETE TOP(2)  #tmpTopSample


The above query will delete top two rows.


TOP with INSERT statement:

CREATE TABLE #tmpTopSample1 (Name VARCHAR(150))
INSERT INTO #tmpTopSample (Name)  VALUES('Harpreet1'),('Harpreet2')

INSERT top(2) #tmpTopSample

SELECT * FROM #tmpTopSample1

The above query will insert top two rows from #tmpTopSample1 table to #tmpTopSample table.

In SQL 2005/2008 we can use TOP clause with expressions for number definition. See below example for the reference.

DECLARE @a INT,
@b INT,
@c INT;
--set values
SET @a = 10
SET @b=5 
SELECT  @c=@a/@b


Select TOP(@c) * from #tmpTopSample

The above query will return 2 rows from #tmpTopSample table.