SQL Server IS_NULLABLE

Question

Good Morning,

Just a quick question what this field actually means?

I am trying to create an export script which follows this standard:

 lname   varchar(30)     **NOT NULL**,

So if last name is_nullable=yes then would I put NULL rather than NOT NULL at the *'d code.

Many Thanks, Joel

Answer 1

lname   varchar(30)     NOT NULL

Means the field lname does not allow NULL values.

lname   varchar(30)     NULL

On the other hand means that NULL values are allowed.

Answer 2

The docs for CREATE TABLE state

<column_definition> ::=
column_name <data_type>
    [ FILESTREAM ]
    [ COLLATE collation_name ] 
    [ SPARSE ] 
    [ NULL | NOT NULL ]
    [ 
        [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] 
      | [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ] 
    ]
    [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ] 

with further explanation

NULL | NOT NULL

Determine whether null values are allowed in the column. NULL is not strictly a constraint but can be specified just like NOT NULL. NOT NULL can be specified for computed columns only if PERSISTED is also specified.

The [ ] brackets mean you don't have to specify either, but given the complexity of the rules around what the default is (see "Nullability Rules Within a Table Definition" in the above link), you're probably better off always saying which you want.