COUNT（*）是否已编入索引？ [英] Is COUNT(*) indexed?

问题描述

我想知道只是为了好奇，从SomeTableName 选择计数（*）也遍历数据库的所有行为选择* from SomeTableName ？

或者，每当添加或删除一行时，数据库元数据中是否还有其他计数字段更新？

此外，我想知道这两个查询中哪一个更快，更快？

解决方案

  SELECT Count（*）
 FROM SomeTableName 
  pre> 
 
 
将始终计算所有行。虽然（不同于 SELECT * ），它不必读取所有列，并且可以使用最窄的（未过滤的）索引。
 
 
 
与MySQL（MyISAM引擎）不同，它不从元数据检索值。 
 
 
 
元数据中提供rowcount值，可以从  sys.partitions  ，但这不会用于 COUNT 查询，并且不总是准确的。
 
I want to know just for curiosity that, does Select Count(*) from SomeTableName also traverses all the rows of the database as that of Select * from SomeTableName?

Or is there any other count field available in database's metadata that updates itself each time a row is added or deleted? And that field is accessed by the former query.

Also I want to know that which of the both queries is faster and how much?
解决方案
SELECT Count(*)
FROM   SomeTableName 
will always count all rows. Though (unlike SELECT *) it does not have to read all columns and can use the narrowest (non filtered) index available to do so.

Unlike MySQL (MyISAM engine) it does not retrieve the value from metadata. 

A rowcount value is available in the metadata and can be retrieved from sys.partitions but this is never used for COUNT queries and isn't always accurate.

                        
这篇关于COUNT（*）是否已编入索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！