intro
Let’s learn everything you need to know to deal with the NULL SQL Server value like a pro and avoid unexpected results.
When dealing with data in a database, it is common for some information to be missing or unknown. For this reason, SQL Server allows columns to be defined as nullable. The NULL SQL Server value acts as a special placeholder to represent the absence of data in a structured and meaningful way.
In this guide, you will explore what NULL means in SQL Server, why it is needed, and how to handle it effectively using various operators and functions.
Read this article to ensure that your knowledge of NULL values is not null!
What Does NULL Mean in SQL Server?
In SQL Server, NULL indicates data that is unknown, not applicable, or yet to be provided. For example, assume a column for a middle name is nullable. In this case, NULL values indicate that the person either does not have a middle name or this information has not been provided yet. Unlike zero or an empty string, NULL signifies the absence of data rather than an actual value.
The uncertainty associated with NULL values can be confusing and frustrating. Avoid that with the tips in our guide on how to effectively deal with NULL values.
How to Define a Nullable Column in SQL Server
Time to dig into the scenarios involving nullable columns in SQL Server.
Follow our blog for a similar guide on MySQL NULL values.
Creating a Table with Nullable Columns
To mark a column as nullable in SQL Server, you can add the NULL keyword in the column definition line of your CREATE TABLE, as in the example below:
1
CREATE TABLE Employee (
2
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
3
FirstName VARCHAR(50) NULL,
4
LastName VARCHAR(50) NULL,
5
BirthDate DATE NULL
6
);
At the same time, keep in mind that columns are nullable by default, unless:
Thus, the above query is equivalent to this SQL Server query:
1
CREATE TABLE Employee (
2
Id INT IDENTITY(1,1) PRIMARY KEY,
3
FirstName VARCHAR(50),
4
LastName VARCHAR(50),
5
BirthDate DATE
6
);
In both examples, the FirstName, LastName, and BirthDate columns can store NULL values.
Add a Nullable Column to an Existing Table
Similar to what was seen earlier, you can add a nullable column to an existing table with the ALTER TABLE statement below:
1
ALTER TABLE Employee
2
ADD Email VARCHAR(100) NULL;
The Employee table will now have a nullable Email field.
Change an Existing Column to Nullable
ALTER TABLE can also be used to update a query to make it nullable:
1
ALTER TABLE Product
2
ALTER COLUMN Price DECIMAL(10,2) NULL;
The Price column now accepts the NULL SQL Server value.
Verify a Column Is Nullable
To verify if a column is nullable in SQL Server, you can query system catalog views like INFORMATION_SCHEMA.COLUMNS or sys.columns. For an easier approach, use an SQL Server client like DbVisualizer.
DbVisualizer allows you to view the SQL DDL of a table with a single click. To use it:
Thanks to DbVisualizer, identifying nullable columns has never been easier!
Function and Operators For Dealing With NULL Values
Time to dig into the tools SQL Server offers to handle with NULL values.
IS NULL
The IS NULL SQL Server operator checks whether an expression evaluates to NULL.
Example:
1
SELECT *
2
FROM Employee
3
WHERE MiddleName IS NULL;
This sample query retrieves all employees who do not have a middle name.
IS NOT NULL
IS NOT NULL in SQL Server is the opposite of the IS NULL operator. That means it is used to check whether an expression does not evaluate to NULL.
Example:
1
SELECT *
2
FROM Employee
3
WHERE MiddleName IS NOT NULL;
This query retrieves all employees who have a middle name.
ISNULL
The ISNULL function replaces NULL values with a specified replacement value. It is often used to ensure that NULL values do not appear in query results where they might cause issues.
Syntax:
1
ISNULL(expression, replacement_value)
Example:
1
SELECT FirstName, ISNULL(MiddleName, '—') AS MiddleName, LastName
2
FROM Employee;
This query gets the first name and last name of an employee, replacing NULL middle names with text "—".
COALESCE
COALESCE is a function that returns the first non-NULL value from a list of expressions. It is helpful for handling multiple columns where one might be NULL, but another might have a value. Find out more details in our guide on SQL COALESCE.
Syntax:
1
COALESCE(expression_1, expression_2, ..., expression_n)
Example:
1
SELECT Id, COALESCE(WorkPhoneNumber, PersonalPhoneNumber) AS ContactNumber
2
FROM Employee;
This query gets the employee ID and the first non-NULL value among WorkPhoneNumber and PersonalPhoneNumber.
NULLIF
The NULLIF function returns NULL if the two specified expressions are equal. If they are not equal, it returns the first expression.
Syntax:
1
NULLIF(expression_1, expression_2)
Example:
1
SELECT Id, NULLIF(Price, 0) AS Price
2
FROM Product;
This query returns the Id and the Price, but if the Price is 0, it returns NULL instead.
Conclusion
In this guide, you saw what NULL means in SQL Server and what operators and functions the database offers to deal with it. You now know that NULL values in SQL Server are useful for representing missing, still-to-be-added, or unknown data.
As shown here, understanding whether a column is nullable in SQL Server is not always straightforward, but with DbVisualizer, these things take just a couple of clicks. In addition to supporting several DBMSs, this powerful database client offers advanced query optimization capabilities, ERD-like schema generation, and much more. Try DbVisualizer for free today!
FAQ
Can any SQL Server data type be nullable?
Yes, any SQL Server data type can be nullable. That means the database engine allows any data type to hold a NULL value, which indicates the absence of data. To define a column as nullable, specify the NULL keyword when creating or altering the column in a table.
What is the difference between NULL and UNKNOWN in SQL Server?
In SQL, NULL typically represents the absence of any value, indicating missing or undefined data. In SQL Server, UNKNOWN is not a data type but a possible result of logical operations involving NULL. For instance, in a comparison like NULL = NULL, the result is UNKNOWN because SQL cannot determine if two missing values are equal. Find out more in the official documentation.
How to test for NULL SQL Server values in a query?
To test for NULL values in a query, you need to use the IS NULL or IS NOT NULL SQL Server operators. For example, to find rows where a specific column has NULL values, the syntax is:
1
SELECT * FROM TableName
2
WHERE ColumnName IS NULL;
On the contrary, to fetch rows where a column does not have NULL values, you need to write something like:
1
SELECT * FROM TableName
2
WHERE ColumnName IS NOT NULL;
These operators effectively check for the presence or absence of NULL values.
Can NULL values be used as information to distinguish one row from another row in a table?
No, NULL values cannot be used to distinguish one row from another in SQL Server. In other words, distribution keys for partitioning and primary keys cannot be nullable. The reason is that the NULL SQL Server value is not considered equal to any other value, including another NULL. So, it cannot serve as a unique identifier or be used in primary keys or unique constraints.
How much disk space do NULL values take in SQL Server?
In SQL Server, the size of NULL values depends on the column type:
In addition to the space required to store a NULL value, there is also an overhead for defining a column as nullable. In particular, the database needs to reserve one extra bit per nullable column in each row. That bit is used to mark whether the value for that column is NULL or not. This is true for both fixed-width and variable-width fields.
