The value NULL in SQL Server cannot be fully discussed in only one post. So let’s start at the beginning and just work with a few variables in this post. NULL in SQL Server means “I don’t know”. It does not mean “zero” or “nothing”. To prove that it doesn’t mean zero, consider the code below and look at the results. The take away here is that you always need to stop and think carefully when working with NULL. This comes up in queries as well when data in columns may contain NULL.

DECLARE @myINT INT;
SELECT @myINT;
SELECT @myINT + 2;
SELECT @myINT / 0;

Here are the results in SSMS. I don’t know plus 2 still equals I don’t know. If you need to convert all nulls to zeros, have a look at the COALESCE function.

What about strings that are null? The same rules of I don’t know apply. What if you use a function like substring or left or right. The results are still NULL. What if you add a string to the end of it? You still get NULL. You still get NULL if you try to get the LEN or DATALENGTH of that string.

Numeric and Decimal

Numeric and decimal are similar and a numeric(5,2) data type says the total number of digits are 5 and there are 2 places to the right of the decimal. The maximum value would therefore be 999.99. If you try to store 1000 into this data type you get an overflow error.

select cast(1000 as decimal(5,2))
select convert(decimal(5,2), 1000)

Here are the results in SSMS.

Msg 8115, Level 16, State 8, Line 3
Arithmetic overflow error converting int to data type numeric.
Msg 8115, Level 16, State 8, Line 4
Arithmetic overflow error converting int to data type numeric.

try_cast and try_convert

These two functions were introduced in SQL Server 2012. Note that for some reason, try_cast may not be a recognized function by Intellisense. Don’t worry. It exists and is documented in Books Online.

select try_cast(1000 as decimal(5,2))
select try_convert(decimal(5,2), 1000)

Here are the results. Notice that NULL can be used to our advantage here instead of being a pain.

In the real world you may be processing a column of data and you don’t want an error. You prefer to have NULL instead. Here is a simple example of that query using try_convert().

select try_convert(decimal(5,2), [columnname])
from tblName

Tutorial

w3schools.com has a tutorial on SQL NULLs at SQL NULL Functions. This tutorial includes IFNULL(), ISNULL(), COALESCE(), and NVL() Functions. The SQL Server ISNULL() function lets you return an alternative value when an expression is NULL.