get the rowcount before issuing statement

Question

get the rowcount before issuing statement

Emir Sadikovic

Grasshopper

Points: 24
More actions
April 13, 2005 at 2:36 pm

#92808

I was wondering, is there any way to get the count of rows that would be retrieved after issuing SELECT statement, and depending on that, to issue select [all] ... or select top n .... statement.
Point is, I don't want to issue SELECT COUNT(*) ... or SELECT COUNT(PK_Column) ... just to get stuck with a few milion rows that have to be considerd by SQL Server. In my case, depending on WHERE clause, same statement could return few rows or few milion rows, which I never know.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 1

Maybe I'm misunderstanding the requirements, but if you know you never want to select more than N rows, why not always do a SELECT TOP N for your query?

If there are fewer rows, you get all of them, but you never pull back millions...

Scott Thornburg

Emir Sadikovic Grasshopper Points: 24 More actions · Answer 2

I had a hard time to understand those requirements, too. It's rather specific (probably excentric), but never the less, is there any way to know and use row count in advance.

SQL Srever has estimated row count, when you display estimated execution plan. The question is, can I use that number for further processing, regardless of quality and style of the solution.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

SQL Server bases its guesses on its internal statistical informations. I doubt that you can query them so that they suit your needs. I don't know of a way to get the probably returned COUNT of a query BEFORE actually issuing it, but I might be wrong on this.

I would seriously tell your client to reconsider this requirement. I've seen such questions every now and then and AFAIK the answer was always the same: No chance.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 4

There is a possibility which is not very accurate because of the nature of how SQLServer is maintaining those statistical informations but anyway...

You can check the rowcnt columns of the sysindexes table to get an approximate feeling of the number of rows.

So your query could look like:

if (select rownct from sysindexes where indid < 2 and id = object_id('MyTable')) < 100

select * from MyTable

else

select top 50 from MyTable

To make the rowcnt value up tu date run dbcc updateusage regullary

Bye
Gabor

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Well, this is a good guess and usually correct, although not guaranteed to.

...but the OP wants to include a WHERE clause and rowcnt will not give you the information how many rows will be matched.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 6

Shure.

The rowcnt column is valid for the entire table.

For a query with a where clause only the count(*) can work

Bye
Gabor