Home > SQL Server > ANSI Default Nullability – SQL Server

ANSI Default Nullability – SQL Server


ANSI_NULL_DFLT_OFF and SET ANSI_NULL_DFLT_ON set statements are used to alter the default nullability of a table column.

If explicit column nullability option is specified in the table design then it will override set option. We cannot set both set options to ON at same time. We have to use any one set statement per execution. But, we can set both set statements to OFF at same time.

GETANSINULL([ ‘database’ ]) function returns the default nullability of the database for the current session.

It returns 1 if the default nullablity is on else 0.

Example for GETANSINULL function:

pic1

Example for SET ANSI_NULL_DFLT_ON ON:

pic2This statement allows null value because the ANSI default null is on.

Example for SET ANSI_NULL_DFLT_ON OFF:

pic3This statement does not allow null values because the ANSI default null is off.

We have learned how use ANSI default null set statement in this blog post. You should test this SQL statement with ANSI_NULL_DFLT_OFF ON/OFF in your machine.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: