Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

'ANSI null default' help

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

856 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