CODEWITHSUNDEEP
database-designnulldefaultsql-server-2008-r2
vicatcu
vicatcu

Reputation: 5837

SQL Server NULL and DEFAULT column specifications

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...

Upvotes: 1

Views: 2201

Answers (2)

user
user

Reputation: 6947

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).

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

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.

Upvotes: 1

Related Questions