Another day and another win against a difficult SQL Server Problem while I worked with my clients on Comprehensive Database Performance Health Check. The best part of my job is that I get to work with different performance problems every time with new clients. Recently during the consulting engagement, I needed to create a query that Lists All the Nullable Columns in Database, which I am sharing for all of you here.
Real-World Scenario
Here is what happened at my client’s place. After going through the scientific checklist of all the performance tuning activity, I realized that my client has still some performance issues in some of the queries. This is the time when we started to look deeper into the query patterns and anti-patterns and quickly realized that this was due to some of the columns which contained NULL values. Ideally, the column with NULL value is not any trouble at all. However, the client had pretty much kept many columns NULLable and due to lack of the data now those columns contained a NULL value.
When any column contains a NULL value, it is really difficult for a developer to write a query on the top of it. When you have to mention the column in the WHERE clause which contains null, you have two options: 1) Use a function ISNULL on the nullable column and 2) Use an additional OR condition to check the NULLABLE variables like Column IS NULL.
In the both the above scenario, we face performance troubles due to statistics skewed and table scanned due to function on the table columns. There are many ways to fix the issue but the best is to change the column to non-nullable and populate either zero (0) or an empty string.
In my client’s case, we identified 6 important columns from their query patterns and changed them so they can contain a non-null value. Right after that we fixed our query and removed the additional check for the NULL value, this immediately improved the query performance over 600% times.
Happy clients make me very happy as well.
List All the Nullable Columns in Database
SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, c.name AS ColumnName, ty.name AS DataType, c.max_length MaxLength, c.precision [Precision] FROM sys.tables AS t INNER JOIN sys.columns AS c ON t.object_id = c.object_id LEFT JOIN sys.types AS ty ON c.user_type_id = ty.user_type_id WHERE c.is_nullable = 1 ORDER BY SchemaName, TableName, ColumnName GO
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
Microsoft provide another view which is easier to use:
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE = ‘YES’
Alternatively Microsoft provide some simple to use views that will do the same job:
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE c.IS_NULLABLE = ‘YES’
ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME;
To get nullable columns
instead of “WHERE c.is_nullable = 0” it should be “WHERE c.is_nullable = 1”, right?
Fixed and you are correct.