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?

What is NOT NULL Constraint? - Interview Question of the Week #073 notnull-800x415

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)

, , ,
Previous Post
SQL Domain Controller – Interview Question of the Week #072
Next Post
Sort Query Using Dynamic Variables Without EXEC – Interview Question of the Week #074

Related Posts

2 Comments. Leave new

  • How can a column with lot of NULL values cause performance problems ? And how did you identify that NULL values did cause the problem? Waiting for your next blog post on this topic to know how you guys fixed it

    Reply
  • the only problem is maybe ‘bad’ other check constraint which doesn’t consider NULL. Some tables need to have NULL, which means that there is no data (no answer in surveys), so my question is same How can a column with lot of NULL values cause performance problems ?

    Reply

Leave a Reply

Menu