It's interesting to work with sql Server 2005

I imported Some of my sql server 2000 Databases In to new Sql server 2005.i saw something new in sql TOP Key word.The TOP operator has been around since SQL Server 7.0( i think so.), in the past it only accepted a constant as the number of rows to return or as the percentage of the rows to return. The TOP statement in SQL Server 7.0 and SQL Server 2000 was also limited to SELECT statements. Thanks to improvements in the TOP operator for SQL Server 2005, the TOP statement can now accept variables and subqueries for the number of rows to return or the percentage of the rows to return. The TOP operator has also been enhanced so that it can be used with INSERT, UPDATE, and DELETE statements in addition to the SELECT statement.

DECLARE @intRows INTEGER;
DECLARE @startAfter INTEGER;
SET @intRows = 12 ;
SET @startAfter = 10;
SELECT TOP (@intRows) * FROM HumanResources.Employee
WHERE EmployeeID > @startAfter
ORDER BY EmployeeID;
GO

And we Can set Varible Values programicaly.

We can still use the PERCENT keyword to return a percentage of rows instead of a fixed number of rows. And sql server 2005 we can use it with Top keyword with a variable value.

DECLARE @intPercRows INTEGER
DECLARE @intPage INTEGER
SET @intPercRows = 12
SET @intPage = 10
SELECT TOP (@intPercRows) PERCENT * FROM HumanResources.Employee
WHERE EmployeeID > @intPage
ORDER BY EmployeeID;
GO


In addition to passing an expression, we can also use a subquery to satisfy the TOP statement value requirement.

DECLARE @startAfter INTEGER;
SET @startAfter = 0;
SELECT TOP (SELECT COUNT(*)/11 FROM HumanResources.Employee) *
FROM HumanResources.Employee
WHERE EmployeeID > @startAfter
ORDER BY EmployeeID;
GO