Null 值会被聚合函数忽略,默认情况下,Sql Server会给出Warning:
Warning: Null value is eliminated by an aggregate or other SET operation.
这个Warning说明Null value 被聚合函数忽略,此时的 SET ANSI_WARNINGS 选项是ON状态,例如,count()函数不会计算null值,min/max函数不会计算null值。如果设置 SET ANSI_WARNINGS OFF ,SQL Server不会抛出 warning 消息。
1,忽略NULL 值
例如,结果是1,正确,但是出现一个Warning。
use tempdb
go
set ANSI_WARNINGS on
if object_id('#dt_test','U') is not null
drop table #dt_test
create table #dt_test
(
id int
)
insert into #dt_test
values(1),(null)
select min(id)
from #dt_test
强烈推荐:将ANSI_Warnings选项设置为ON
2,查看当前DB的 ANSI_Warnings 选项的设置
select name,
database_id,
is_ansi_warnings_on
from sys.databases
where database_id=db_id()
引用《SET ANSI_WARNINGS》:
As the Warning says, NULLs are being ignored because we are using aggregate function (SUM, AVG). To avoid the warning we can use “set ansi_warnings off” before the script. Here is the modified script.
SET ANSI_WARNINGS OFF
When set to ON, if null values appear in aggregate functions, such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT, a warning message is generated. When set to OFF, no warning is issued.
SET ANSI_WARNINGS must be ON when you are creating or manipulating indexes on computed columns or indexed views. If SET ANSI_WARNINGS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail
参照文档: