Confused on how a value gets added with the DEFAULT constraint
If your column definition does not have default constraint and it is nullable, the nullable field will have null mark, when you add a row even when you specify DEFAULT keyword for your insert value. For example for the following table definition

 CREATE TABLE Test(
    ColA  [nvarchar](50) NOT NULL,
    COLB [nvarchar](50) NULL)


The first three insert statements will insert NULL mark for ColB. Only the last one will have ColB value assigned.

 insert into Test(ColA, ColB) values ('a', DEFAULT);
 insert into Test(ColA, ColB) values ('b', NULL);
 insert into Test(ColA) values ('c');
 insert into Test(ColA, ColB) values ('d', 'e');


Whereas for the following table definition with DEFAULT constraint.

CREATE TABLE Test(
    ColA  [nvarchar](50) NOT NULL,
    COLB [nvarchar](50) NULL DEFAULT 'x')


Only the second statement below will insert NULL mark for ColB. The first and the third statements will assign 'x' as the DEFAULT value.

 insert into Test(ColA, ColB) values ('a', DEFAULT);
 insert into Test(ColA, ColB) values ('b', NULL);
 insert into Test(ColA) values ('c');
 insert into Test(ColA, ColB) values ('d', 'e');