非常接近(忽略任何进行中的事务)将是:

Very close approximate (ignoring any in-flight transactions) would be:

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 s.[schema_id] = t.[schema_id]
  WHERE t.name = N'myTable'
  AND s.name = N'dbo'
  AND p.index_id IN (0,1);

这将比 COUNT(*) 返回快得多，并且如果您的表变化得足够快，那么它的准确度并没有降低 - 如果您的表在您开始 COUNT 之间发生了变化(并且锁被占用)以及何时返回(当锁被释放并且所有等待的写事务现在都被允许写入表时)，它是否更有价值?我不这么认为.

This will return much, much quicker than COUNT(*), and if your table is changing quickly enough, it's not really any less accurate - if your table has changed between when you started your COUNT (and locks were taken) and when it was returned (when locks were released and all the waiting write transactions were now allowed to write to the table), is it that much more valuable? I don't think so.

如果您有要计算的表的某个子集(例如，WHERE some_column IS NULL)，您可以在该列上创建一个过滤索引，并以一种方式构建 where 子句或其他，取决于它是例外还是规则(因此在较小的集合上创建过滤索引).所以这两个索引之一:

If you have some subset of the table you want to count (say, WHERE some_column IS NULL), you could create a filtered index on that column, and structure the where clause one way or the other, depending on whether it was the exception or the rule (so create the filtered index on the smaller set). So one of these two indexes:

CREATE INDEX IAmTheException ON dbo.table(some_column)
  WHERE some_column IS NULL;

CREATE INDEX IAmTheRule ON dbo.table(some_column)
  WHERE some_column IS NOT NULL;

然后您可以使用类似的方式获得计数:

Then you could get the count in a similar way using:

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 s.[schema_id] = t.[schema_id]
  INNER JOIN sys.indexes AS i
  ON p.index_id = i.index_id
  WHERE t.name = N'myTable'
  AND s.name = N'dbo'
  AND i.name = N'IAmTheException' -- or N'IAmTheRule'
  AND p.index_id IN (0,1);

如果你想知道相反的情况，你只需从上面的第一个查询中减去.

And if you want to know the opposite, you just subtract from the first query above.