Flexibility using TOP clause in SQL Server 2005

Hello all,

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 INT
SET @Rows = 10

SELECT TOP ( @Rows ) *
FROM TempMaster

This will return the top 10 rows from TempMaster. You can also replace @Rows with anything that evaluates to a number.

Now look at the following query; its odd but runs just fine:

SELECT TOP ( SELECT COUNT(*) FROM TempMaster ) *
FROM TempDetails

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.