Newly added XML column with default xml value always shows NULL

Question

I'm running the command

ALTER TABLE [DB].dbo.TABLEA ADD [Field1] [xml]  DEFAULT
CAST(N'<SettingValues xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://someurl.com/schemas.xsd" />' AS xml)

I was expecting a new column show up with the xml string but instead they are all showing NULL. Did I do something wrong?

I based my syntax on this Microsoft document resource.

You can test this out over at the SQLFiddle I created.

Answer 1

The default only applies to newly added rows. You will have to do an UPDATE to set the existing rows to the desired default.

Answer 2

When creating the new column you can use the SQL Server WITH VALUES syntax to have the default value set.

CREATE TABLE TableA
(
    ID INT IDENTITY (1,1),
    TestData VARCHAR(120)
);

INSERT TableA Select 'A';
INSERT TableA Select 'B';
INSERT TableA Select 'C';

SELECT 
    * 
FROM
    TableA;

Running the above produce the following output.

ID  TestData
1   A
2   B
3   C

We then add two new columns, Field1 and Field2 using the following syntax

ALTER TABLE TableA ADD [Field1] [xml]  DEFAULT
CAST(N'<SettingValues xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://someurl.com/schemas.xsd" />' AS xml) 


ALTER TABLE TableA ADD [Field2] [xml]  DEFAULT
CAST(N'<SettingValues xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://someurl.com/schemas.xsd" />' AS xml) WITH VALUES

The following desired result is obtained for the new column Field2 with the existing rows populated with the XMLstring while those for new column Field1 are Null.

ID  TestData    Field1  Field2
1   A   NULL    <SettingValues xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://someurl.com/schemas.xsd" />
2   B   NULL    <SettingValues xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://someurl.com/schemas.xsd" />
3   C   NULL    <SettingValues xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://someurl.com/schemas.xsd" />

Answer 3

If the column is set to NOT NULL then the default value is used even if WITH VALUES is not used.

ALTER TABLE TableA ADD [Field3] [xml]  NOT NULL DEFAULT
CAST(N'<SettingValues xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://someurl.com/schemas.xsd" />' AS xml)

From the docs:

When adding a column and a DEFAULT constraint, if the column allows NULLS using WITH VALUES will, for existing rows, set the new column's value to the value given in DEFAULT constant_expression. If the column being added does not allow NULLS, for existing rows, the column's value will always be set to the value given in the DEFAULT constant expression.

This fiddle demonstrates the difference.