Solved

'ANSI null default' help

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how the fundamental information of how to create a table.
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.

734 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