SQL Server 2005中的计数（*）对计数（Id） [英] Count(*) vs Count(Id) in sql server 2005

问题描述

我使用SQL COUNT 函数从表中获取总数或行数。

  SELECT COUNT（*）FROM Table 
  

和

  SELECT COUNT FROM table 
  

此外，在性能和执行时间方面有什么不同吗？

$如果column_name的值可以为NULL，那么COUNT（column_name）可以返回少于COUNT（*）的值。

>

但是，如果我在回答你的问题时可以略微不同，因为你似乎专注于表现。

首先，注意，发出 SELECT COUNT（*）FROM table 可能会阻止写入者，并且它也将被其他读写器阻止，除非您已经更改了隔离级别-jerk往往是NOLOCK，但我看到一个有前途的人最终开始相信快照）。如果你需要一个绝对事务一致和准确的行计数（即使它只有返回数字给你的毫秒数有效），然后 SELECT COUNT（*）是你唯一的选择。

另一方面，如果你想得到一个99.9％准确的球场，你是更好的像这样的查询：

  SELECT row_count = SUM（row_count）
 FROM sys.dm_db_partition_stats 
 WHERE [object_id] = OBJECT_ID 'dbo.Table'）
 AND index_id IN（0,1）; 
  

（SUM存在分区表的帐户 - 如果不使用表分区，

此DMV维护表格的精确行数，但当前正在参与事务的行除外事务将使您的 SELECT COUNT 查询等待。但是否则这将导致一个比你提出的查询快得多的答案，并且不比使用NOLOCK更准确。

I use SQL COUNT function to get the total number or rows from a table. Is there any difference between the two following statements?

SELECT COUNT(*) FROM Table

and

SELECT COUNT(TableId) FROM Table

Also, is there any difference in terms of performance and execution time?

解决方案

Thilo nailed the difference precisely... COUNT( column_name ) can return fewer than COUNT( * ) if column_name values can be NULL.

However if I can take a slightly different angle at answering your question, since you seem to be focusing on performance.

First, note that issuing SELECT COUNT(*) FROM table will potentially block writers, and it will also be blocked by other readers/writers unless you have altered the isolation level (knee-jerk tends to be NOLOCK but I'm seeing a promising number of people finally starting to believe in snapshot). If you need an absolutely transactionally consistent and accurate row count (even if it is only valid for the number of milliseconds it takes to return the number to you), then SELECT COUNT( * ) is your only choice.

On the other hand, if you are trying to get a 99.9% accurate ballpark, you are much better off with a query like this:

SELECT row_count = SUM(row_count)
  FROM sys.dm_db_partition_stats
  WHERE [object_id] = OBJECT_ID('dbo.Table')
  AND index_id IN (0,1);

(The SUM is there to account for partitioned tables - if you are not using table partitioning, you can leave it out.)

This DMV maintains accurate row counts for tables with the exception of rows that are currently participating in transactions - and those very transactions are the ones that will make your SELECT COUNT query wait. But otherwise this will lead to a much quicker answer than the query you propose, and no less accurate than using NOLOCK.

这篇关于SQL Server 2005中的计数（*）对计数（Id）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！