• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

why this different?

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
wasabi3689
Asked:
wasabi3689
3 Solutions
 
Anthony PerkinsCommented:
Why NOT NULL becomes NULL???
Perhaps someone changed it?  But the bigger question is why not?  What exactly is your question?
0
 
Aaron ShiloCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
wasabi3689Author Commented:
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
 
Aaron ShiloCommented:
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
 
Anthony PerkinsCommented:
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

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now