I recently ran into a forum post where the poster wanted to know why they were getting the following warning during query execution:
"Null value is eliminated by an aggregate or other SET operation."
Having been asked this question a few times by a few of my office colleagues as well, I thought to write up a quick post on the reason behind this warning.
A quick test
The test below is simple – I am creating a sample test table which allows NULL values to be inserted. I am then trying to perform a simple aggregate function (SUM) over the NULL-able column. Upon checking the “Messages” tab, we see that no warning is returned.
USE [tempdb];
GO
SET NOCOUNT ON;
--Safety Check
IF OBJECT_ID('dbo.NULLAggregation','U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[NULLAggregation];
END
GO
--Create the test table
CREATE TABLE [dbo].[NULLAggregation]
([Id] INT NOT NULL IDENTITY (1,1),
[Value] INT NULL,
[ValueString] VARCHAR(50) NOT NULL,
CONSTRAINT [pk_NULLAggregation] PRIMARY KEY ([Id])
);
GO
--Insert the test data
INSERT INTO [dbo].[NULLAggregation] ([Value], [ValueString])
VALUES ( 1, 'One'),
(10, 'Ten'),
(22, 'Twenty-Two');
GO
--Perform the aggregation
--NOTE: No NULL values are in the table at this point
SELECT SUM([na].[Value]) AS [SumOfValues]
FROM [dbo].[NULLAggregation] AS [na];
GO
/****************************************
RESULT
-----------
SumOfValues
-----------
33
****************************************/
Now, I add a single record with a NULL value in the [Value] column and repeat the aggregation. While the result is the same, we have a warning in the “Messages” tab.
INSERT INTO [dbo].[NULLAggregation] ([Value], [ValueString])
VALUES ( NULL, 'One');
GO
SELECT SUM([na].[Value]) AS [SumOfValues]
FROM [dbo].[NULLAggregation] AS [na];
GO
/****************************************
RESULT
-----------
SumOfValues
-----------
33
Warning: Null value is eliminated by an aggregate or other SET operation.
****************************************/
The reason for the warning
To begin – this is just a warning and not an error. If your script/job is failing it is probably failing due to some other data condition OR as an indirect result of operating on NULL values.
The warning simply suggests that an aggregation operation is being done on a NULL value. If no NULL values are present in the dataset being evaluated, then the warning is not encountered.
Solutions
The important thing is to consider what is important for the business/domain.
- If processing on NULL values are okay for the business/domain, then one of the following two (2) workaround can be applied:
- The warning can either be ignored OR
- Use the “SET ANSI_WARNINGS OFF” option for your query/procedure
- If processing on NULL values is not acceptable for your business/domain, then
- Either use a simple WHERE clause to remove the records with NULL values from the aggregation
- Use input validations in the application code and NOT NULL checks in the database to stop the NULL values from being entered by the users
NOTE: If you do decide to use the “SET ANSI_WARNINGS OFF” option, please do so with caution. It can have unintended consequences with string operations as well (https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-warnings-transact-sql?view=sql-server-ver15)
I trust you found this post useful.
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 