NULL VALUES
-
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.
-
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?
-
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
-
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----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
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.
-
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[/url] 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[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url] -
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply