Solved

'ANSI null default' help

Posted on 2006-06-12
2
537 Views
Last Modified: 2012-08-13
Hi,

I am after some help. Can anybody tells me what happens when I use this code:

EXEC sp_dboption 'pubs','ANSI null default','false'
GO
CREATE TABLE t1 (a tinyint)
GO
INSERT INTO t1 (a) VALUES (null)
GO
SET ANSI_NUL_DFLT_ON ON
GO
CREATE TABLE t2 (a tinyint)
Go
INSERT INTO t2 (a) VALUES (null)
GO
SET ANSI_NULL_DFLT_ON OFF
GO
CREATE TABLE t3 (a tinyint)
GO
INSERT INTO t3 (a) VALUES (null)
GO
EXEC sp_dboption 'pubs','ANSI null default','true'
GO
CREATE TABLE t4 (a tinyint)
GO
INSERT INTO t4 (a) VALUES (null)
GO
SET ANSI_NUL_DFLT_ON ON
GO
CREATE TABLE t5 (a tinyint)
GO
INSERT INTO t5 (a) VALUES (null)
Go
SET ANSI_NUL_DFLT_ON OFF
GO
CREATE TABLE t6 (a tinyint)
GO
INSERT INTO t6 (a) VALUES (null)
Go
EXEC sp_dboption 'pubs','ANSI null default','false'
GO
0
Comment
Question by:alcojoe
2 Comments
 
LVL 4

Accepted Solution

by:
indu_mk earned 500 total points
ID: 16883899
This is the output
==================================================================
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'a', table 'pubs.dbo.t1'; column does not allow nulls. INSERT fails.
The statement has been terminated.

(1 row(s) affected)

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'a', table 'pubs.dbo.t3'; column does not allow nulls. INSERT fails.
The statement has been terminated.

(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)
===================================================================

SET ANSI_NULL_DFLT_ON has an effect when the 'ANSI null default' for the database is false.
SET ANSI_NULL_DFLT_ON has no effect when the 'ANSI null default' for the database is true


EXEC sp_dboption 'pubs','ANSI null default','false'
---------- new column value will be NOT NULL by default

CREATE TABLE t1 (a tinyint)
---------- a will be a NOT NULL column

INSERT INTO t1 (a) VALUES (null)
---------- as a is a NOT NULL column, this insert stmt raises an error

SET ANSI_NULl_DFLT_ON ON
---------- for this session deafult null for new columns in enabled (ON)

CREATE TABLE t2 (a tinyint)
---------- a will be a NULLABLE column

INSERT INTO t2 (a) VALUES (null)
----------  1 row inserted

SET ANSI_NULL_DFLT_ON OFF
---------- for this session deafult null for new columns in disabled (OFF)

CREATE TABLE t3 (a tinyint)
---------- a will be a NOT NULL column

INSERT INTO t3 (a) VALUES (null)
---------- as a is a NOT NULL column, this insert stmt raises an error

EXEC sp_dboption 'pubs','ANSI null default','true'
---------- new column value will be NULL by default

CREATE TABLE t4 (a tinyint)
---------- a will be a NULLABLE column

INSERT INTO t4 (a) VALUES (null)
----------  1 row inserted

SET ANSI_NULl_DFLT_ON ON
---------- for this session deafult null for new columns in enabled (ON)

CREATE TABLE t5 (a tinyint)
---------- a will be a NULLABLE column

INSERT INTO t5 (a) VALUES (null)
----------  1 row inserted

SET ANSI_NULl_DFLT_ON OFF
---------- for this session deafult null for new columns in disabled (OFF)

CREATE TABLE t6 (a tinyint)
---------- a will be a NULLABLE column

INSERT INTO t6 (a) VALUES (null)
----------  1 row inserted

EXEC sp_dboption 'pubs','ANSI null default','false'
---------- new column value will be NOT NULL by default

0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16885313
from books online
==============
Allows the user to control the database default nullability. When NULL or NOT NULL is not specified explicitly, a user-defined data type or a column definition uses the default setting for nullability. Nullability is determined by session and database settings. Microsoft SQL Server™2000 defaults to NOT NULL. For ANSI compatibility, setting the database option ANSI_NULL_DEFAULT to ON changes the database default to NULL.

When this option is set to ON, all user-defined data types or columns that are not explicitly defined as NOT NULL during a CREATE TABLE or ALTER TABLE statement default to allowing null values. Columns that are defined with constraints follow constraint rules regardless of this setting.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server Log File Space 6 34
Date conversion in sql server 2012 6 26
Sql query for filter 12 23
SQL Date Retrival 7 31
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now