SQL SERVER – Adding Values Containing NULLs

Today we will see a very simple blog post about Adding Values Containing NULLs. During the consulting engagement, Comprehensive Database Performance Health Check my client asked me if I know any way to optimize the code where they were adding simple column values containing Nulls.

When I looked at the code, I personally found it pretty straight forward and I could not give any other optimization suggestions. However, one thing which technology has taught me is not to afraid of help and opinion.

So here it goes dear readers, please guide me if there is any better way to add column values. Before we look at the code, let us look at the current table structure and expected results.

-- Create Table
CREATE TABLE MyTestTable
(
     ID VARCHAR(2)
    ,Col1 INT
    ,Col2 INT
    ,Col3 INT
)
GO

-- Insert Values
INSERT INTO MyTestTable 
VALUES
	('a',1,2,NULL),
	('b',1,2,3),
	('c',NULL,NULL,3),
	('d',NULL,NULL,NULL),
	('e',1,NULL,3)
GO
GO

-- Select the data
SELECT * 
FROM MyTestTable
GO

The result of the query above is given in the image above as the Current Table. Now let us add the data of the column and we are going to use the simple operator of PLUS (+) as well as function ISNULL which actually will check if the column value is NULL then will convert it to zero.

SELECT ID,
ISNULL (Col1,0)+ ISNULL(Col2,0)+ ISNULL(Col3,0)
	AS Addition
FROM MyTestTable
GO

When I ran the query above it gave us a result which is similar to the expected results in the image above. I guess, this is the simple solution and I do not have any better way to do the additional.

If you know any alternate way or better way, just leave a comment and I will be happy to blog about it with due credit to you. You can always reach out to me on Twitter for further conversation.

Reference: Pinal Dave (https://blog.sqlauthority.com)