What is NOT NULL Constraint? – Interview Question of the Week #073

Question: What is a NOT NULL constraint?

Answer: It requires a column to contain a value for every row. SQL Server rejects an INSERT or UPDATE that would put NULL in that column.

While working with a customer on performance tuning, I found several important columns containing missing data even though the application expected values. We corrected the data, made those columns NOT NULL, and made other changes that improved the workload. The original story is not a claim that NULL is inherently slow or that NOT NULL alone solved the performance problem. It made the table’s rules match what the application needed.

You can declare NOT NULL when creating a table, or use ALTER COLUMN later. The same small example demonstrates both, including the problem that existing NULLs cause:

IF OBJECT_ID('tempdb..#SqlaNotNull37392') IS NOT NULL
    THROW 50001, 'The sample table already exists in this session.', 1;
CREATE TABLE #SqlaNotNull37392 (ID int NULL, ColSecond int NOT NULL);
BEGIN TRY
    INSERT #SqlaNotNull37392 VALUES (1, NULL);
END TRY
BEGIN CATCH
    SELECT N'NOT NULL at creation' AS Test, ERROR_NUMBER() AS ErrorNumber;
END CATCH;
INSERT #SqlaNotNull37392 VALUES (NULL, 10);
BEGIN TRY
    ALTER TABLE #SqlaNotNull37392 ALTER COLUMN ID int NOT NULL;
END TRY
BEGIN CATCH
    SELECT N'Existing NULL prevents alteration' AS Test, ERROR_NUMBER() AS ErrorNumber;
END CATCH;
-- Correct the intentionally missing test value before changing the definition.
UPDATE #SqlaNotNull37392 SET ID = 1 WHERE ID IS NULL;
ALTER TABLE #SqlaNotNull37392 ALTER COLUMN ID int NOT NULL;
SELECT name, is_nullable FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('tempdb..#SqlaNotNull37392') ORDER BY column_id;
DROP TABLE #SqlaNotNull37392;

The first attempted INSERT fails because ColSecond is already NOT NULL. ID initially allows NULL, so the next row is legal. Changing ID to NOT NULL fails until that existing row is corrected. Finally, is_nullable is zero for both columns.

In a real table, decide what missing values mean before replacing them. Zero and an empty string are values, not NULL, and a default does not make an explicitly supplied NULL valid. ALTER COLUMN must repeat the intended data type and may require an expensive operation and locks, so review dependencies and the maintenance approach for a large table.

NOT NULL provides integrity, not uniqueness. Add a primary key or unique constraint when values also need to identify a row uniquely. Use the rule because the data requires it, then measure any performance effect on the actual queries.

Reference: ALTER COLUMN and nullability.

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