php sqlsrv_num_rows 不返回任何值

sqlsrv_num_rows Not Returning Any Value

提问by user1067577

I am trying to get the number of rows returned in a query. The while loop looping through the results works, but for some reason the sqlsrv_num_rows does not return any value:

我正在尝试获取查询中返回的行数。循环遍历结果的 while 循环有效，但由于某种原因， sqlsrv_num_rows 不返回任何值：

$result = "SELECT * from dtable WHERE id2 = 'apple'";
$query = sqlsrv_query($conn, $result);

$row_count = sqlsrv_num_rows($query);
echo $row_count;

while($row = sqlsrv_fetch_array($query))
{
      echo 'yes';
}

Thanks.

谢谢。

回答by alpakyol

It is because sqlsrv_query()uses SQLSRV_CURSOR_FORWARDcursor type by default. However, in order to get a result from sqlsrv_num_rows(), you should choose one of these cursor types below:

这是因为默认sqlsrv_query()使用SQLSRV_CURSOR_FORWARD游标类型。但是，为了从 中获得结果sqlsrv_num_rows()，您应该选择以下游标类型之一：

• SQLSRV_CURSOR_STATIC
• SQLSRV_CURSOR_KEYSET
• SQLSRV_CURSOR_CLIENT_BUFFERED

• SQLSRV_CURSOR_STATIC
• SQLSRV_CURSOR_KEYSET
• SQLSRV_CURSOR_CLIENT_BUFFERED

For more information, check: Cursor Types (SQLSRV Driver)

有关更多信息，请查看：游标类型（SQLSRV 驱动程序）

In conclusion, if you use your query like:

总之，如果您使用以下查询：

$query = sqlsrv_query($conn, $result, array(), array( "Scrollable" => 'static' ));

you will get result in:

你会得到结果：

$row_count = sqlsrv_num_rows($query);

回答by CRAFTY DBA

I agree with Hyman. I count(*) is a quick way to get a row count, however, you might have to do a clustered index scan. For small data sets, this is not an issue.

我同意Hyman。I count(*) 是获取行计数的快速方法，但是，您可能需要执行聚集索引扫描。对于小数据集，这不是问题。

On the other hand, you can use the system catalog views. However, these get updated per some background thread. For multi-terabyte data sets, a catalog lookup might be quicker.

另一方面，您可以使用系统目录视图。但是，这些会根据某个后台线程进行更新。对于多 TB 数据集，目录查找可能会更快。

Depending upon system events, the count may or may not be accurate.

根据系统事件，计数可能准确，也可能不准确。

http://sqlblog.com/blogs/kalen_delaney/archive/2009/12/07/how-many-rows.aspx

http://sqlblog.com/blogs/kalen_delaney/archive/2009/12/07/how-many-rows.aspx

It all depends on how accurate you need to be. If it is ledger data, then very accurate. If it is forecast data, may be less accurate.

这完全取决于您需要达到的准确程度。如果是账本数据，那么非常准确。如果是预测数据，可能不太准确。

I suggest using RCSI instead of the default READ COMMITTED to get a better point in time count. This is using the SELECT COUNT(*) FROM [TABLE]syntax.

我建议使用 RCSI 而不是默认的 READ COMMITTED 以获得更好的时间点计数。这是使用SELECT COUNT(*) FROM [TABLE]语法。

http://www.sqlpass.org/summit/2013/Sessions/SessionDetails.aspx?sid=4730

http://www.sqlpass.org/summit/2013/Sessions/SessionDetails.aspx?sid=4730

Randy Knight had a great presentation on this last year.

Randy Knight 去年对此进行了精彩的介绍。

You can also look at my isolation presentation that has code that demonstrates that READ COMMITTED can be inaccurate.

您还可以查看我的隔离演示文稿，其中包含演示 READ COMMITTED 可能不准确的代码。

http://craftydba.com/?page_id=880

http://craftydba.com/?page_id=880

Listed below are three solutions.

下面列出了三种解决方案。

Good luck

祝你好运

J

J

-- Show time & i/o
SET STATISTICS TIME ON
SET STATISTICS IO ON
GO

-- Remove clean buffers & clear plan cache
CHECKPOINT 
DBCC DROPCLEANBUFFERS 
DBCC FREEPROCCACHE
GO

-- test database
use adventureworks2012
go


-- traverse the table
select count(*) as 'rows' from person.address
go

/*

/*

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server 解析和编译时间：CPU 时间 = 0 毫秒，已用时间 = 0 毫秒。

(1 row(s) affected) Table 'Address'. Scan count 1, logical reads 36, physical reads 1, read-ahead reads 34, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

（1 行受影响）表“地址”。扫描计数 1，逻辑读取 36，物理读取 1，预读读取 34，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

SQL Server Execution Times: CPU time = 15 ms, elapsed time = 26 ms.

SQL Server 执行时间：CPU 时间 = 15 毫秒，已用时间 = 26 毫秒。

*/

*/

-- Look at sysindexes
select o.name as 'Table', max(i.rows) 'Rows'
from sysobjects o join sysindexes i
on o.id = i.id
where 
(i.indid = 1 or i.indid = 0) and
o.type = 'U' and
o.name = 'Address'
group by o.name
go

/*

/*

SQL Server parse and compile time: CPU time = 15 ms, elapsed time = 132 ms.

SQL Server 解析和编译时间：CPU 时间 = 15 毫秒，经过时间 = 132 毫秒。

(1 row(s) affected) Table 'sysidxstats'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sysschobjs'. Scan count 1, logical reads 6, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

（受影响的 1 行）表 'sysidxstats'。扫描计数 1，逻辑读取 2，物理读取 2，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表 'sysschobjs'。扫描计数 1，逻辑读 6，物理读 3，预读 0，lob 逻辑读 0，lob 物理读 0，lob 预读 0。

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 36 ms.

SQL Server 执行时间：CPU 时间 = 0 毫秒，已用时间 = 36 毫秒。

*/

*/

-- Look at sys.partitions
SELECT max(rows) as 'Rows'  FROM sys.partitions 
WHERE object_id = object_id('Person.Address');

/*

/*

SQL Server parse and compile time: CPU time = 16 ms, elapsed time = 104 ms.

SQL Server 解析和编译时间：CPU 时间 = 16 毫秒，经过时间 = 104 毫秒。

(1 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sysidxstats'. Scan count 1, logical reads 10, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sysschobjs'. Scan count 0, logical reads 4, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sysrowsets'. Scan count 1, logical reads 6, physical reads 1, read-ahead reads 24, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

（受影响的 1 行）表“工作表”。扫描计数 0，逻辑读取 0，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表 'sysidxstats'。扫描计数 1，逻辑读取 10，物理读取 2，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表 'sysschobjs'。扫描计数 0，逻辑读取 4，物理读取 2，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表 'sysrowsets'。扫描计数 1，逻辑读 6，物理读 1，预读 24，lob 逻辑读 0，lob 物理读 0，lob 预读 0。

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 34 ms.

SQL Server 执行时间：CPU 时间 = 0 毫秒，已用时间 = 34 毫秒。

*/

*/