Rowcount from query before returning results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Rowcount from query before returning results

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-12 : 08:42:11

manray writes "I need to know the count of how many rows a query is going to return without actully bringing back all the data to my pc. I know I can use select count(*) to find how many rows are in a table, but I need to know how many rows are going to be returned in a complicated query that has joined multiple tables without actually running the query and returning the total results because it will be around one million and many columns. I realize I have to run it to get the number of rows it will return but I don't want all the columns and rows to come
back as a result. Just the count. For example a query might be:

select one, two, three, four, five
from table1, table2, table3,
where etc, etc, etc.

I want the count of how many rows will be returned from the query, nota single table. I don't have sqlserver enterprise edition to use the analyzer, I just thought there might be a way of including this whole query between parenthises with some way to bring back just the count. Any ideas?"

szgldt
Starting Member

10 Posts

Posted - 2006-01-12 : 09:26:25

As far as I am aware you must run a query to get the row count. However you do not need to return all the columns. You can just return the count.

Instead of putting all the column names just put Count(*).

For example:

SELECT Count(*)
FROM table1,
INNER JOIN table2
ON etc etc

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2006-01-12 : 20:32:03

SELECT COUNT(*)
FROM
(

YOUR QUERY HERE

) sub

This will be less burdensome than running the actual query, and should give you the results you want.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-13 : 03:18:03

You cant get rowcount without actually running the query with Joins. As suggested only select count(*)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-13 : 06:26:17

If you are going to do the query anyway, but return an initial resultset with the number of rows, you could "minimise CPU overhead" by doing:

INSERT INTO #MyTempTable
SELECT ... list of PK columns
FROM ... table list ...
WHERE ... complex stuff here ...

SELECT [RowCount] = @@ROWCOUNT

SELECT ... list of display columns ...
FROM #MyTempTable
JOIN ... tables required for Display Columns Only ...
ORDER BY ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources