Click here to Skip to main content
14,838,477 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Friends,

I have a prob mentioned below:

There are three Tables Let A,B and C

I create pagination on a procedure (above 3 tables in it used)
now there data have been very huge .
so i want to paginate with parameter PageIndex and PageSize,

Now problem is that i have to count all rows of result set suppose there are 1 Lac records then time taken in it by count() function is similar as full query execution ,

It there any way to count result set rows with TOP 10(or n) record and also total rows of result set

Please suggest me

Thanks in Advance

Shreeniwas Kushwah

RV Solutions Noida (UP)
gvprabu 21-Feb-13 12:08pm
Hi Shreeniwas,

U need to do this though SP like U will pass 2 Parameter as @eStartPoint and @EndPoint. If table have 100000 Records then if You will pass 2000 and 2500 means SP will return 2000 to 2500 only 500 records from 100000 Records.

So Explain the Requirement little clear.
SriNivas IT 22-Feb-13 1:38am
Hi GVPrabu,

I want to return 100000 (in rows affected message or any medium) not 500 rows


Arun1_m1 22-Feb-13 8:38am
Can you post the query that you are using?
SriNivas IT 23-Feb-13 6:22am
Actually Arun you may understand by query situation as below:

Let A is a table which has 100000 or more records and i fire query as

select top 400 col1 from A

then affected rows are as 400

but i want to find total scanned rows which are defenately 100000

can it possible????

use this query may be it helps u
select  top(3 or n) * ,(select count(*) from table_name) as COUNT from table_name

but remember TOP keyword must write first and then other columns separated by comma.
here COUNT (alias name) will return total rows in your table


Try like this also... you can find the table record count using system table data

SELECT  TOP (3 or n) * , T.row_count
FROM table_name M
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
AND OBJECT_NAME(OBJECT_ID)='Table_Name') T ON T.TableName='Table_Name'


This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900