Best Practice for SQL Server Null Values / Empty Strings

You are totally right in your reseaech of null fields, did
you know that if you perform a string concatination with a
null it will always result in a null i.e
@.Forename = 'Denise'
@.Middlename = null
@.Surname = 'Smith'
Set @.Fullname = @.Forename + ' ' + @.Middlename + ' ' +
@.Surname
Will mean @.Fullname will be null.
However Nulls can also be useful i.e looking for NOT NULL,
and see the COALESCE statement, it really up to you.
If you want to get rid of null then you can use defaults.
The default will change a null to anything you want it to
be i.e '' or empty string.
To create a default
1. In EA go to the database
2. Select Defualts
3. Right click - select new defaults
4. Give it a name such as 'EmptyString'
Then when you create a column you can assign it the
default. This however will only work for new records and
not for existing ones.
J

>--Original Message--
>I'm fairly new to SQL Server. Coming from Acces, I see
that Null values are handled differently. I've read many
of the posts on querying Null values, but I want to know
what is the best practice for designing a new system (SQL
Server 2000) that could contain empty fields.
>For example, suppose I have a 'Phone' field that is
often, but not always, filled in. If the user blanks out
a phone number, the .NET DataAdapter .Update method will
save the field as an empty string instead of a NULL. This
of course makes every query more complex having to check
for both nulls and empty strings.
>Is there any practical way to prevent, at the database
level, the empty strings from getting into the database?
(Perhaps triggers or some global setting?) Or should the
string fields be empty strings and never nulls...? I
could re-write the data adapter, but I don't know if I can
trust that every program that touches the database will
have handled the issue correctly.
>Any opionions?
>Thanks,
>Denise
>
>using VB.Net and ADO.Net code and if the user blanks out
a field, ADO.Net by default it sometimes saves them as an
empty string
>.
>
Julie,
(just to point out that the first part of your response is not always
necessarily the case
SET CONCAT_NULL_YIELDS_NULL ON
select null + 'hello'
SET CONCAT_NULL_YIELDS_NULL OFF
select null + 'hello'
Regards,
Paul Ibison