How to get total number of rows returned

Question

How to get total number of rows returned

gaurangtalwadkar

SSC-Addicted

Points: 437
More actions
May 14, 2010 at 6:56 am

#224514

hi,
I'm executing query from my C# application and I want to calculate selectivity.
Query can be like
select
s_name,
count(*) as numwait
from
supplier,
lineitem l1,
orders,
nation
where
s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and exists (
select
*
from
lineitem l2
where
l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey
)
and not exists (
select
*
from
lineitem l3
where
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
and s_nationkey = n_nationkey
and n_name = '[NATION]'
group by
s_name
order by
numwait desc,
s_name;
In this query I'm executing query with different values of parameter NATION.
I can get EstimateRows for a particular value of NATION from Statistics but how to get total number of rows returned.
And there are many other queries which I'm executing.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if you are doing this from a .NET application, can't you jsut put the results in a DataTable and get the DataTable.Rows.Count?

otherwise i think you can get the "xx rows(s) affected " messages from the SqlConnection.InfoMessage Event,; i've used that to get PRINT statements for a SSMS emulator i played with for a bit.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gaurangtalwadkar SSC-Addicted Points: 437 More actions · Answer 2

I'm getting EstimateRows of the query from Statistics, I dont have to get the datatable.rows.count()

How to get total number of rows when n_name ='[NATION]' is removed.

Lowell SSC Guru Points: 323518 More actions · Answer 3

I'm a little twisted around i guess; you'r not doing this from a C# application like you said, or are you getting the estimated plan in your results to your C# application? that'd be sweet, never did that yet...

if this is just for a review, just rerun the query with a different WHERE statement in SSMS, right?

unless you update statistics, the Estimated plan rows could be inaccurate; if you want the real number of rows, you need to get it from the actual plan.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

douglas.allison-1136597 SSC-Addicted Points: 444 More actions · Answer 4

at the top of your query

declare @rc as int

then as the bottom of the select statement set @rc=@@rowcount

If you have done this right @rc will be populated with the count of the select statement.

Have a look in SQL help for @@rowcount for a better explenation on how best to use it. I use it a lot for knowing what's just happened.