Solved

why this different?

Posted on 2013-02-07
6
245 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
Comment Utility
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
Comment Utility
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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 167 total points
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:wasabi3689
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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 article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now