|
In SQL Server 2000, we can able use the Top Clause only with Numeric Constants. That is, it does not allow entering the variables.
SQL 2005 Top Clause allows us to do with the Variable that is very much useful.
a) User can specify an expression as an input to the TOP keyword.
b) User can use TOP in modification statements (INSERT, UPDATE, and DELETE).
DECLARE @Rows INT
SET @Rows = 10
SELECT TOP (@Rows) * FROM demo.Orders
Inside the TOP Clause, you can use the Query also, which returns a numeric.
SELECT TOP(SELECT COUNT(*) FROM Sales.Customer) * FROM demo.Orders
Top Keyword provides the option to insert / update / delete only 'n' records even the select set more than Values.
SQL Server 2000:
SET ROWCOUNT 5
UPDATE demo.tbStevePurchase SET OrderQty = 10
SQL Server 2005:
UPDATE TOP(5) demo.tblStevePurchase SET OrderQty = 10
INSERT TOP(5) demo.tblStevePurchase
SELECT
LineNumber,
OrderQty
FROM
Purchasing.PurchaseOrderDetail
DELETE TOP(2) demo.tblStevePurchase
So, Thanks to SQL 2005, to provide the TOP feature against the variables, and functions.
|
