How to count total rows of result set with TOP keyword in SQL Server?

Question

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)

Posted 21-Feb-13 0:50am

SriNivas IT

Add a Solution

Comments

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.
Regards,
GVPrabu.

SriNivas IT 22-Feb-13 1:38am

Hi GVPrabu,

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

Thanks

Shreeniwas

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????

2 solutions

Solution 1

use this query may be it helps u

SQL

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

BY BKTHEBEST

Posted 22-Feb-13 20:38pm

bkthebest

Updated 22-Feb-13 20:43pm

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

gvprabu · Accepted Answer · 2013-02-23T01:21:00

Hi,

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

SQL

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

Regards,
GVPrabu