SQL Server 2005 introduces two enhancements to the TOP clause.
1) User can specify an expression as an input to the TOP keyword.
2) User can use TOP in modification statements (INSERT, UPDATE, and DELETE).
Explanation : User can specify an expression as an input to the TOP keyword.
In SQL SERVER 2000 usage of TOP is implemented in following query.
SELECT TOP 10 TableColumnID FROM TableName
Â
For ages Developers and DBAs wants to pass parameters to TOP keyword. IN SQL SERVER 2005 it is possible. Example, @iNum is variables set before SELECT statement is ran.
DECLARE @iNum INT SET @iNum = 10 SELECT TOP (@iNum) TableColumnID FROM TableName
This code will run fine in SQL SERVER 2005. Now it is possible to pass variables through Stored Procedures and T-SQL Code. Not only variables but we can run sub-query as a parameters of the TOP. Example, following query will return TOP 1/4 rows from table TableName.
SELECT TOP ( SELECT CAST(( SELECT COUNT(*)/4 FROM TableName) AS INT)) * FROM TableName
Explanation : User can use TOP in modification statements (INSERT, UPDATE, and DELETE).
In SQL SERVER 2000 usage of the limited row modification was implemented using SET ROWSET command. Example,
SET ROWCOUNT 5 UPDATE TableName SET TableColumn = 14 SET ROWCOUNT 0
In SQL SERVER 2005 limited row modification can be achieved by TOP keyword function. Example,
UPDATE TOP(5) TableName SET TableColumn = 14
Â
Reference : Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
super
really enjoyed a lot
Useful Information. Thanks.
good information
If there are duplicate records in the table. For example:
There are 3 records, in which 2 records are same. If I will query like ‘select top 2 from table_name’ then what will be…
The result is random until you use ORDER BY Clause
when i run the below command , shows 201 row affected
but when i refresh the database don’t changed on it(database)
update dbo.data
set id = floor(id) or round(id,0)
best regards