select range of data rows in MSSQL2005

We all know if we have to find range of data then we use sub queries but sql2005 provide row_number to find range of data

for ex.
if we have to select data from 21 row to 30 the n

SELECT * FROM
(
SELECT Row_Number() OVER (ORDER BY last_name, first_name) as rowid,
user_id,
last_name,
first_name
FROM user
) as a
where rowid >20 and rowid <31


same if i want 28 record the n

SELECT * FROM
(
SELECT Row_Number() OVER (ORDER BY last_name, first_name) as rowid,
user_id,
last_name,
first_name
FROM user
) as a
where rowid = 28

@@rowcount with MSSQL

@@ROWCOUNT returns the number of rows affected or read depending on the operation.

terms as follows

• @@ROWCOUNT returns the number of rows returned to the user for a SELECT.
• @@ROWCOUNT returns the number of rows affected after issuing a INSERT, DELETE, or UPDATE.

e.g



SELECT emp_firstname,emp_lastname from emp_details ;


out put is


emp_firstname emp_lastname

yogesh k

Jhon Bush


out put returns two rows

if we execute following after this the n

SELECT @@ROWCOUNT AS 'Rows previously affected';

then out put will be

Rows previously affected
------------------------
2

(1 row(s) affected

But if we execute query like


SELECT Count(*) as rowcount from emp_details
then

out put is

rowcount
-----------
2

(1 row(s) affected)

and we execute following after this
SELECT @@ROWCOUNT AS 'Rows previously affected';


then

out put is

Rows previously affected
------------------------
1

(1 row(s) affected)

• Notice it tells you that it sent back only 1 row to the user.because only one row is return to user