Solved

why this different?

Posted on 2013-02-07
6
253 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
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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

837 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