The Microsoft has made some significant improvements for some of the existing features of SQL server 2000 in SQL Server 2005.
One of the most important enhancements was done for the ‘TOP ‘Clause. The previous versions of SQL server accept only ‘Constant value’ for the ‘TOP’ clause, but in SQL Server 2005, we can pass any numeric expression.
Another important enhancement is, now, we can use ‘TOP’ even for Delete, Update and Insert statements.
Syntax: TOP (Expression) [PERCENT] [WITH TIES]
Expression can be any valid arithmetic expression and this specifies the no of rows affected or to be returned.
[PERCENT] Indicates that the query returns only the first expression percent of rows from the result set.
[WITH TIES] specifies, additional rows to be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP ...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.
Example1 :
USE AdventureWorks
DECLARE @n INT;
SET @n = 10 ;
SELECT TOP ( @n ) [AddressID], [AddressLine1],[City]
FROM Person.Address
Example2: TOP Clause WITH TIES
USE AdventureWorks
DECLARE @n INT;
SET @n = 10 ;
SELECT TOP ( @n ) WITH TIES [AddressID],[AddressLine1], [City]
FROM Person.Address
ORDER BY [City]
Example3: Use of ‘TOP’ while updating a table
DECLARE @updateTopTest table (
i int identity,
value int
)
INSERT INTO @updateTopTest
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6
SELECT * FROM @updateTopTest
--update top 3 rows
UPDATE TOP (3) @updateTopTest -– Top requires parenthesis for UPDATE
SET Value = Value * 10
SELECT TOP 5 * -- parenthesis is not mandatoty for SELECT
FROM @UpdateTopTest
Note: You should enclose the ‘expression’ in a parenthesis, in case you are using TOP clause with all operations other than SELECT.
Example4: TOP with DELETE
DECLARE @updateTopTest table (
i int identity,
value int
)
INSERT INTO @updateTopTest
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6
SELECT * FROM @updateTopTest
--delete first row
DELETE TOP (1)
FROM @updateTopTest
SELECT *
FROM @UpdateTopTest
