Upto now we know that how to use TOP clause in Sql Server 2000 . SQL Server 2005 come up with more flexible way to use TOP clause.
Here is the simple way to get TOP 10 [or say 'n' a dynamic number] from a table.
DECLARE @Rows INTThis will return the top 10 rows from TempMaster. You can also replace @Rows with anything that evaluates to a number.
SET @Rows = 10
SELECT TOP ( @Rows ) *
Now look at the following query; its odd but runs just fine:
You can also use the TOP clause for INSERT, UPDATE and DELETE statements. If you wanted to DELETE in batches of 500 you can now do that using the TOP clause.
SELECT TOP ( SELECT COUNT(*) FROM TempMaster ) *