How to get total number of rows returned

Question

Post reply

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: 323482 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

gaurangtalwadkar

SSC-Addicted

Points: 437

May 14, 2010 at 7:24 am

#1167519

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: 323482 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 Old Hand Points: 394 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.