Top Clause

DROP TABLE TEMP_UPDATE

CREATE TABLE TEMP_UPDATE
(
SNO INT IDENTITY(1,1),
EMP_NAME VARCHAR(20),
SALARY MONEY
)

INSERT INTO TEMP_UPDATE
SELECT 'VICKY',0 UNION SELECT 'ANAND',0 UNION SELECT 'BS',0 UNION SELECT 'AS',0 UNION SELECT 'KC',0

SELECT  * FROM TEMP_UPDATE

/*Use Top Clause in Select Statement*/
SELECT TOP 2 * FROM TEMP_UPDATE

/*Use Top Clause in Update Statement*/
UPDATE TOP (2) TEMP_UPDATE
SET SALARY = 1000

SELECT  * FROM TEMP_UPDATE

/*Use Top Clause in Insert Statement*/
INSERT TOP (2) INTO TEMP_UPDATE (EMP_NAME,SALARY)
SELECT  EMP_NAME,SALARY FROM TEMP_UPDATE

SELECT  * FROM TEMP_UPDATE

/*Use Top Clause in Delete Statement*/

DELETE TOP (2) FROM TEMP_UPDATE

SELECT  * FROM TEMP_UPDATE


/*We can Use parameter in Top Clause*/

DECLARE @topvalue INT
SET @topvalue = 2
SELECT  TOP (@topvalue) *  FROM TEMP_UPDATE

/*We can use PERCENT to list particular % of table rows*/
SELECT  * FROM TEMP_UPDATE
--5 rows

SELECT  TOP (50) PERCENT * FROM TEMP_UPDATE
--3 rows
SELECT  TOP (20) PERCENT * FROM TEMP_UPDATE
--1 row
SELECT  TOP (25) PERCENT * FROM TEMP_UPDATE
--2 row


/*To Avoid incorrect result. always use ORDER BY clause with TOP operator*/