CODEWITHSUNDEEP
phpsql-serversql-server-2008sqlsrv
gergalyb
gergalyb

Reputation: 99

PHP mssql count rows in a statement

I would like to count the number of rows in a statement returned by a query. The only solutions I found were:

  1. sqlsrv_num_rows() This one seems a bit too complicated for such a simple task like this and I read that using this slows down the execution quite a bit
  2. Executing a query with SELECT COUNT This method seems unnecessary, also it slows down the execution and if you already have a statement why bother with another query.
  3. Counting the rows while generating a table As I have to generate a html table from the statemnt I could put a variable in the table generating loop and increment it by one, but this one only works when you already have to loop through the entire statement.

Am I missing some fundamental function and/or knowledge or is there no simpler way?

Any help or guidance is appreciated.

EDIT: The statement returned is only a small portion of the original table so it wouldn't be practical to execute another query for this purpose.

Upvotes: 1

Views: 756

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93734

In sql server table rows information is stored in the catalog views and Dynamic Management Views you can use it to find the count

This method will only work for the physical tables. So you can store the records in one temp table and drop it later

SELECT Sum(p.rows) 
FROM   sys.partitions AS p 
       INNER JOIN sys.tables AS t 
               ON p.[object_id] = t.[object_id] 
       INNER JOIN sys.schemas AS s 
               ON t.[schema_id] = s.[schema_id] 
WHERE  p.index_id IN ( 0, 1 ) -- heap or clustered index 
       AND t.NAME = N'tablename' 
       AND s.NAME = N'dbo';

For more info check this article

If you don't want to execute another query then use select @@rowcount after the query. It will get the count of rows returned by previous select query

select * from query_you_want_to_find_count 
select @@rowcount

Upvotes: 1

Related Questions