MS SQL 2008 What is the difference between a null field and an empty field?

Posted on 2011-04-24
Last Modified: 2012-06-27
I need to edit some MS SQL data and used an ACCESS project to do it. The data was in a view, and the editing went fine.

When i ran the application i got inconsistent results.

Looking at the data in MSSQL SMS i noted a difference between records i entered through ACCESS and other records: The ACCESS entered records contained NULL for empty fields. Other records had empty fields (necessary for the application) but did not show the NULL value.

The fields in question are varchars.

What causes the different presentation in SMS, and how can I make ACCESS create acceptable values?
Question by:KWDave
    LVL 75

    Expert Comment

    by:Anthony Perkins
    If by empty you mean it is '', then yes, they are fundementally different.  A value of NULL means it is unknown, which is not the same as ''.

    Author Comment

    Thanks for the try, but no joy yet.

    I realize null and empty are different conditions. I don't know how to distinguish them in ACCESS, and the behave differently in MS SQL.

    To test what i think was suggested, i entered '' in the fields in ACCESS. In MS SQL Server Management Studio the newly entered data showed as '', not empty as i need. when i deleted the '' in ACCESS, the fields again show as null in MS SQL SMS.

    Unfortunately the SMS editor only allows editing the first 200 rows. The table has almost 1000 rows. There are only a handful that need editing. If ACCESS won't behavie, is there another editor that I could use to directly edit MS SQL tables?

    The only other thing i can think of is to edit with a special character in ACCESS, and update the data in a query.

    This is is ms sql 2008 R2, and Access in office 10.
    LVL 75

    Accepted Solution

    >>Unfortunately the SMS editor only allows editing the first 200 rows. The table has almost 1000 rows<<
    So why in the world are you doing this manually.  If you want them to be NULL then it is as simple as doing to following from a query window in SSMS:
    UPDATE YourTableName
    Set YourColumnName = NULL
    WHERE YourColumnName = ''

    UPDATE YourTableName
    Set YourColumnName = ''
    WHERE YourColumnName IS NULL

    I still do not understand what is the problem.  If you do not understand the importance of NULL in DBMS then I suggest you do some reading on the subject.

    Author Closing Comment

    This issue is too unique and trivial to add to the knowledge base
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>This issue is too unique and trivial to add to the knowledge base <<
    I agree that this question is trivial and has no value in the PAQ, so you best request that the question be deleted.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    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.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Viewers will learn how the fundamental information of how to create a table.

    732 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

    17 Experts available now in Live!

    Get 1:1 Help Now