Friday, August 1, 2014

SQL Server: How much of your data is NULL?

For a project I’m working on, I ‘inherited’ an existing database. Most of the columns where nullable so almost every field in our Entity Framework model was of type Nullable<T>. As the data itself is used to do calculations, all these extra null checks and ‘.Value’ everywhere made our code a lot harder to read and understand.

However while looking through the data in the database, I had the feeling that most columns should actually be not nullable. But I had to be 100% sure before I could make the switch.

Based on the following blog post(http://blogs.msdn.com/b/samlester/archive/2012/09/22/tsql-solve-it-your-way-finding-the-percentage-of-null-values-for-each-column-in-a-table.aspx), I created a small SQL script that returns the percentage of NULL values for all columns in a table:

This returns the following result:

image

By doing this, I could easily identify columns that possibly should be not nullable.

No comments: