Nullable Columns in a Database: List Them and Count NULLs

Nullable columns in a database are the first place to look when queries fill up with ISNULL. A catalog query lists them. A second script counts the NULLs, and the count tells you what to do next.

Why Nullable Columns Cost You

Nullable columns in a database force every query on them to deal with NULL. A developer wraps the column in ISNULL, or adds an OR condition for the NULL case. A function on a column in a WHERE clause keeps SQL Server from seeking an index on it. The extra condition also skews the estimates.

In one client review, the team had left many columns nullable, and over time those columns filled with NULLs. Six important columns became NOT NULL, with zero or an empty string where the data was missing. The extra checks left the queries, and the client reported an improvement of over 600 percent on one query. The first step is a list.

Build a Small Shop

The script creates a database named NullableListDemo with a customers table, an orders table and a view. Several columns allow NULL on purpose. Run it on a test server.

IF DB_ID(N'NullableListDemo') IS NULL CREATE DATABASE NullableListDemo;
GO
USE NullableListDemo;
GO
DROP VIEW IF EXISTS dbo.OpenOrders;
DROP TABLE IF EXISTS dbo.Orders, dbo.Customers;
CREATE TABLE dbo.Customers (
    CustomerID int NOT NULL PRIMARY KEY,
    FullName nvarchar(80) NOT NULL,
    Phone varchar(20) NULL,
    Email nvarchar(120) NULL,
    Notes nvarchar(max) NULL,
    LoyaltyPoints int NULL,
    PhoneDigits AS (CONVERT(varchar(20), REPLACE(Phone, '-', '')))
);
CREATE TABLE dbo.Orders (
    OrderID int NOT NULL PRIMARY KEY,
    CustomerID int NOT NULL,
    ShippedOn date NULL,
    Discount decimal(5,2) NULL,
    Remarks varchar(200) NULL
);
INSERT INTO dbo.Customers (CustomerID, FullName, Phone, Email, Notes, LoyaltyPoints)
VALUES (1, N'Maya Collins', '503-555-0101', N'maya@example.com', NULL, 120),
       (2, N'Leo Brennan', NULL, N'leo@example.com', NULL, 40),
       (3, N'Priya Shah', '303-555-0133', N'priya@example.com', NULL, 0),
       (4, N'Noah Kim', NULL, N'noah@example.com', NULL, 75),
       (5, N'Sam Rivera', '312-555-0188', N'sam@example.com', NULL, 15),
       (6, N'Dana Cruz', NULL, N'dana@example.com', NULL, 60);
INSERT INTO dbo.Orders (OrderID, CustomerID, ShippedOn, Discount, Remarks)
VALUES (1, 1, '2026-09-01', 0.00, NULL),
       (2, 1, '2026-09-03', 5.00, NULL),
       (3, 2, NULL, 0.00, NULL),
       (4, 3, '2026-09-04', 0.00, NULL),
       (5, 4, NULL, 10.00, NULL),
       (6, 5, '2026-09-06', 0.00, NULL),
       (7, 6, NULL, 0.00, NULL),
       (8, 3, '2026-09-08', 2.50, NULL);
GO
CREATE VIEW dbo.OpenOrders
AS
SELECT OrderID, CustomerID, ShippedOn FROM dbo.Orders WHERE ShippedOn IS NULL;

List the Nullable Columns

This query finds the nullable columns in a database. It reads sys.columns for every user table where is_nullable is 1. It formats the data type with its length. It also flags computed columns, because a computed column can be nullable too.

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName,
       t.name AS TableName,
       c.name AS ColumnName,
       CASE WHEN ty.name IN (N'varchar', N'char', N'varbinary', N'binary')
                 THEN ty.name + N'(' + IIF(c.max_length = -1, N'max', CONVERT(nvarchar(10), c.max_length)) + N')'
            WHEN ty.name IN (N'nvarchar', N'nchar')
                 THEN ty.name + N'(' + IIF(c.max_length = -1, N'max', CONVERT(nvarchar(10), c.max_length / 2)) + N')'
            WHEN ty.name = N'decimal'
                 THEN ty.name + N'(' + CONVERT(nvarchar(10), c.precision) + N',' + CONVERT(nvarchar(10), c.scale) + N')'
            ELSE ty.name
       END AS DataType,
       c.is_computed AS IsComputed
FROM sys.tables AS t
JOIN sys.columns AS c ON c.object_id = t.object_id
JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
WHERE c.is_nullable = 1
ORDER BY SchemaName, TableName, c.column_id;

Eight columns allow NULL. The primary keys don’t appear, because a key column can’t be NULL. The computed column PhoneDigits is nullable because its source column is.

The INFORMATION_SCHEMA Version

A shorter query reads INFORMATION_SCHEMA.COLUMNS. It is easy to remember, and other database products offer it too.

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE = 'YES'
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

It returns nine rows, one more than the catalog query. The extra row is OpenOrders.ShippedOn, a column of the view. The view lists columns of its own, and INFORMATION_SCHEMA includes them. Use this form when you want views too. Use sys.columns when you need the computed flag or exact sizes. Both queries show only the objects that your login has the right to see.

Count the NULLs in Each Column

A list shows what is allowed. It does not show what is stored. The next script builds one UNION ALL query from the list. For each nullable column it counts all rows and the rows that hold a value. The difference is the NULL count. The count script needs SQL Server 2017 or later (STRING_AGG).

DECLARE @union nvarchar(max) = (
    SELECT STRING_AGG(CONVERT(nvarchar(max),
               N'SELECT N' + QUOTENAME(s.name + N'.' + t.name, '''') + N' AS TableName, N'
             + QUOTENAME(c.name, '''') + N' AS ColumnName, COUNT(*) AS TotalRows, COUNT('
             + QUOTENAME(c.name) + N') AS FilledRows FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)),
           N' UNION ALL ') WITHIN GROUP (ORDER BY s.name, t.name, c.column_id)
    FROM sys.tables AS t
    JOIN sys.schemas AS s ON s.schema_id = t.schema_id
    JOIN sys.columns AS c ON c.object_id = t.object_id
    WHERE c.is_nullable = 1
);
DECLARE @sql nvarchar(max) = N'SELECT TableName, ColumnName, TotalRows, FilledRows,
       TotalRows - FilledRows AS NullRows,
       CASE WHEN TotalRows = 0 THEN N''Empty table''
            WHEN FilledRows = 0 THEN N''All NULL''
            WHEN FilledRows = TotalRows THEN N''No NULLs''
            ELSE N''Mixed'' END AS Verdict
FROM (' + @union + N') AS x
ORDER BY TableName, ColumnName;';
EXEC (@sql);

Read the verdict column. No NULLs means the column allows NULL but never uses it. That column is a candidate for NOT NULL. All NULL means nobody ever filled it. That column is a candidate for removal, after you check the code that reads it.

Mixed needs a decision. The column holds both values and NULL, and NULL means something. ShippedOn is NULL for an order that has not shipped, and that is information.

Run the Count With Care

The count script reads every table once for each nullable column, inside one statement. On a small database that costs nothing. On a large one, run it in a quiet hour, or limit the list to one schema first.

Decide What Replaces a NULL

Don’t change a column to NOT NULL until you know what goes in its place. A discount of 0.00 means no discount, so it fits. A phone number of an empty string means nothing, and it hides the difference between unknown and none. The post on Change Column From NULL to NOT NULL in SQL Server covers the steps. A default constraint on the new NOT NULL column helps. Later inserts then succeed without bringing the old problem back.

Should NULL Disappear?

You could argue that NULL is the honest answer for unknown data and should stay. It should. A shipping date that is NULL is correct until the parcel ships. The goal is not zero NULLs. The goal is that every nullable column has a reason.

What to Remember

List the nullable columns in a database, then count their NULLs. Columns with no NULLs are the cheap wins. Columns that are always NULL are clutter. Mixed columns stay nullable, and the queries that read them handle NULL on purpose. Save the result before you change anything, so you can compare it after. Run the count again later to confirm that nothing slipped back in.

The scripts only read, apart from the demo tables. When you finish, drop the demo database.

USE master;
GO
IF DB_ID(N'NullableListDemo') IS NOT NULL
BEGIN
    ALTER DATABASE NullableListDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE NullableListDemo;
END;

A nullable column is not a flaw, it is a promise that someone has to keep.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.