SQL Server NULL and DEFAULT column specifications

Question

Is there a difference between declaring a column with just Allow Nulls = Yes as compared to declaring it with both Default Value or Binding = NULL AND Allow Nulls = Yes?

In particular I'm interested in if there is a difference with respect to JDBC and the wasNull method semantics. My impression was that Allow Nulls = Yes (without a DEFAULT spec) was sufficient to allow you to exclude the column from an INSERT clause and expect that the value of that field in the inserted row is assigned the SQL NULL value...

It has been suggested to me that wasNull misbehaves for specifically for an NVARCHAR field in my database that is retrieved from a result set using the getString method of the result set when I didn't have the DEFAULT specified explicitly as NULL...

Damien_The_Unbeliever · Accepted Answer · 2011-03-21 15:43:35Z

1

You can declare a column to have a default value of NULL, but it's a spectacularly pointless thing to do. All I'd expect it to achieve is to minutely slow down the insertion process (since it has to look up the default value). Allow Nulls = Yes should be sufficient.

I don't see how wasNull comes into this specifically, since, so far as I understand it, wasNull is used when consuming result sets, not when performing insert operations.

answered Mar 21, 2011 at 15:43

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user Over a year ago

Actually, depending on the data type, declaring the column to have a default value of NULL may be exactly what needs to be done.

Damien_The_Unbeliever Over a year ago

@Michael - have you got an example you're thinking of?

user Over a year ago

@Damien: See the link in my answer.

user Over a year ago

(Obviously, that should be depending on the data type and the desired result.)

Damien_The_Unbeliever Over a year ago

@Michael - there's no mention of requiring a default to achieve a null. The fourth bullet point says "Is nullable. A null value is used." - which would indicate that you don't need to do anything else to that column.

|

user · Accepted Answer · 2011-03-21 15:44:15Z

1

Simply making the column allow nulls does not guarantee that it will have the value NULL if it is not provided in an INSERT statement's column list.

Books Online has the gory details under the Arguments heading. Look at (column_list).

answered Mar 21, 2011 at 15:44

user

6,9478 gold badges47 silver badges82 bronze badges

Collectives™ on Stack Overflow

SQL Server NULL and DEFAULT column specifications

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related