Solved

'ANSI null default' help

Posted on 2006-06-12
2
556 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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Rewrite without the NULLIF 4 25
SQL Query Syntax error after > 11 39
Many to one in one row 2 35
8 hour Continual coverage based on Time in and Time Out 9 15
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

679 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