NULL VALUES

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » NULL VALUES

NULL VALUES

Rate Topic

Display Mode

Topic Options

Author

Message

lonhanner

Posted Sunday, January 06, 2013 2:37 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 22, 2013 11:14 AM
Points: 39, Visits: 141

My client "My 1st" has a sql database with many, many null values in each table. Its because they do not get all info when they are adding new records. I would like to change all null values including dates to a default value such as "NA" or something, and set this default value when no data is entered into a field. I am using vb.net 2010 pro and sql server express. Can I write a query to update all null values with a default value.
Please help.
as always thanks.

Post #1403360

pietlinden

Posted Sunday, January 06, 2013 2:44 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 20, 2013 4:13 PM
Points: 186, Visits: 1,583

I disagree. Maybe check in the front end or the stored procedure used to write the data to the table, but changing a NULL to N/A adds absolutely no information. So what's the point in storing it, since it means essentially the same thing as NULL, which doesn't take up space?

Post #1403362

lonhanner

Posted Sunday, January 06, 2013 3:19 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 22, 2013 11:14 AM
Points: 39, Visits: 141

Ok thanks for educating me on the null thing(good info for me). I do not have any stored procedures in my database or to say I have not created any. I use parameter in vb to store the values of my fields. They are passed through objects as a datasources. I can read and create new records. I just cant update. I will check on the front end.

as always thanks

Post #1403364

Bhuvnesh

Posted Monday, January 07, 2013 12:42 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

lonhanner (1/6/2013)

I do not have any stored procedures in my database or to say I have not created any. I use parameter in vb to store the values of my fields. They are passed through objects as a datasources. I can read and create new records. I just cant update. I will check on the front end.

So i suspect the standard of your application here , is it a testing(specifically beginners) , i dont have knowledge about .Net but i think it would be difficult to handle comple business logic at front end level.additonally what Store proc provide is performance boost from backend perspective(as stored proc play with the data faster because of exec plan available in cache). so i will suggest to to use procedure for data handling

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1403438

Steven Willis

Posted Monday, January 07, 2013 9:33 AM

SSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 7:19 AM
Points: 283, Visits: 1,239

Just FYI...

You can change the default value (constraint) of any column while in design mode. (See attached screen shot.)

If you add a constraint and set the column to not allow NULLs, when you save the change all NULLs will be changed to the constraint default value. Once saved, if you insert a new row you do not need to specify a value for the column with the constraint unless it is different from the default--it will insert the default value automatically.

A common example of this might be a DateCreated column where you set the constraint to use 'GETDATE()' and set the column to not allow nulls. Then whenever a row is created the current datetime will be inserted.

Whether you want change all current NULLs to something else or leave them NULL depends on a lot of things that are way beyond the scope of one post. But a good place to start is to look up the ISO standards for the type of information you are storing.

For example, say you have a column for 'gender.' Lots of developers use 'M', 'F', or NULL. But the ISO standard is:

Column Name: 'SEX'

Values:
0 = not known,
1 = male,
2 = female,
9 = not applicable.

Using these values always gives a clear picture. So if you don't know a person's sex we KNOW that we don't know. Or if the entity is an organization it's clear by entering a value of '9' that this column is not applicable to that entity. Thus, no NULLs to deal with in WHERE clauses or to complicate query plans.

Post Attachments

ColumnConstraint.png (134 views, 27.28 KB)

Post #1403708

CapnHector

Posted Monday, January 07, 2013 2:50 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 935, Visits: 1,709

Personally i would rather "Store" the null value than "N/A" as i can save space and if its a numeric field dont have to make any thing special. I put the "N/A" in on the front end when retrieving the data (either in the UI or with an ISNULL() in the SP). now if the field is really N/A (Last menstrual cycle for a male, love that question on medical forms) then i will store a N/A in the NOT NULL column but if a column is null able i just keep the nulls.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler

Post #1403881

Aadhar Joshi

Posted Tuesday, January 08, 2013 3:20 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 10:44 PM
Points: 1,143, Visits: 229

I would say, u can update columns by N/A because u later don't have to handle from .net as sql server is more faster in compare to .net and it also creates burden to handle NULL by replacing N/A

Post #1404091

« Prev Topic | Next Topic »

Permissions