SQLSERVER 里SELECT COUNT(1) 和SELECT COUNT(*)哪个性能好？
count
s
select
sqlserver
哪个
性能
SQLSERVER 里SELECT COUNT(1) 和SELECT COUNT(*)哪个性能好？ 今天遇到某人在我以前写的一篇文章里问到 如果统计信息没来得及更新的话，那岂不是统计出来的数据时错误的了 这篇文章的地址： SQLSERVER是怎麽通过索引和统计信息来找到目标数据的(第三篇) 之前
SQLSERVER 里SELECT COUNT(1) 和SELECT COUNT(*)哪个性能好？
今天遇到某人在我以前写的一篇文章里问到
如果统计信息没来得及更新的话，那岂不是统计出来的数据时错误的了
这篇文章的地址：SQLSERVER是怎麽通过索引和统计信息来找到目标数据的(第三篇)
之前我以为SELECT COUNT(*)是根据统计信息来的，但是后来想了一下，这个肯定不是
那么SQLSERVER怎麽统计SELECT COUNT(*)的呢？？
其实SQLSERVER也是使用扫描的方法
大家也可以先看一下：SQLSERVER中的ALLOCATION SCAN和RANGE SCAN
但是这里不讨论是ALLOCATION SCAN还是RANGE SCAN，大家知道SQLSERVER使用的是扫描的方式就可以了
聚集索引表
SQL脚本如下：
<span> 1</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span> <span> 2</span> <span>GO</span> <span> 3</span> <span> 4</span> <span>--</span><span>建立聚集索引表</span> <span> 5</span> <span>CREATE</span> <span>TABLE</span> ct1(c1 <span>INT</span>, c2 <span>VARCHAR</span> (<span>2000</span><span>)); </span><span> 6</span> <span>GO</span> <span> 7</span> <span>--</span><span>建立聚集索引</span> <span> 8</span> <span>CREATE</span> <span>CLUSTERED</span> <span>INDEX</span> t1c1 <span>ON</span><span> ct1(c1); </span><span> 9</span> <span>GO</span> <span>10</span> <span>11</span> <span>--</span><span>插入测试数据</span> <span>12</span> <span>DECLARE</span> <span>@a</span> <span>INT</span><span>; </span><span>13</span> <span>SELECT</span> <span>@a</span> <span>=</span> <span>1</span><span>; </span><span>14</span> <span>WHILE</span> (<span>@a</span> <span> <span>12</span><span>) </span><span>15</span> <span>BEGIN</span> <span>16</span> <span>INSERT</span> <span>INTO</span> ct1 <span>VALUES</span> (<span>@a</span>, <span>replicate</span>(<span>'</span><span>a</span><span>'</span>, <span>2000</span><span>)) </span><span>17</span> <span>SELECT</span> <span>@a</span> <span>=</span> <span>@a</span> <span>+</span> <span>1</span> <span>18</span> <span>END</span> <span>19</span> <span>GO</span> <span>20</span> <span>21</span> <span>22</span> <span>23</span> <span>24</span> <span>--</span><span>查询数据</span> <span>25</span> <span>SELECT</span> <span>*</span> <span>FROM</span> ct1 </span>
登录后复制
看一下执行计划
（图片一）
<span>1</span> <span>SET</span> <span>STATISTICS</span> PROFILE <span>ON</span> <span>2</span> <span>GO</span> <span>3</span> <span>SELECT</span> <span>COUNT</span>(<span>*</span>) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>ct1</span><span>]</span>
登录后复制
（图片二）
这里需要了解流聚合运算符
MSDN对于流聚合运算符的解释
（图片三）
宋沄剑的文章里也有对流聚合运算符的解释
SQL Server中的执行引擎入门
重点是理解：Stream Aggregate 运算符按一列或多列对行分组，然后计算由查询返回的一个或多个聚合表达式
Stream Aggregate 运算符按一列对行分组，然后计算由查询返回的一个聚合表达式
我们用下面两个图会清楚一些
（图片四）
（图片五）
SQLSERVER对表中的行分组进行扫描，但是SQLSERVER以多少行为一组来进行扫描呢？？这个不得而知了
为什麽要使用流聚合？
大家一定会自然而然地想到分组统计提高性能，特别是表中数据量非常大的时候，分组统计特别有用
计算标量运算符只是把聚合的结果隐式转换为int类型
大家知道ct1表只有两列，但是SELECT COUNT(3) FROM [dbo].[ct1]也能够返回表中的行数
<span>1</span> <span>SELECT</span> <span>COUNT</span>(<span>1</span>) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>ct1</span><span>]</span>
登录后复制
<span>1</span> <span>SELECT</span> <span>COUNT</span>(<span>3</span>) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>ct1</span><span>]</span>
登录后复制
（图片六）
就算用列名都是一样的执行计划
<span>1</span> <span>SELECT</span> <span>COUNT</span>(c1) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>ct1</span><span>]</span> <span>2</span> <span>SELECT</span> <span>COUNT</span>(c2) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>ct1</span><span>]</span>
登录后复制
（图片七）
SQLSERVER究竟以哪一列来进行表的行数统计的呢？？？？？？
答案就在
Stream Aggregate 运算符要求输入的数据要按某列进行排序，如果由于前面的 Sort 运算符或已排序的索引查找或扫描导致数据尚未排序，
则优化器将在此运算符前面使用一个 Sort 运算符，使表的某列是有序排序的。
<span>1</span> <span>SELECT</span> <span>COUNT</span>(<span>*</span><span>) </span><span>2</span> <span>SELECT</span> <span>count</span>（<span>3</span><span>） </span><span>3</span> <span>SELECT</span> <span>count</span>（c2）
登录后复制
（图片八）
上面三个SQL语句都是按照聚集索引的第一个字段（ct1表中的c1列）来进行统计的
因为聚集索引的第一个字段是根据建立聚集索引的时候的排序顺序预先排好序
Stream Aggregate 运算符要求输入的数据要按某列进行排序
所以无论是指定字段名、*还是数字，都是根据聚集索引的第一个字段来统计
堆表
SQL脚本如下:
<span> 1</span> <span>CREATE</span> <span>TABLE</span> t1(c1 <span>INT</span>, c2 <span>VARCHAR</span> (<span>8000</span><span>)); </span><span> 2</span> <span>GO</span> <span> 3</span> <span> 4</span> <span> 5</span> <span>--</span><span>插入测试数据</span> <span> 6</span> <span> 7</span> <span> 8</span> <span> 9</span> <span>DECLARE</span> <span>@a</span> <span>INT</span><span>; </span><span>10</span> <span>SELECT</span> <span>@a</span> <span>=</span> <span>1</span><span>; </span><span>11</span> <span>WHILE</span> (<span>@a</span> <span> <span>12</span><span>) </span><span>12</span> <span>BEGIN</span> <span>13</span> <span>INSERT</span> <span>INTO</span> t1 <span>VALUES</span> (<span>@a</span>, <span>replicate</span>(<span>'</span><span>a</span><span>'</span>, <span>5000</span><span>)) </span><span>14</span> <span>SELECT</span> <span>@a</span> <span>=</span> <span>@a</span> <span>+</span> <span>1</span> <span>15</span> <span>END</span> <span>16</span> <span>GO</span> <span>17</span> <span>18</span> <span>19</span> <span>20</span> <span>--</span><span>查询数据</span> <span>21</span> <span>SELECT</span> <span>*</span> <span>FROM</span> t1</span>
登录后复制
（图片九）
（图片十）
堆表这里使用的是ALLOCATION SCAN
因为分配页面的时候是根据c1列的值从1~12进行分配的
（图片十一）
109页面存放的c1值是1
120页面存放的c1值是2
174页面存放的c1值是3
193页面存放的c1值是4
8316页面存放的c1值是5
8340页面存放的c1值是6
8351页面存放的c1值是7
8353页面存放的c1值是8
。
。
。
。
。
（图片十二）
这里执行计划在流聚合之前并没有进行排序的原因：因为建表进行页面分配的时候已经按照C1列的值进行有序的页面分配
所以当ALLOCATION SCAN的时候，C1列已经是有序的了
（图片十三）
不明白的童鞋可以再看一下：SQLSERVER中的ALLOCATION SCAN和RANGE SCAN
为什麽SQLSERVER选择统计C1列的值，因为C1列的值是可以排序的，C2列不能排序，统计不了
那么如果一个表中没有可以用来排序的列呢????
先drop掉t1表，再建立t1表，脚本如下：
<span> 1</span> <span>CREATE</span> <span>TABLE</span> t1(c1 <span>VARCHAR</span> (<span>2</span>), c2 <span>VARCHAR</span> (<span>8000</span><span>)); </span><span> 2</span> <span>GO</span> <span> 3</span> <span> 4</span> <span> 5</span> <span>--</span><span>插入测试数据</span> <span> 6</span> <span>DECLARE</span> <span>@a</span> <span>INT</span><span>; </span><span> 7</span> <span>SELECT</span> <span>@a</span> <span>=</span> <span>1</span><span>; </span><span> 8</span> <span>WHILE</span> (<span>@a</span> <span> <span>12</span><span>) </span><span> 9</span> <span>BEGIN</span> <span>10</span> <span>INSERT</span> <span>INTO</span> t1 <span>VALUES</span> (<span>'</span><span>a</span><span>'</span>, <span>replicate</span>(<span>'</span><span>a</span><span>'</span>, <span>5000</span><span>)) </span><span>11</span> <span>SELECT</span> <span>@a</span> <span>=</span> <span>@a</span> <span>+</span> <span>1</span> <span>12</span> <span>END</span> <span>13</span> <span>GO</span> <span>14</span> <span>15</span> <span>16</span> <span>--</span><span>查询数据</span> <span>17</span> <span>SELECT</span> <span>*</span> <span>FROM</span> t1</span>
登录后复制
结果是
（图片十四）
我觉得SQLSERVER应该会在表中加上一列，类似用来区分聚集索引页面重复值的UNIQUIFIER(KEY)列
当查询完毕之后就删除掉这一列
（图片十五）
非聚集索引表
SQL脚本如下:
<span> 1</span> <span>CREATE</span> <span>TABLE</span> nct1(c1 <span>INT</span>, c2 <span>VARCHAR</span> (<span>8000</span><span>)); </span><span> 2</span> <span>GO</span> <span> 3</span> <span>--</span><span>建立非聚集索引</span> <span> 4</span> <span>CREATE</span> <span>INDEX</span> nt1c1 <span>ON</span><span> nct1(c1); </span><span> 5</span> <span>GO</span> <span> 6</span> <span> 7</span> <span>--</span><span>插入数据</span> <span> 8</span> <span>DECLARE</span> <span>@a</span> <span>INT</span><span>; </span><span> 9</span> <span>SELECT</span> <span>@a</span> <span>=</span> <span>1</span><span>; </span><span>10</span> <span>WHILE</span> (<span>@a</span> <span> <span>10</span><span>) </span><span>11</span> <span>BEGIN</span> <span>12</span> <span>INSERT</span> <span>INTO</span> nct1 <span>VALUES</span> (<span>@a</span>, <span>replicate</span>(<span>'</span><span>a</span><span>'</span>, <span>5000</span><span>)) </span><span>13</span> <span>SELECT</span> <span>@a</span> <span>=</span> <span>@a</span> <span>+</span> <span>1</span> <span>14</span> <span>END</span> <span>15</span> <span>GO</span> <span>16</span> <span>17</span> <span>--</span><span>查询数据</span> <span>18</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>nct1</span><span>]</span> <span>19</span> </span>
登录后复制
（图片十六）
大家一定要记住:非聚集索引是建立在c1列上的!!!
下面两个SQL语句都是一样的,都是根据c1列的值进行统计,而SQLSERVER只扫描非聚集索引页面,而不扫描数据页面
<span>1</span> <span>SELECT</span> <span>COUNT</span>(<span>*</span>) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>nct1</span><span>]</span> <span>2</span> <span>3</span> <span>SELECT</span> <span>COUNT</span>(<span>3</span>) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>nct1</span><span>]</span>
登录后复制
SELECT COUNT(*) FROM [dbo].[nct1]是不需要到数据页面去读取c2列的数据的,只需要扫描非聚集索引页面（c1列）就可以了
SELECT COUNT(3) FROM [dbo].[nct1]跟SELECT COUNT(*) FROM [dbo].[nct1]也是一样
不知道大家还记得书签查找不，如果SQLSERVER扫描了非聚集索引页面之后还需要到数据页面去读取其他字段的数据的话，就需要RID查找运算符
（图片十七）
SQLSERVER聚集索引与非聚集索引的再次研究（下）
SELECT COUNT(*) FROM [dbo].[nct1]和SELECT COUNT(3) FROM [dbo].[nct1]的扫描方式跟前面说的聚集索引表是差不多的
这里就不一一叙述了~
而SELECT COUNT(c2) FROM [dbo].[nct1]为什麽会用表扫描呢？
<span>1</span> <span>SELECT</span> <span>COUNT</span>(c2) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>nct1</span><span>]</span>
登录后复制
c2列不在非聚集索引页面里，所以需要表扫描
（图片十八）
SELECT COUNT(c2) FROM [dbo].[nct1]跟前面说的堆表是差不多的，这里就不一一叙述了
总结
做了这麽多实验
可以总结出：select count(*)、count(数字)、count(字段名)是没有性能差别的！！
我说的没有差别是在相同的条件下，就像非聚集索引表，如果使用
SELECT COUNT(c2) FROM [dbo].[nct1]
跟SELECT COUNT(*) FROM [dbo].[nct1]、SELECT COUNT(3) FROM [dbo].[nct1]相比肯定有差别
因为SELECT COUNT(c2) FROM [dbo].[nct1]走的是表扫描
如果SELECT COUNT(c1) FROM [dbo].[nct1]
跟SELECT COUNT(*) FROM [dbo].[nct1]、SELECT COUNT(3) FROM [dbo].[nct1]相比是没有差别的
（图片十九）
大家走的都是非聚集索引扫描
无论是聚集索引表、堆表、非聚集索引表都是扫描表中的记录来统计出表中的行数的
希望大家看完这篇文章之后，不再一知半解了，这是我的希望o(∩_∩)o
如有不对的地方，欢迎大家拍砖o(∩_∩)o
-----------------------------------------------------------------------
补上IO和时间的比较 2013-10-19
---------------------------------
聚集索引表
<span>1</span> <span>SET</span> <span>STATISTICS</span> IO <span>ON</span> <span>2</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span> <span>3</span> <span>GO</span> <span>4</span> <span>SELECT</span> <span>COUNT</span>(<span>*</span>) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>ct1</span><span>]</span>
登录后复制
<span>1</span> <span>SQL Server 分析和编译时间: </span><span>2</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>2</span><span> 毫秒。 </span><span>3</span> <span>4</span> (<span>1</span><span> 行受影响) </span><span>5</span> 表 <span>'</span><span>ct1</span><span>'</span>。扫描计数 <span>1</span>，逻辑读取 <span>5</span> 次，物理读取 <span>0</span> 次，预读 <span>0</span> 次，lob 逻辑读取 <span>0</span> 次，lob 物理读取 <span>0</span> 次，lob 预读 <span>0</span><span> 次。 </span><span>6</span> <span>7</span> <span>SQL Server 执行时间: </span><span>8</span> CPU 时间 <span>=</span> <span>15</span> 毫秒，占用时间 <span>=</span> <span>2</span> 毫秒。
登录后复制
<span>1</span> <span>SET</span> <span>STATISTICS</span> IO <span>ON</span> <span>2</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span> <span>3</span> <span>GO</span> <span>4</span> <span>SELECT</span> <span>COUNT</span>(<span>1</span>) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>ct1</span><span>]</span>
登录后复制
<span>1</span> <span>SQL Server 分析和编译时间: </span><span>2</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>2</span><span> 毫秒。 </span><span>3</span> <span>4</span> (<span>1</span><span> 行受影响) </span><span>5</span> 表 <span>'</span><span>ct1</span><span>'</span>。扫描计数 <span>1</span>，逻辑读取 <span>5</span> 次，物理读取 <span>0</span> 次，预读 <span>0</span> 次，lob 逻辑读取 <span>0</span> 次，lob 物理读取 <span>0</span> 次，lob 预读 <span>0</span><span> 次。 </span><span>6</span> <span>7</span> <span>SQL Server 执行时间: </span><span>8</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span> 毫秒。
登录后复制
<span>1</span> <span>SET</span> <span>STATISTICS</span> IO <span>ON</span> <span>2</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span> <span>3</span> <span>GO</span> <span>4</span> <span>SELECT</span> <span>COUNT</span>(c1) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>ct1</span><span>]</span>
登录后复制
<span>1</span> <span>SQL Server 分析和编译时间: </span><span>2</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>1</span><span> 毫秒。 </span><span>3</span> <span>4</span> (<span>1</span><span> 行受影响) </span><span>5</span> 表 <span>'</span><span>ct1</span><span>'</span>。扫描计数 <span>1</span>，逻辑读取 <span>5</span> 次，物理读取 <span>0</span> 次，预读 <span>0</span> 次，lob 逻辑读取 <span>0</span> 次，lob 物理读取 <span>0</span> 次，lob 预读 <span>0</span><span> 次。 </span><span>6</span> <span>7</span> <span>SQL Server 执行时间: </span><span>8</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span> 毫秒。
登录后复制
---------------------------------------------------
堆表
<span>1</span> <span>SET</span> <span>STATISTICS</span> IO <span>ON</span> <span>2</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span> <span>3</span> <span>GO</span> <span>4</span> <span>SELECT</span> <span>COUNT</span>(<span>*</span>) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>t1</span><span>]</span>
登录后复制
<span> 1</span> <span>SQL Server 分析和编译时间: </span><span> 2</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 3</span> <span> 4</span> <span>SQL Server 执行时间: </span><span> 5</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 6</span> <span> 7</span> <span>SQL Server 执行时间: </span><span> 8</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 9</span> <span>SQL Server 分析和编译时间: </span><span>10</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span>11</span> <span>12</span> (<span>1</span><span> 行受影响) </span><span>13</span> 表 <span>'</span><span>t1</span><span>'</span>。扫描计数 <span>1</span>，逻辑读取 <span>12</span> 次，物理读取 <span>0</span> 次，预读 <span>0</span> 次，lob 逻辑读取 <span>0</span> 次，lob 物理读取 <span>0</span> 次，lob 预读 <span>0</span><span> 次。 </span><span>14</span> <span>15</span> <span>SQL Server 执行时间: </span><span>16</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span> 毫秒。
登录后复制
<span>1</span> <span>SET</span> <span>STATISTICS</span> IO <span>ON</span> <span>2</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span> <span>3</span> <span>GO</span> <span>4</span> <span>SELECT</span> <span>COUNT</span>(<span>1</span>) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>t1</span><span>]</span>
登录后复制
<span> 1</span> <span>SQL Server 分析和编译时间: </span><span> 2</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 3</span> <span> 4</span> <span>SQL Server 执行时间: </span><span> 5</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 6</span> <span> 7</span> <span>SQL Server 执行时间: </span><span> 8</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 9</span> <span>SQL Server 分析和编译时间: </span><span>10</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>79</span><span> 毫秒。 </span><span>11</span> <span>12</span> (<span>1</span><span> 行受影响) </span><span>13</span> 表 <span>'</span><span>t1</span><span>'</span>。扫描计数 <span>1</span>，逻辑读取 <span>12</span> 次，物理读取 <span>0</span> 次，预读 <span>0</span> 次，lob 逻辑读取 <span>0</span> 次，lob 物理读取 <span>0</span> 次，lob 预读 <span>0</span><span> 次。 </span><span>14</span> <span>15</span> <span>SQL Server 执行时间: </span><span>16</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span> 毫秒。
登录后复制
<span>1</span> <span>SET</span> <span>STATISTICS</span> IO <span>ON</span> <span>2</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span> <span>3</span> <span>GO</span> <span>4</span> <span>SELECT</span> <span>COUNT</span>(c1) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>t1</span><span>]</span>
登录后复制
<span> 1</span> <span>SQL Server 分析和编译时间: </span><span> 2</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 3</span> <span> 4</span> <span>SQL Server 执行时间: </span><span> 5</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 6</span> <span> 7</span> <span>SQL Server 执行时间: </span><span> 8</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 9</span> <span>SQL Server 分析和编译时间: </span><span>10</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>1</span><span> 毫秒。 </span><span>11</span> <span>12</span> (<span>1</span><span> 行受影响) </span><span>13</span> 表 <span>'</span><span>t1</span><span>'</span>。扫描计数 <span>1</span>，逻辑读取 <span>12</span> 次，物理读取 <span>0</span> 次，预读 <span>0</span> 次，lob 逻辑读取 <span>0</span> 次，lob 物理读取 <span>0</span> 次，lob 预读 <span>0</span><span> 次。 </span><span>14</span> <span>15</span> <span>SQL Server 执行时间: </span><span>16</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span> 毫秒。
登录后复制
-----------------------------------------------------------------------------------------
非聚集索引表
<span>1</span> <span>SET</span> <span>STATISTICS</span> IO <span>ON</span> <span>2</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span> <span>3</span> <span>GO</span> <span>4</span> <span>SELECT</span> <span>COUNT</span>(<span>*</span>) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>nct1</span><span>]</span>
登录后复制
<span> 1</span> <span>SQL Server 分析和编译时间: </span><span> 2</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 3</span> <span> 4</span> <span>SQL Server 执行时间: </span><span> 5</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 6</span> <span> 7</span> <span>SQL Server 执行时间: </span><span> 8</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 9</span> <span>SQL Server 分析和编译时间: </span><span>10</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>1</span><span> 毫秒。 </span><span>11</span> <span>12</span> (<span>1</span><span> 行受影响) </span><span>13</span> 表 <span>'</span><span>nct1</span><span>'</span>。扫描计数 <span>1</span>，逻辑读取 <span>2</span> 次，物理读取 <span>0</span> 次，预读 <span>0</span> 次，lob 逻辑读取 <span>0</span> 次，lob 物理读取 <span>0</span> 次，lob 预读 <span>0</span><span> 次。 </span><span>14</span> <span>15</span> <span>SQL Server 执行时间: </span><span>16</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span> 毫秒。
登录后复制
<span>1</span> <span>SET</span> <span>STATISTICS</span> IO <span>ON</span> <span>2</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span> <span>3</span> <span>GO</span> <span>4</span> <span>SELECT</span> <span>COUNT</span>(<span>1</span>) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>nct1</span><span>]</span>
登录后复制
<span> 1</span> <span>SQL Server 分析和编译时间: </span><span> 2</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 3</span> <span> 4</span> <span>SQL Server 执行时间: </span><span> 5</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 6</span> <span> 7</span> <span>SQL Server 执行时间: </span><span> 8</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 9</span> <span>SQL Server 分析和编译时间: </span><span>10</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span>11</span> <span>12</span> (<span>1</span><span> 行受影响) </span><span>13</span> 表 <span>'</span><span>nct1</span><span>'</span>。扫描计数 <span>1</span>，逻辑读取 <span>2</span> 次，物理读取 <span>0</span> 次，预读 <span>0</span> 次，lob 逻辑读取 <span>0</span> 次，lob 物理读取 <span>0</span> 次，lob 预读 <span>0</span><span> 次。 </span><span>14</span> <span>15</span> <span>SQL Server 执行时间: </span><span>16</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>49</span> 毫秒。
登录后复制
<span>1</span> <span>SET</span> <span>STATISTICS</span> IO <span>ON</span> <span>2</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span> <span>3</span> <span>GO</span> <span>4</span> <span>SELECT</span> <span>COUNT</span>(c1) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>nct1</span><span>]</span>
登录后复制
<span> 1</span> <span>SQL Server 分析和编译时间: </span><span> 2</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 3</span> <span> 4</span> <span>SQL Server 执行时间: </span><span> 5</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 6</span> <span> 7</span> <span>SQL Server 执行时间: </span><span> 8</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 9</span> <span>SQL Server 分析和编译时间: </span><span>10</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span>11</span> <span>12</span> (<span>1</span><span> 行受影响) </span><span>13</span> 表 <span>'</span><span>nct1</span><span>'</span>。扫描计数 <span>1</span>，逻辑读取 <span>2</span> 次，物理读取 <span>0</span> 次，预读 <span>0</span> 次，lob 逻辑读取 <span>0</span> 次，lob 物理读取 <span>0</span> 次，lob 预读 <span>0</span><span> 次。 </span><span>14</span> <span>15</span> <span>SQL Server 执行时间: </span><span>16</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>1</span> 毫秒。
登录后复制
2014-6-21补充：
<span>USE</span> <span>[</span><span>sss</span><span>]</span>
<span>--</span><span>建表</span>
<span>CREATE</span> <span>TABLE</span> counttb ( id <span>INT</span> <span>NULL</span><span> )
</span><span>--</span><span>插入数据</span>
<span>INSERT</span> <span>INTO</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>counttb</span><span>]</span><span>
( </span><span>[</span><span>id</span><span>]</span><span> )
</span><span>SELECT</span> <span>1</span>
<span>UNION</span> <span>ALL</span>
<span>SELECT</span> <span>NULL</span>
<span>--</span><span>统计行数</span>
<span>SELECT</span> <span>COUNT</span>(<span>1</span><span>) ,
</span><span>COUNT</span>(<span>*</span><span>) ,
</span><span>COUNT</span><span>(id)
</span><span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>counttb</span><span>]</span>
<span>--</span><span>查询索引的统计值</span>
<span>SELECT</span> a.<span>[</span><span>rowcnt</span><span>]</span><span> ,
b.</span><span>[</span><span>name</span><span>]</span>
<span>FROM</span> sys.<span>[</span><span>sysindexes</span><span>]</span> <span>AS</span><span> a
</span><span>INNER</span> <span>JOIN</span> sys.<span>[</span><span>objects</span><span>]</span> <span>AS</span> b <span>ON</span> a.<span>[</span><span>id</span><span>]</span> <span>=</span> b.<span>[</span><span>object_id</span><span>]</span>
<span>WHERE</span> b.<span>[</span><span>name</span><span>]</span> <span>=</span> <span>'</span><span>counttb</span><span>'</span>
<span>--</span><span>创建非聚集索引</span>
<span>CREATE</span> <span>INDEX</span> ix_counttb_id <span>ON</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>counttb</span><span>]</span><span> (id)
</span><span>--</span><span>统计行数</span>
<span>SELECT</span> <span>COUNT</span>(<span>1</span><span>) ,
</span><span>COUNT</span>(<span>*</span><span>) ,
</span><span>COUNT</span><span>(id)
</span><span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>counttb</span><span>]</span>登录后复制
因为在创建非聚集索引前和创建非聚集索引后的行数值都是一样的，可以看出COUNT(*) COUNT(1) 和COUNT(ID)
的统计方式不一样，所以没有可比性
一般我们在统计行数的时候都会把NULL值统计在内的，所以这样的话，最好就是使用COUNT(*) 和COUNT(1) ，这样的速度最快！！
本站声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
R.E.P.O.能量晶体解释及其做什么（黄色晶体）
2 周前
By 尊渡假赌尊渡假赌尊渡假赌
仓库：如何复兴队友
1 个月前
By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险：如何获得巨型种子
4 周前
By 尊渡假赌尊渡假赌尊渡假赌
击败分裂小说需要多长时间？
3 周前
By DDD
R.E.P.O.保存文件位置：在哪里以及如何保护它？
3 周前
By DDD
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
sqlserver怎么导入mdf文件
Apr 08, 2024 am 11:41 AM
导入步骤如下：将 MDF 文件复制到 SQL Server 的数据目录（通常为 C:\Program Files\Microsoft SQL Server\MSSQL\DATA）。在 SQL Server Management Studio（SSMS）中，打开数据库并选择“附加”。单击“添加”按钮，选择 MDF 文件。确认数据库名称，点击确定按钮即可。
sqlserver安装失败怎么样删除干净
Apr 05, 2024 pm 11:27 PM
如果 SQL Server 安装失败，可通过以下步骤清理：卸载 SQL Server删除注册表项删除文件和文件夹重启计算机
mysql和sqlserver语法有什么区别
Apr 22, 2024 pm 06:33 PM
MySQL 和 SQL Server 的语法差异主要体现在数据库对象、数据类型、SQL 语句和其他方面。数据库对象差异包括存储引擎和文件组的指定方式、索引和约束的创建。数据类型差异涉及数值类型、字符类型和日期时间类型的差异。SQL 语句差异体现在结果集限制、数据插入、更新和删除操作等方面。其他差异还包括标识列、视图和存储过程的创建方式。了解这些差异对于使用不同的数据库系统时避免错误非常重要。
sqlserver怎么删除数据库
Apr 05, 2024 pm 11:00 PM
要删除 SQL Server 数据库，请依次执行以下步骤：1. 登录 SQL Server Management Studio；2. 展开数据库节点；3. 右键单击要删除的数据库；4. 选择“删除”；5. 确认删除。注意：删除数据库不可逆，请确保已备份重要数据且断开其他对象连接。
本地运行性能超越 OpenAI Text-Embedding-Ada-002 的 Embedding 服务，太方便了！
Apr 15, 2024 am 09:01 AM
Ollama是一款超级实用的工具，让你能够在本地轻松运行Llama2、Mistral、Gemma等开源模型。本文我将介绍如何使用Ollama实现对文本的向量化处理。如果你本地还没有安装Ollama，可以阅读这篇文章。本文我们将使用nomic-embed-text[2]模型。它是一种文本编码器，在短的上下文和长的上下文任务上，性能超越了OpenAItext-embedding-ada-002和text-embedding-3-small。启动nomic-embed-text服务当你已经成功安装好o
navicat数据库文件在哪
Apr 23, 2024 am 10:57 AM
Navicat 数据库配置文件的存储位置因操作系统而异：Windows：用户特定路径为 %APPDATA%\PremiumSoft\Navicat\macOS：用户特定路径为 ~/Library/Application Support/Navicat\Linux：用户特定路径为 ~/.config/navicat\配置文件名称包含连接类型，如 navicat_mysql.ini。这些配置文件存储数据库连接信息、查询历史和 SSH 设置。
不同Java框架的性能对比
Jun 05, 2024 pm 07:14 PM
不同Java框架的性能对比：RESTAPI请求处理：Vert.x最佳，请求速率达SpringBoot2倍，Dropwizard3倍。数据库查询：SpringBoot的HibernateORM优于Vert.x及Dropwizard的ORM。缓存操作：Vert.x的Hazelcast客户机优于SpringBoot及Dropwizard的缓存机制。合适框架：根据应用需求选择，Vert.x适用于高性能Web服务，SpringBoot适用于数据密集型应用，Dropwizard适用于微服务架构。
sqlserver删不干净无法重新安装怎么办
Apr 05, 2024 pm 11:30 PM
SQL Server 删除不干净导致无法重新安装的问题可以通过以下步骤解决：手动删除文件和注册表项；使用 SQL Server 安装卸载工具；使用第三方卸载工具；检查 Windows 事件查看器；重新启动计算机；重新安装 SQL Server。
