get the rowcount before issuing statement
-
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.
-
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
-
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.
-
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] -
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 -
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] -
Shure.
The rowcnt column is valid for the entire table.
For a query with a where clause only the count(*) can work
Bye
Gabor
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply