Join the result of a Temp column to a table SQL Server

Question

I am using SQL Server to create my database.

I want to add a column to my Table which would calculate the number of NULL values in each row, like this:

Column1 | Column2 | Column3 | Score
a       |   B     |   C     |   0
x       |   NULL  |   NULL  |   2

Currently, I have this:

Column1 | Column2 | Column3
a       |   B     |   C     
x       |   NULL  |   NULL  

I have created a new column called Score, and in order to calculate it, I have used:

SELECT
CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END + 
CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END +
CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END 
As TMP
FROM MyTable

That returns a column with all my lines and the Score for each line:

  |TMP
1 |0
2 |2

I would like to update the column Score in myTable with those values.

Thanks for your help.

Answer 1

You could use a computed column - a virtual column that is always computed with a given expression, and not stored on disk. This way, you avoid problems with data consistency. The syntax is easy:

CREATE TABLE myTab
(
    column1 datatype
  , column2 datatype
...
  , Score AS CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END +
       CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END +
       CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END 
);

In order to alter the existing table and add such a column, use:

 ALTER TABLE myTab ADD Score AS CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END +
       CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END +
       CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END 

Source: https://msdn.microsoft.com/en-us/library/ms188300.aspx