When DEFAULT doesn't default

I almost gave a wrong code review to a colleague:

It's better to use NOT NULL:

ALTER TABLE dbo.blah
ADD IsTransferred BIT NULL DEFAULT (0);

Is there a business or technical reason why IsTransferred should be nullable?

On the above DDL for IsTransferred, nullable or not nullable, IsTransferred defaults to 0, hence this is a superfluous code:

-- initialize new column, i.e., NULL columns, to "not transferred"
UPDATE dbo.blah
SET IsTransferred = 0
WHERE IsTransferred IS NULL ;


Further read: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-database-rsquo-s-black-hole.aspx

The code could be shortened and optimized but I'm not inclined to advise it; this query is just a one-time thing, no need to micro-optimize. The code is good enough

Spotted the wrong thing on the above advice? The above advice is correct if we are using PostgreSQL. However we are using SQL Server, the newly-added IsTransferred field will default to null if the field is nullable regardless of the above DDL indicating a default value of false(0).


So the UPDATE above is not a superfluous code, at least on SQL Server, the UPDATE is still needed despite the DEFAULT 0 clause. PostgreSQL (and other RDBMSes for that matter) is intuitive in this regard, it honors the DEFAULT clause regardless of the field is nullable or not.


Further read: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-database-rsquo-s-black-hole.aspx


PostgreSQL-related news: https://twitter.com/spolsky/status/433323487961178113



Happy Coding! ツ