sql server 2008 NULLS in Data

HI all,
I'm working on a data migration project and have to transfer data to clients sql server. In my source I have lot of NULL values in some of the columns. what is the best practice. Where ever nulls are in source should I replace them with ''  and then send them the data OR leave nulls as it is as they can query nulls easily than empty fields?
What is the common and best practice. What if they export these nulls to excel. Will it show them nulls too.  Please suggest.
BI 0824Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James ElliottManaging DirectorCommented:
NULL is the absence of data. I personally would not change that. SQL handles nulls very well.

Here are some functions that do it:

http://www.w3schools.com/sql/sql_null_values.asp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The topic of NULL vs. '' or 0 or some other character is always a big topic of debate.  Most developers would prefer the NULLs, as there is a big difference between the absense of data (posted above) and a zero, but in some cases based on business need another character is preferred.   Most business people have no frEEaking clue what 'NULL' means, and it only confuses them.

>What if they export these nulls to excel.
Excel often interprets a NULL as the string 'NULL', and write it out when a data set is exported to Excel.  In that case it may be advisable to write a query that replaces the NULL values with '', and export the query.

>What is the common and best practice
Use the ISNULL or COALESCE functions to convert NULLs into some other value.
0
Scott PletcherSenior DBACommented:
From a purely data perspective, NULLs are extremely valuable in differentiating between unknown/missing data and known data that is actually 0 or blank ('').  From the data side, NULLs are in invaluable advantage that should not be given up.

From the purely development and querying side, NULLs are a royal pita.  Writing queries to properly handle NULLs can be quite tricky, even for those who moderately understand NULLs, but are not really expert at it.  This is particularly true for "not equal" conditions.  For example, if you write " state <> 'FL' " in a query, you might expect to see NULL states, but you won't.

Then what to do?  I suggest that data accuracy/integrity is the most important part of a database.  Therefore, I'd opt to use NULLs and give clear guidelines to developers/power users that write queries on how to properly deal with NULLs.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.