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

I am an independent database consultant and I focus on SQL Server Performance Tuning. The other day when I was working with my customer on a large project on performance tuning, we noticed a table which had created performance issues and it contains a huge amount of NULL data. In this blog post we will learn about NOT NULL constraint.

Technically NULL data should be not be part of the performance problem in most of the cases, but in this case we had a very different issue. A large amount of NULL value in multiple columns of their very important table was creating an execution plan which was not very effective in terms of performance. We finally identified columns which should have not allowed NULL value and created NOT NULL constraint. Finally, after few more changes to the table, we were able to get necessary performance improvement. I will write a detailed blog about what we did and how we got the necessary performance in future blog posts. However, one thing which we made sure to avoid NULL values in the column was to create NOT NULL Constraint on columns. Let us learn more about the same.

Question: What is NOT NULL Constraint?

Answer: NOT NULL is integrity CONSTRAINT. It does not allow creating of the row where column contains a NULL value.

To prevent nulls to be inserted in the database, table should have a NOT NULL constraint. Two ways NOT NULL constraint can be implemented with database.

1) Implement Constraint, when Table is created
2) Implement Constraint after Table is created

Following example demonstrates both the way to create NOT NULL constraints.

USE AdventureWorks
GO
-- NOT NULL Constraint, when Table is created
CREATE TABLE ConstraintTable
(ID INT, ColSecond INT NOT NULL)
GO
-- NOT NULL Constraint after Table is created
ALTER TABLE ConstraintTable
ALTER COLUMN ID INT NOT NULL
GO
--Clean Up
DROP TABLE ConstraintTable
GO

Reference: Pinal Dave (https://blog.sqlauthority.com)