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)
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
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 ?