ANSI NULL DEFAULT database option - Microsoft SQL Server

Hi,

I am using SQL Server 2005 32 bit version. Could any one clarify me
the caption database option with some examples? I am quite confused
with the explanation given in MSDN. Kindly help me.

Thanks in Advance.
Om Prakash

Nov 13 '07 #1

Subscribe Reply

11026

Erland Sommarskog

Om (bu**********@g mail.com) writes:

I am using SQL Server 2005 32 bit version. Could any one clarify me
the caption database option with some examples? I am quite confused
with the explanation given in MSDN. Kindly help me.

I'm a little uncertain on what options you are asking about. Is it
only the ANSI NULL DEFAULT option, or is it the ANSI options in general
or options in general.

I'll expand this to the ANSI options in general, since this is a quite con-
fusing topic, and if you did not get it from MSDN one can hardly blame you.
The good news is that those settings have little importance in practice.
There are several ways to turn on the ANSI options:
o Set by default on connection with most APIs.
o Explicit SET command.
o Database option.
o The server-wide configuration option "user options".

As long as there is no explicit SET OFF command, an option is ON if any of
the four says so. And since SQL Server sets these options ON with most
APIs, if follows that these options only matters if you use API that
does not. And the only API I can think of here is DB-Library. Possibly
very old ODBC drivers.

Note that some tools, Enterprise Manager and SQLCMD, runs with some of
these off, but they send an explicit SET command, so the database
setting does not matter.

Note also that ANSI_NULLS and QUOTED_IDENTIFI ER are saved with the
procedure, so here what matters is the setting at create-time, not
at run time.
The particular option ANSI NULL DEFAULT controls what this means:

CREATE TABLE gogo (a int)

Is a nullable or not? With ANSI NULL DEFAULT on, the column is nullable,
with old-style SQL Server it is not. In this particular case, my
recommendation is that you always explicitly say NULL/NOT NULL.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Nov 13 '07 #2

Similar topics

33014

Check if $_GET["menu"] is null.

by: Nuno Paquete | last post by:

Hi group. I'm using this code to see if is there any parameter for variable "menu": if($_GET == "downloads") .... But this code log errors if there is no parameter passed (this heappens at the first time the page is loaded). I tryed this code:

PHP

3101

Usage example for ANSI.py from the pexpect package

by: Eric Myers | last post by:

Hello folks: (This message is also posted on the help forum at the pexpect sourceforge page, but all indentation in the code got stripped away when I submitted the post.) For some time I've wanted to make use of the ANSI.py module in the pexpect package to handle screen-based telnet sessions in Python, but I could never break the ice with the thing. After reading an article by Greg Jorgenson where he recounts using the package to...

Python

11481

value is null or not an object, but its defined?

by: cwizard | last post by:

I'm calling on a function from within this form, and there are values set but every time it gets called I get slammed with a run time error... document.frmKitAmount.txtTotalKitValue is null or not an object... the function is like so: function calc_total() { var x,i,base,margin,total,newmargin,newtotal; base = document.frmKitAmount.txtTotalKitValue.value; margin = document.frmKitAmount.margin.value/100;

Javascript

1788

Common practice, allowing null values in data classes.

by: D Witherspoon | last post by:

What is the accepted method of creating a data class or business rules object class with properties that will allow the returning of null values? For example... I have a class named CResults with the following properties. TestID int QuestionID int AnswerID int So, this is a simple example, but I want to be able to know if AnswerID is

by: Roose | last post by:

Just to make a tangential point here, in case anyone new to C doesn't understand what all these flame wars are about. Shorthand title: "My boss would fire me if I wrote 100% ANSI C code" We are discussing whether this newsgroup should focus on 100% ANSI C or simply topics related to the C language in the real world. There is a C standard which is defined by an international committee. People who write compilers refer to this in...

C / C++

16194

NULL values

by: James Fisher | last post by:

How are people handling NULL values for value types. Descriptions of problem Say you have a typical method with the signature: public int InsertPerson(string firstName, string lastName, int personCategoryID) Now say you only want the firstName and lastName parameters to be

C# / C Sharp

20359

notepad saves as ANSI, what about c#

by: Ziver MALHASOGLU | last post by:

Hi, I produce a text file using my windows application written with c#. -- System.Text.Encoding encOutput=null; encOutput=System.Text.Encoding.UTF8; StreamWriter sw=new StreamWriter(@"c:\a.txt",false, encOutput); -- this UTF-8 file will be used to transfer data to another database's import wizard. Unfortunately db that will import requires the file to be

C# / C Sharp

13658

Allow NULL or Define DEFAULT Value

by: binder | last post by:

I am designing a new table with a few columns that may or may not have a value on each row that is inserted. What issues determine whether to allow a NULL value to be inserted for that column or define a default value to be used? I want to think through the repercussions of this decision before I get into production.

Microsoft SQL Server

3532

After transferring to MySQL, date fieldvalues now show null

by: janetopps | last post by:

I have a news website, with asp pages, which was on Access, and i upgraded to MySQL, i used Bullzip to transfer the data. It had about 1000 pages, which im now able to pull up on the public side. Im sorting out a few glitches though. Since i upgraded from ms access database to MySQL, i have added about 4 articles to test the new setup. I note some fields aren't being added in the new mySql database for the new 4 records. When i ran the MySQK...

ASP / Active Server Pages

8869

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8551

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8639

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

6198

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5664

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4198

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4368

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2011

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1775

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General