Forcing default values on a nullable columns

When we add a new column to an existing table that contains data we need to think about if we need to populate the data in this column for the existing rows.

If the column should not allow NULL then we need to supply a default for the new column (or set it to allow NULL, backfill it, then change to not allow NULL).

We can add a default constraint to a column that allows NULL but the default is not applied unless we specifically say to set it

Here’s a demonstration of working with defaults on nullable columns.

First let’s create a table as follows…

DROP TABLE IF EXISTS dbo.WithValuesTest;
GO

CREATE TABLE dbo.WithValuesTest
(
    Id INT NOT NULL IDENTITY PRIMARY KEY,
    FullName VARCHAR(100) NOT NULL
);
GO

INSERT INTO dbo.WithValuesTest
(
    FullName
)
VALUES
    ('Leslie Tambourine'),
    ('Linda Pencilcase'),
    ('Sally Carpets'),
    ('Andy Anchovie'),
    ('Ronnie Clotheshorse');
GO

SELECT *
FROM dbo.WithValuesTest

This gives us the following data…
DefaultNULL01

If we try to add a new not nullable column to this table this errors because there is no data to add into the new column

ALTER TABLE dbo.WithValuesTest ADD DateOfBirth DATETIME2(0) NOT NULL;
Msg 4901, Level 16, State 1, Line 86

ALTER TABLE only allows columns to be added that…

  1. can contain nulls, or
  2. have a DEFAULT definition specified, or
  3. the column being added is an identity or timestamp column, or
  4. the table must be empty

The column DateOfBirth cannot be added to the non-empty table WithValuesTest because it does not satisfy these conditions.

However, it we specify a default it will work…

ALTER TABLE dbo.WithValuesTest ADD DateOfBirth DATETIME2(0) NOT NULL CONSTRAINT DF_DOB DEFAULT '19000101';

SELECT *
FROM dbo.WithValuesTest

DefaultNULL02

To continue let’s drop this new column

ALTER TABLE dbo.WithValuesTest DROP CONSTRAINT IF EXISTS DF_DOB;
ALTER TABLE dbo.WithValuesTest DROP COLUMN IF EXISTS DateOfBirth;

Now let’s try to create the same column with the same default but now allow the column to be nullable

ALTER TABLE dbo.WithValuesTest ADD DateOfBirth DATETIME2(0) NULL CONSTRAINT DF_DOB DEFAULT '19000101';

SELECT *
FROM dbo.WithValuesTest;

DefaultNULL03

As you can see, unlike when the column didn’t allow NULL, the default value has not been used.

Let’s drop the column again and this time create it with the WITH VALUES clause…

ALTER TABLE dbo.WithValuesTest ADD DateOfBirth DATETIME2(0) NULL DEFAULT '19000101' WITH VALUES;

SELECT *
FROM dbo.WithValuesTest;

DefaultNULL04

To complete the demo let’s now try and add some new rows.

If we don’t include the DateOfBirth column in the insert list then the default is used…

INSERT INTO dbo.WithValuesTest (FullName)
VALUES ('Bernard Seesaw');

DefaultNULL05

Suppose our code needs to insert the date of birth if it’s supplied but should use the default value if it’s not supplied, i.e. if it’s NULL.

If we just try a straight insert using the NULL value then NULL is what is put into the table.

INSERT INTO dbo.WithValuesTest (FullName, DateOfBirth)
VALUES ('Lizzie Onion', '19830402'),
('Terence Artichoke', NULL);

DefaultNULL06

We can specify DEFAULT instead of NULL and it will use the default value on the insert

INSERT INTO dbo.WithValuesTest (FullName, DateOfBirth)
VALUES ('Mavis Breadbin', DEFAULT);

DefaultNULL07

The DEFAULT value can be used in an UPDATE statement as well…

UPDATE dbo.WithValuesTest
SET DateOfBirth = DEFAULT
WHERE FullName = 'Terence Artichoke'

DefaultNULL08

 

Advertisements

Partitioning Series

I’ve now written a few different posts about various parts of partitioning so thought I would put them all together here in a single series.

  1. Introduction to Partitioning – this post covers how to set up a table with multiple partitions using a partition scheme and function and briefly touches on partition elimination to help reduce the amount of data being read in a query.
  2. More Partitioning – this covers partition elimination in more detail and includes tips to make sure the optimiser knows it can eliminate certain partitions. It also covers the $PARTITION function.
  3. Using $PARTITION to find the last time an event occured – this post demonstrates a real life scenario where I’ve been able to use the $PARTITION function to speed up a query.
  4. 70-761 Exam Prep: Miscellaneous T-SQL – Part 1 – section 3 of this post covers truncating partitions which is new functionality available in SQL Server 2016.
  5. Modifying Partitions – this post shows how to add and remove partitions from an existing partition function.