SQL Server中的计数(*)和计数(1)之间的差异 [英] Difference between count (*) and count (1) in SQL server

问题描述

count(1)和count(*)之间的差异，例如.

我尝试过的事情:

用示例

difference between count(1) and count(*) with example.

What I have tried:

difference between count(1) and count(*) with example

推荐答案

count(1)和count(*)之间的区别曾经有一段时间与COUNT中的内容或表达式相关在EXISTS结构中使用的.

在古老的时代，如果在这样的表达式中使用列，则优化器不一定足够聪明以消除行检索.当数据本身与查询无关时，这会导致对表进行不必要的磁盘或内存I/O操作.

但是，这是恐龙灭绝之前的时间，因此任何现代优化器都可以识别这种情况，并且知道不获取实际记录.我可以想象有些数据库产品尚处于初期阶段，并且会遇到这类问题，但是如果我们谈论的是主流数据库，那么答案很简单:没有区别.

There used to be a time when it was relevant what you had inside COUNT or what expression you used in an EXISTS structure.

In the old ages the optimizer wasn''t necessarily smart enough to eliminate the row retrieval if a column was used in such expression. This caused an unnecessary disk or memory I/O operation to the table when the data itself was irrelevant to the query.

However, this was a time just before dinosaurs were extinct so any modern optimizer recognizes this situation and understands not to fetch the actual record. I could imagine there are some database products that are in their early stages and suffer from these kinds of problems but if we''re talking about the mainstream databases then the answer is easy: No difference.

不存在所有存在的问题

选择 count(*) y count(1) => Son iguales，es decir si tenemos una tabla con 5 registros en los dos casos nos va dar 5.

no existe una diferencia entre estos dos tipos de consultas

select count(*) y count(1) => son iguales, es decir si tenemos una tabla con 5 registros en los dos casos nos va dar 5.

create table #tabla (student varchar(50), id int)
insert into #table (student,id)
values('Camila',1111)
     ,('Andres',2222)
     ,('Maria',null)
     ,('Jose',null) 
     ,('Pedro',3333)

select count(*) from #tabla
-- result 5

select count(1) from #tabla 
-- result 5

select count(id) from #tabla
-- result 3

espero poder ayudarte...

这篇关于SQL Server中的计数(*)和计数(1)之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！