Solved

why this different?

Posted on 2013-02-07
6
255 Views
Last Modified: 2013-02-11
I have two MS  SQL database. They are the same database. but made in different time ( 6 ms ago) and different version of MS SQL server. One is made MS SQL 2008, another one is maded in MS  SQL 2008 R2

When I compare them, I see the following is strange.

One database showing the following for table ABC

[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

Another database showing the same table and same field as

[NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

Why NOT NULL becomes NULL???
0
Comment
Question by:wasabi3689
[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
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38864859
Why NOT NULL becomes NULL???
Perhaps someone changed it?  But the bigger question is why not?  What exactly is your question?
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 38864876
hi

one table was created with the column constraint NOT NULL
and the other made with no constraint that's all.

you can change the constraint on any of the tables as long as the data is correct
for example change the NULL to not null as long as you don't really have any nulls in the columns.

or on the other hand change the NOT NULL column to NULL.

Aaron.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 167 total points
ID: 38864969
it may depend on some server settings.
when you create a table, the NOT NULL or NULL implicitly applies ...

http://msdn.microsoft.com/en-us/library/ms174979.aspx
Nullability Rules Within a Table Definition

The nullability of a column determines whether that column can allow a null value (NULL) as the data in that column. NULL is not zero or blank: NULL means no entry was made or an explicit NULL was supplied, and it typically implies that the value is either unknown or not applicable.

When you use CREATE TABLE or ALTER TABLE to create or alter a table, database and session settings influence and possibly override the nullability of the data type that is used in a column definition. We recommend that you always explicitly define a column as NULL or NOT NULL for noncomputed columns or, if you use a user-defined data type, that you allow the column to use the default nullability of the data type. Sparse columns must always allow NULL.

When column nullability is not explicitly specified, column nullability follows the rules shown in the following table.

Column data type
      

Rule

Alias data type
      

The Database Engine uses the nullability that is specified when the data type was created. To determine the default nullability of the data type, use sp_help.

CLR user-defined type
      

Nullability is determined according to the column definition.

System-supplied data type
      

If the system-supplied data type has only one option, it takes precedence. timestamp data types must be NOT NULL.

When any session settings are set ON by using SET:

    ANSI_NULL_DFLT_ON = ON, NULL is assigned.

    ANSI_NULL_DFLT_OFF = ON, NOT NULL is assigned.

    When any database settings are configured by using ALTER DATABASE:

    ANSI_NULL_DEFAULT_ON = ON, NULL is assigned.

    ANSI_NULL_DEFAULT_OFF = ON, NOT NULL is assigned.

    To view the database setting for ANSI_NULL_DEFAULT, use the sys.databases catalog view

When neither of the ANSI_NULL_DFLT options is set for the session and the database is set to the default (ANSI_NULL_DEFAULTis OFF), the default of NOT NULL is assigned.

If the column is a computed column, its nullability is always automatically determined by the Database Engine. To find out the nullability of this type of column, use the COLUMNPROPERTY function with the AllowsNull property.
Note Note

The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server both default to having ANSI_NULL_DFLT_ON set to ON. ODBC and OLE DB users can configure this in ODBC data sources, or with connection attributes or properties set by the application.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:wasabi3689
ID: 38865310
I want to know if the default setting to NULL or NOT NULL is different for different MS  SQL server version?

Where to see the default setting for column NULL or NOT NULL setting in MS  SQL?

Why the same database for this field got change from NOT NULL to NULL as I've known we don't that change at all

This is important because our application is depended on it. I want to find out the root cause.
0
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 167 total points
ID: 38865353
1. no there is no difference
2.  you can right click the table and click on DESIGN.
3. how do you create the table ? may be in the original database the table was originaly created with NULL and then changed to NOT NULL.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 166 total points
ID: 38866353
Why the same database for this field got change from NOT NULL to NULL as I've known we don't that change at all
That would be because you or your organization have allowed that to happen.  In other words you need to fix your SQL Server permissions.

This is important because our application is depended on it. I want to find out the root cause.
I think we already told you that.  But in case you missed it here it goes again:
Someone changed it or created it that way.  There is no other explanation.  The nightly gremlins were not responsible in this case.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

738 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