Not Null Again
If you don’t allow NULL values in your database, just skip this editorial section of the newsletter. Still there? Today I wanted to talk about the use of NULL in your table definitions. Not about if you should use them or not, or even about how it changes your code. What I want to talk about is a nuance of SQL Server when it comes to defining tables using NULL capable columns.
First off, many versions of SQL Server maintain a bitmap for every row/column to identify those entries with NULL values, if null is allowed. There is no miracle in this information. However, be aware that there is overhead in the engine when your allow NULL values in your tables. It has to maintain the bitmap on every nullable column and row.
Secondly, if you are going to allow null values in your tables, and you are defining your tables using TSQL, be sure to explicitly specify that the column allows NULL or NOT NULL. If you don’t specify your explicit intention, then the default nullable setting depends on a number of factors.
I didn’t know that there were different factors. I thought that the SQL Server engine defaulted to allowing NULL if you didn’t explicitly specify nullability. I have a habit of always specifying NULL in my TSQL because it communicates clearly what my intention is, not the behavior of the engine or other factors.
Recently I have been reading a section in an old Kalen Delany SQL Server 2005 Storage Engine book and came across a section about NULL. I’ve had this book for years, and always skipped over this section because I have already determined my position and practices built around using NULL. Today I decided to read and was surprised to find that column NULL values default in the SQL Server engine to NOT NULL . However, the behavior I have experienced has been the opposite.
Kalen shares that the behavior may display itself differently over different database connectivity techniques such as ODBC or SQL Native client libraries. That is because those libraries have default settings for the ANSI configuration settings in SQL Server. The default settings for those connection libraries result in a default null definition of NULL.
If you choose, you can set the ANSI configuration settings to different values and change the default value. At the end of the day, I think it is much simpler to specify you intention for NULL on each column when you create a table, temp table, or memory table variable. Then you don’t have to know the nuances of what does what when inside a SQL Engine. Moreover, if you use the code, or translate it to another SQL engine, there is no question as to the intent.
How are you handling NULLS in your data engine of choice? Do you find it simpler to speicify the intention for NULL values? Share your practices with us by writing to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
How to start SQL Server without a MASTER database
SQL Server depends on the MASTER and RESOURCE databases for critical information required to start the instance. Without these databases your SQL SERVER will not start. We recently had an outage where everything on the RAID-5 disk drive array containing the SQL Server system (master, resource, model and MSDB) and USER databases was lost due to hardware failure. After the hardware issue was resolved, the data on the drive array was restored from backups. This restored all files on the drive including the SQL Server database and log backups. However the actual database data and log files (MDF, NDF and LDF) were not restored because the backup software did not include “in-use” files like database files during the backup operation. Consequently there were no backups of the actual system and USER database files.
Featured White Paper(s)
All-At-Once Operations
Written by Itzik Ben-Gan with SolidQ
SQL supports a concept called all-at-onc… (read more)
Featured Script
AutomaticMechanismsCanBeSurprising.sql
Helper script for AutomaticMechanismsCanBeSurprising article… (read more)