SQL, SQL Server, SQL Tips and Tricks
Working with NULL’s are always a challenge – not many developers understand how NULL works sometimes. In a related note, long back I wrote below blog in which I explained two set options Quoted Identifier and ANSI NULL.
SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation
In future blogs, I would try to explain another one called ANSI_WARNINGS. Let’s look at the various warnings which can be suppressed when working with NULL.
Warning: Null value is eliminated by an aggregate or other SET operation
Let us run below script in SQL Server Management Studio to see the effect.
SET NOCOUNT ON
DECLARE @ItemSale TABLE (ID INT, Qty INT, Price MONEY)
INSERT INTO @ItemSale (ID, Qty, Price) SELECT 1, 25, 100
INSERT INTO @ItemSale (ID, Qty, Price) SELECT 2, NULL, 200
INSERT INTO @ItemSale (ID, Qty, Price) SELECT 3, 5, NULL
SELECT SUM(Qty) 'Sum - 1' FROM @ItemSale WHERE ID IN (1,3)
-- no warning
SELECT SUM(Price) 'Sum - 2' FROM @ItemSale WHERE ID IN (1,3)
-- Warning: Null value is eliminated by an aggregate or other SET operation.
SELECT AVG(Qty) 'Avg' FROM @ItemSale WHERE ID IN (1,2)
-- Warning: Null value is eliminated by an aggregate or other SET operation.
Here is the output
As the error 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
Here is the output after adding the set option:
First of all we should remember that default value of the setting is OFF, which is actually good. In subsequent blogs, we would cover an error which can be suppressed using same set option. Tuning it ON can be dangerous as well. Stay tuned!
What would be interesting is if anyone out there has turned this setting ON deliberately. Is there a scenario where you found it useful? Let me know.
Reference:Pinal Dave (https://blog.sqlauthority.com)
SQL Error Messages
SQL SERVER – Maximum Columns per Primary Key – Fix : Error : Msg 1904, Level 16, The index on table has column names in index key list. The maximum limit for index or statistics key column list is 16
SQL SERVER – Availability Group Missing or Deleted Automatically?
SQL SERVER – Msg 0, Level 11 – A Severe Error Occurred on the Current Command. The results, if Any, Should be Discarded
39 Comments. Leave new
December 5, 2019 10:11 pm
The logical example case for when you might think you want to ignore the warning: Let’s say you have a list of 100 companies in the database. Some of them may have insurance policies. Those that have insurance have a “deductible” value, those that don’t have insurance have a NULL deductible value. This is important because a deductible of “0” is NOT at all the same thing as not having insurance with “no” deductible since there is not insurance. Some may HAVE insurance with a 0 deductible. You want to know the average deductible value for those that DO have insurance. This is easy by just doing AVG(deductible) and it does exactly what you want, counting only those that DO have insurance — but it emits a pesky warning that in this case is not actually a problem. You don’t want to count those for the denominator that don’t have insurance, so using a coalesce is inappropriate and not needed. I will admit I don’t know as I write what the aggregate function would do if there are all nulls, but I think it would probably return null as the result without an error — that would be fine and is informative. IMO It could be nice to “dissect” this warning out of the system for a brief time while leaving all other warnings intact, but apparently you can’t. On the other hand, I understand the warning since people may not know that’s how the aggregate function works. I would say for most cases the solution may be to use a WHERE that explicitly eliminates the NULL values, for example: “WHERE deductible IS NOT NULL”. This way you are telling the system you “know” what you want and it won’t warn you, because you eliminated the null values yourself — the aggregate function never sees them and there is no need to warn you. The time when this may not work is when you have multiple columns where you’d like to get an aggregate value from them but there will be mixed nulls on a per row basis. You can’t eliminate the row and you’d like to have the aggregate function handle that, simply not counting the null entries. In this case, maybe, you could be forced to consider SET ANSI_WARNINGS OFF, for only a brief time. It just seems like a poor option because you can’t selectively suppress the single warning, so I’d want to look harder for another solution if possible. It’s too bad we can’t have some kind of a query hint for that.Reply
December 18, 2019 7:00 am
Hi Dave! We met last year. Your last sentence regarding turning it on deliberately – although not explicitly turning it on, you can’t set ANSI_WARNINGS to OFF if you are using heterogeneous queries.Reply
August 17, 2020 6:49 amReply
Interesting… I’ve mostly taken the ANSI_WARNINGS setting for granted.
While the databases may default to having this setting OFF, SSMS defaults connections to having ther setting ON. Having it ON seems like a good thing to me because, as it says in Books Online…
“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.”
It also states that, luckily (or a lot of what we do would fail)…
“Clients, such as the SQL Server Native Client ODBC driver, the SQL Server Native Client OLE DB Provider for SQL Server, and the Microsoft JDBC Driver for SQL Server automatically set ANSI_WARNINGS to ON with a connection flag.”
So you ask “What would be interesting is if anyone out there has turned this setting ON deliberately. Is there a scenario where you found it useful?”. Considering that it defaults to on in almost everything that accesses a database and has some seriously bad things happen when you turn it off, it would be more interesting to ask if anyone has explicitly turned it off in all those other places.
As for me, I’m very thankful that it is set to ON in all those other places especially since (but not limited to) we have a shedload of computed columns in our databases. ;-)
June 29, 2021 10:32 pm
I Would like to raise an error when this occurs. In my case, I would need to fix the missing data. Is this possible?Reply
January 27, 2022 5:12 am
I’ve always wanted to better understand if there was a way to know which aggregated column had the NULL. Is there is a way to get that from the ANSI WARNING somehow? I know I can remove all of the aggregation functions one-by-one but I was wondering if there is a better way.Reply
November 21, 2022 4:06 pm
on counting, NULL values are not counted, thus it could be very handy if you let the field as isReply
COUNT(DISTINCT CASE WHEN expr THEN field ELSE NULL END)
« Older Comments
Leave a Reply
Pinal Daveis an SQL Server Performance Tuning Expert and independent consultant with over 17 years of hands-on experience. He holds a Masters of Science degree and numerous database certifications.
Pinal has authored 13 SQL Server database books and 45 Pluralsight courses. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,500 database tech articles on his blog at https://blog.sqlauthority.com.
Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. If you need help with any SQL Server Performance Tuning Issues, please feel free to reach out at email@example.com.
Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2).
Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long term multi-channel campaigns to drive leads for their sales pipeline.
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
- Warning: Null value is eliminated by an aggregate or other SET operation.
- select sum(COALESCE(i, 0))
- select count(i)
- select count(*)
- select count(1)
- SET ANSI WARNINGS OFF.
Nulls and Aggregate Functions. If an aggregate function against a column that contains nulls is executed, the function ignores the nulls. This prevents unknown or inapplicable values from affecting the result of the aggregate.How do you handle NULL values in aggregator transformation? ›
treats null values as nulls in aggregate functions. If you pass an entire port or group of null values, the function returns NULL. However, when you configure the PowerCenter Integration Service, you can choose how you want it to handle null values in aggregate functions.How to exclude NULL values in SQL from the the WHERE clause? ›
You can use a WHERE clause to retrieve rows that contain a null value in a specific column. You can also use a predicate to exclude null values. You cannot use the equal sign to retrieve rows that contain a null value. (WHERE column-name = NULL is not allowed.)How do set operators handle NULL values? ›
When comparing rows, set operators treat NULL values as equal to each other. In contrast, when NULL is compared to NULL in a search condition the result is unknown (not true).How do you resolve NULL indicator? ›
If the table column is defined as NOT NULL (with no default) and if we try to insert a null value we get this error. Resolution: This should be resolved by making sure that the inserted value is not null.Which aggregate function doesn t ignore nulls? ›
Except COUNT function, all the group functions ignore NULL values.Does aggregate function COUNT NULL values? ›
Why Use Aggregate Functions? An aggregate function in SQL performs a calculation on multiple values and returns a single value. SQL provides many aggregate functions that include avg, count, sum, min, max, etc. An aggregate function ignores NULL values when it performs the calculation, except for the count function.How do you handle NULL values in WHERE clause? ›
Null values can be used as a condition in the WHERE and HAVING clauses. For example, a WHERE clause can specify a column that, for some rows, contains a null value. A basic comparison predicate using a column that contains null values does not select a row that has a null value for the column.Do aggregate functions ignore NULL values? ›
An aggregate function performs a calculation on a set of values, and returns a single value. Except for COUNT(*) , aggregate functions ignore null values.
- Group by simple columns.
- Use sorted input.
- Use incremental aggregation.
- Filter data before you aggregate it.
- Limit port connections.
By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.How do you add a not NULL condition in WHERE clause? ›
Let's look at an example of how to use the IS NOT NULL condition in a SELECT statement in SQL Server. For example: SELECT * FROM employees WHERE last_name IS NOT NULL; This SQL Server IS NOT NULL example will return all records from the employees table where the last_name does not contain a null value.How do I not exclude NULL values in SQL? ›
By far the simplest and most straightforward method for ensuring a particular column's result set doesn't contain NULL values is to use the IS NOT NULL comparison operator.How to remove NULL values in case statement in SQL Server? ›
- Using ISNULL() function - TSQL specific function.
- Using COALESCE() function - ANSI SQL standard function.
- Using a CASE statement.
In the create procedure statement, you can declare null as the default value for individual parameters: create procedure procedure_name @param datatype [ = null ] [, @param datatype [ = null ]]... If the user does not supply a parameter, Adaptive Server executes the stored procedure without displaying an error message.What is the null safe operator in SQL Server? ›
NULL -safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL , and 0 rather than NULL if one operand is NULL . The <=> operator is equivalent to the standard SQL IS NOT DISTINCT FROM operator.Which function is used to handle null values? ›
You can use the null handling functions in the Transformer stage to handle nulls in derivations. If you use input columns in an output column expression, a null value in any input column causes a null to be written to the output column. You can, however, use the null handling functions to handle nulls explicitly.Does SQL AVG ignore NULLs? ›
SQL Server AVG() Function
The AVG() function returns the average value of an expression. Note: NULL values are ignored.
A primary key defines the set of columns that uniquely identifies rows in a table. When you create a primary key constraint, none of the columns included in the primary key can have NULL constraints; that is, they must not permit NULL values.
Yes, they do. This means that if you had the numbers 1,2,3,null,null then Looker's median and average would both return 2 rather than 1 or 1.25 .How do aggregates function when NULLs are present? ›
Aggregate Functions and NULL Values
If all of the values passed to the aggregate function are NULL, then the aggregate function returns NULL.
For example, avg() (average) can be computed by sum()/count(), where both sum() and count() are distributive aggregate functions. Similarly, it can be shown that min_N() and max_N() (which find the N minimum and N maximum values, respectively, in a given set) and standard_deviation() are algebraic aggregate functions.How are NULLs treated if they exist in grouping attributes? ›
How are NULLs treated if they exist in grouping attributes? They are separated into a group created for all tuples with a NULL value in grouping attribute. A query (SELECT statement) inside a query that can appear as part of a condition in the WHERE or HAVING clauses as well as in the FROM clause.How do you handle null exceptions? ›
- Making sure an object is initialized properly by adding a null check before referencing its methods or properties.
- Using Apache Commons StringUtils for String operations e.g. using StringUtils.
In the Power Query editor, head over to the View tab and check on “Column quality” under Data Preview. Here you will see a menu appear at the top of each column summarizing what percent of values are valid, contain formula errors, and are empty. And to Power Query, empty means null and null alone.How do you update a value to null in SQL? ›
You can use this query, to set the specific row on a specific column to null this way: Update myTable set MyColumn = NULL where Field = Condition. Here, the above code will set the specific cell to null as per the inner question (i.e. To clear the value from a cell and make it NULL).Do group functions ignore NULL values SQL? ›
In SQL Server, All Group functions ignore NULL values. For example: the average salary is calculated based on the rows in the table where a valid value is stored (the total salary divided by the number of employees receiving a salary).Why does WHERE not work with aggregate functions? ›
Aggregate functions are not allowed because the WHERE clause is used for filtering data before aggregation. So while WHERE isn't for aggregation, it has other uses. To filter data based on an aggregate function result, you must use the HAVING clause.Why should we avoid NULL values in SQL? ›
NULL values are used to indicate that you could have a value, but you don't know what that value should be yet. They are placeholders until you finally collect the data needed to fill the table field with a real value. You should never confuse NULL values for zeros or blank strings.
For ex source file has a million records...if you dont sort,for every individual group aggregator needs to scan entire set every time this will be burden on aggregator. Hence we use sorter to improve performance.Is Aggregator transformation connected or unconnected? ›
Aggregator transformation is an Active and Connected transformation used to perform aggregate calculations like averages, sums, counts, etc., on groups of data.What is the difference between Aggregator and expression transformation? ›
The Aggregator transformation behaves like the Expression transformation except you can configure the Aggregator transformation to perform calculations on a group of data. The Expression transformation returns results on a row-by-row basis.How do you make all columns not null in SQL? ›
To enforce NOT NULL for a column in SQL Server, use the ALTER TABLE .. ALTER COLUMN command and restate the column definition, adding the NOT NULL attribute.What function would you use to eliminate null values from a column? ›
As explained, the dropna() function is used to eliminate null values.Should you make every attribute in tables not null? ›
Why care if a column is or isn't nullable? The reason you need to specify this is because a relational database is designed to make it efficient to prevent bad data getting in. Constraints are the means of doing this. You must therefore use NOT NULL for all columns that cannot legitimately contain nulls.How do you check for null condition? ›
The result of a join of null with any other value is null. Because null values represent unknown or inapplicable values, Transact-SQL has no basis to match one unknown value to another. You can detect the presence of null values in a column from one of the tables being joined only by using an outer join.What is the not null condition in WHERE clause in SQL Server? ›
The SQL NOT NULL condition is used to test for a non-NULL value. It returns TRUE if a non-NULL value is found and FALSE otherwise. The SQL IS NULL condition is used to test for a NULL value (similar to the NULL definition above). It returns TRUE if a NULL value is found and FALSE otherwise.How do you avoid NULL values in select query? ›
You can use a WHERE clause to retrieve rows that contain a null value in a specific column. You can also use a predicate to exclude null values. You cannot use the equal sign to retrieve rows that contain a null value. (WHERE column-name = NULL is not allowed.)
ISNULL() function, CASE statement, COALESCE() function can be used to replace null values in a SQL Server table. To replace null with a specified replacement value, we can use any of the following: ISNULL() function. CASE statement.How to replace NULL values in select statement in SQL Server? ›
There are two ways to replace NULL with blank values in SQL Server, function ISNULL(), and COALESCE(). Both functions replace the value you provide when the argument is NULL like ISNULL(column, '') will return empty String if the column value is NULL.How to assign NULL value in case statement in SQL Server? ›
In using the ISNULL SQL function, you should start by selecting the MiddleName column using the SELECT statement. After that, you should use the ISNULL function to check the MiddleName column that's specified inside the parentheses and replace any NULL value in it with the 'Unknown' value.How do you replace NULL values in datasets? ›
Value counts for Embarked column
Null values in Embarked column are replaced by most frequent value using SimpleImputer. Using SciKit Learn SimpleImputer class we can also replace a null value with a custom value. Replacing all null values in Cabin column with an Unknown class.
There are two ways to replace NULL with blank values in SQL Server, function ISNULL(), and COALESCE(). Both functions replace the value you provide when the argument is NULL like ISNULL(column, '') will return empty String if the column value is NULL.How to remove NULL from hash set? ›
A HashSet , being a set, only contains one "copy" of any object, which also means that it can only contain one instance of null . Thus, you can just use HashSet. remove(null) .Which function is used to replace null values? ›
We can replace NULL values with a specific value using the SQL Server ISNULL Function. The syntax for the SQL ISNULL function is as follow. The SQL Server ISNULL function returns the replacement value if the first parameter expression evaluates to NULL.What is the alternative to returning null? ›
Several alternatives for returning null values include using null object reference types, a null object pattern, and a result type as the return type. Therefore, the recommendation is to return an empty value instead of a null to keep the code clean and error-free.How to replace null values in SQL using NVL? ›
The Oracle NVL() function allows you to replace null with a more meaningful alternative in the results of a query. The NVL() function accepts two arguments. If e1 evaluates to null, then NVL() function returns e2 . If e1 evaluates to non-null, the NVL() function returns e1 .How do you handle null variables? ›
- Don't Overcomplicate Things. ...
- Use Objects Methods as Stream Predicates. ...
- Never Pass Null as an Argument. ...
- Validate Public API Arguments. ...
- Leverage Optional. ...
- Return Empty Collections Instead of Null. ...
- Optional Ain't for Fields. ...
- Use Exceptions Over Nulls.
Remove Null from filter by Duplicating the Dimension
Therefore, duplicate the dimension (right-click on the dimension and 'Duplicate'). 2. Put the duplicate field to the filter shelf. Next select the values you want to hide – i.e. Null – and click exclude on the right of the filter box.
In SQL, we can use two functions to replace null entries with a default value. These functions are COALESCE() and ISNULL() . We'll use only the COALESCE() method in this Answer.How to replace character with null in SQL? ›
SQL Server Replace blank with null
In SQL Server, a blank or an empty expression is represented by two single quotes (”). Now to replace a blank value with NULL, we can use the Replace() function, which is used to replace character expression in a given string expression.
Comparing COALESCE and ISNULL
Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.
Unpivot and Pivot
The first method involves unpivoting and then pivoting the data. This works because when you unpivot, Power Query automatically removes any null values.
Duplicate values are not allowed in a HashSet. You can insert “null” values in a HashSet.