Best Practice for SQL Server Null Values / Empty Strings

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 co
ntain 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 m
ore 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 adapte
r, 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
Denise,
I'll give you my opinion, for what it's worth.
A NULL means an unknown state. Therefore if you do not know the person's
phone number then it is unknown, therefore NULL.
An empty string is a positive entry into the database. It could be
interpreted as, "I KNOW that this value is empty" and could therefore be
interpreted as "this person doesn't have a phone".
This is a subtle difference to NULL. NULL just means "I don't know", or
unknown state. I doubt there is any performance difference between the
two, however I haven't tested it.
I'm not sure if I've actually answered your question because you need to
ensure you are passing NULLs to the database and not empty strings in
your data access layer code. If you want to prevent empty strings from
entering the database, then you could use a constraint like this:
create table a (i int not null, c varchar(30) null check (c <> ''))
insert a (i,c) values (1,null) -- succeeds
insert a (i,c) values (2,'') -- fails
select * from a
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Denise wrote:
> 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
|||There are strong debates regarding whether or not nulls should ever be
allowed in data columns. I agree with Mark, if you do not know the value
allow null ( even though it may require programming on the front end.)
Others ( Kalen Delaney for instance) make strong arguments for never
allowing nulls in the database.
This is an area where reasonable people differ in their opinions, so do
whatever works for you, with a clear conscience..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:O07qtuHSEHA.2000@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Denise,
> I'll give you my opinion, for what it's worth.
> A NULL means an unknown state. Therefore if you do not know the person's
> phone number then it is unknown, therefore NULL.
> An empty string is a positive entry into the database. It could be
> interpreted as, "I KNOW that this value is empty" and could therefore be
> interpreted as "this person doesn't have a phone".
> This is a subtle difference to NULL. NULL just means "I don't know", or
> unknown state. I doubt there is any performance difference between the
> two, however I haven't tested it.
> I'm not sure if I've actually answered your question because you need to
> ensure you are passing NULLs to the database and not empty strings in
> your data access layer code. If you want to prevent empty strings from
> entering the database, then you could use a constraint like this:
> --
> create table a (i int not null, c varchar(30) null check (c <> ''))
> insert a (i,c) values (1,null) -- succeeds
> insert a (i,c) values (2,'') -- fails
> select * from a
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Denise wrote:
> 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.
> 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.
> 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.
ADO.Net by default it sometimes saves them as an empty string
|||you've definately opened a can of worms. (Religious topic)
Many argue that nulls suggest a normalization problem.
Nulls will cause performance issues
Nulls will force you to add handling into your sprocs etc.
it's your call of course whether or not you wish to use them.
I lean towards not using them except in rare situations, but that's just me.
Cheers,
Greg Jackson
PDX, Oregon
|||Thanks for all your insight.