[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

'ANSI null default' help

Posted on 2006-06-12
2
Medium Priority
?
592 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
[X]
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
2 Comments
 
LVL 4

Accepted Solution

by:
indu_mk earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

649 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