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
Denis
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 stringYou 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
>.
>|||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|||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|||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...
> 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|||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.
Showing posts with label strings. Show all posts
Showing posts with label strings. Show all posts
Monday, March 19, 2012
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
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
Sunday, March 11, 2012
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, f
illed in. If the user blanks out a phone number, the .NET DataAdapter .Upda
te 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 stri
ngs from getting into the database? (Perhaps triggers or some global settin
g?) Or should the string fields be empty strings and never nulls...? I cou
ld re-write the data adapte
r, but I don't know if I can trust that every program that touches the datab
ase 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 stringDenise,
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 defau
lt 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.
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, f
illed in. If the user blanks out a phone number, the .NET DataAdapter .Upda
te 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 stri
ngs from getting into the database? (Perhaps triggers or some global settin
g?) Or should the string fields be empty strings and never nulls...? I cou
ld re-write the data adapte
r, but I don't know if I can trust that every program that touches the datab
ase 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 stringDenise,
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 defau
lt 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.
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.
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.
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
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
Wednesday, March 7, 2012
Best method of doing Connection Strings
I am using SQL 2000 sp3a on Windows 2000 sp3. I have developed an Intranet application using asp.net/vb.net. Currently my connection string is:
data source=intraweb1;initial catalog=ASGWEB;password=blahblah;persist security info=True;user id=justauser;packet size=4096
So all my users are coming in with one SQL database id. Is this the best method for a combination of security and performance?
I do not allow anonymous to the website so I was thinking of setting up an application role and putting the domain users account in it. But from some other threads I was reading this does not work well with connection pooling.> Is this the best method for a combination of security and performance?
yeah, that's fine. I hardly ever do it otherwise - it's not fine-grained security-wise, but do you need it to be?
as for the connection pooling thing, yup - connection polling makes a pollfor the user id, so with multiple users you'd probably lose the beneficial effects, besides needing more CALs|||::besides needing more CALs
Using onedb server is does NOT save you CAL's. Read the licensing condition. You still need one CAL for every user. They say user - NOT user id. This is actually extremely clear, especially in the descriptions and comments.|||I had a discussion about this recently, and the concensus seemed to be one Device Access license for IIS to grab data if you're using one user ID. licencing is a nightmare though, and don't claim to be an expert on it by any means. I usually just ask MS whet the deal is and get multiple answers (!)
Best Method for Handling Long Strings in Stored Procedures?
What is the best way to handle long strings in stored procedures, especially strings that my exceed 8000 characters?
I have a procedure that retrieves a couple groups of sales records that require follow-up for each salesman. This information is then formatted into the body of an HTML email (the number of data items for each quote require the use of tables to be readable in the email) by appending the necessary HTML and record data to a VARCHAR(8000) variable which is ultimately used as the body of the email.
The procedure functions great but I have encountered two unexpected issues I need some help resolving.
1. How can I handle strings longer than 8000 characters? It looks like if I have more than about 12 – 15 records returned by the time I add the HTML I am exceeding this limit and the balance is just truncated.
2. There seem to be "breaks" that are inserted in string/variable based on criteria I haven't figured out. Occasionally these occur in the middle of one of the HTML tags and break the corresponding rendering. I have tried, with limited to success to prevent this by inserting CHAR(10) characters at appropriate places to keep line lengths to about 80 characters but that consumes valuable character space and seems to only be about 60% effective. Can anyone enlighten me on what is causing this?
Thanks for your help.For item 1 use the ntext field type. You can store an unlimited length text value in an ntext field. You can set and get data with an ntext field just like you would with a varchar. You can't use ntext fields quite like you would a character fields (e.g., you can't use it in a WHERE clause) but if you just want to save and retrieve it you should be ok.
For item 2 I don't know, I haven't encountered that problem.|||I am using a variable to hold the string in the stored proc as it is assembled. It was my understanding that text and ntext fields could not be used for variables. Are you creating a cursor with an ntext field or what?
What I am trying to do it insert some text into the variable (header) then append some more text (section header) then I iterate throught the results from the select statement appending each returned record to the variable. Then I append more text (2nd section header) and again iterate throught those select results and append each. Then I return this now very fat variable to the calling proc which uses it as the body of the email.
Is there a better approach for this type of task? Basicaally I am trying to dynamically build an email body that can exceed 8000 characters with some regularity.
Thanks,
--Marshall|||It might be easier to do the string building from a calling program (C#, VB.Net). I don't know of a way to concatenate ntext fields in a sproc. When I have used ntext fields they just pass into and out of a sproc. The sproc itself doesn't manipulate them.
Sunday, February 12, 2012
Beginner Help Wanted With PL/SQL
Hi I was wondering if anybody could help me with this problem:
Step 1: Create a table to store text strings entered by a database user. When a text string is entered to the database, various information about the entry should be recorded including:
A unique identifier for the entry that will be the primary key. You must set the primary key using a constraint
The actual text string entered by the user
The name of the database user that entered the text string
The date that the string was entered
Step 2: Create a sequence that starts off with an initial value of 100 and increments by 10
Step 3: Declare a PL/SQL block that:
Declares four appropriately named variables using anchored datatypes that match each column in the table defined in Step 1
Prompts the user to enter a text string
Assign the value entered by the user to the variable defined to store the string entered
Assign the date, user and next value in the sequence to the other variable defined previously. Note that you should use the SELECT INTO FROM DUAL method of assignment
Within the PL/SQL Block, insert the information assigned to the variables into the table defined in Step 1Which specific part are you having trouble with? Clearly you are not asking us to do all your homework for you!|||Homework? Yeah hold on I have to go ask my mammy if I can use the computer!|||Originally posted by iknownothing
Homework? Yeah hold on I have to go ask my mammy if I can use the computer!
Don't forget to say "please"!|||Jaysus you're hilarious!!|||Originally posted by iknownothing
Jaysus you're hilarious!!
You are too kind. But seriously, is there any specific help you want with your "homework" ("class assignment", call it what you will), or did you just want someone to do it all for you? You will find that people round here are very helpful if you are prepared to put in some of the effort yourself. On the other hand, people are less inclined to help when it appears that someone just wants to pass off someone else's effort as their own.
So: what have you come up with so far, and where are you stuck?|||Well, you are already using a cursor-based record! :-
student_val c_student%ROWTYPE;
i.e. the record type is defined in terms of the cursor c_student.
The table-based cursor would be student%ROWTYPE.
If you use a cursor FOR loop you can get rid of the declaration altogether, along with a lot of other code:
SET SERVEROUTPUT ON;
DECLARE
CURSOR c_student IS
SELECT * FROM student;
begin
open c_student;
for student_val in c_student loop
DBMS_OUTPUT.PUT_LINE('Student Details: ' || student_val.salutation || student_val.first_name
|| student_val.last_name || student_val.phone || student_val.Registration_date );
end loop;
end;|||Yeah I figured it out later and deleted the post coz it was pointless but what I need to know now is how to change it from a cursor to a table based! Im in the process of trying but am getting nowhere!!
Step 1: Create a table to store text strings entered by a database user. When a text string is entered to the database, various information about the entry should be recorded including:
A unique identifier for the entry that will be the primary key. You must set the primary key using a constraint
The actual text string entered by the user
The name of the database user that entered the text string
The date that the string was entered
Step 2: Create a sequence that starts off with an initial value of 100 and increments by 10
Step 3: Declare a PL/SQL block that:
Declares four appropriately named variables using anchored datatypes that match each column in the table defined in Step 1
Prompts the user to enter a text string
Assign the value entered by the user to the variable defined to store the string entered
Assign the date, user and next value in the sequence to the other variable defined previously. Note that you should use the SELECT INTO FROM DUAL method of assignment
Within the PL/SQL Block, insert the information assigned to the variables into the table defined in Step 1Which specific part are you having trouble with? Clearly you are not asking us to do all your homework for you!|||Homework? Yeah hold on I have to go ask my mammy if I can use the computer!|||Originally posted by iknownothing
Homework? Yeah hold on I have to go ask my mammy if I can use the computer!
Don't forget to say "please"!|||Jaysus you're hilarious!!|||Originally posted by iknownothing
Jaysus you're hilarious!!
You are too kind. But seriously, is there any specific help you want with your "homework" ("class assignment", call it what you will), or did you just want someone to do it all for you? You will find that people round here are very helpful if you are prepared to put in some of the effort yourself. On the other hand, people are less inclined to help when it appears that someone just wants to pass off someone else's effort as their own.
So: what have you come up with so far, and where are you stuck?|||Well, you are already using a cursor-based record! :-
student_val c_student%ROWTYPE;
i.e. the record type is defined in terms of the cursor c_student.
The table-based cursor would be student%ROWTYPE.
If you use a cursor FOR loop you can get rid of the declaration altogether, along with a lot of other code:
SET SERVEROUTPUT ON;
DECLARE
CURSOR c_student IS
SELECT * FROM student;
begin
open c_student;
for student_val in c_student loop
DBMS_OUTPUT.PUT_LINE('Student Details: ' || student_val.salutation || student_val.first_name
|| student_val.last_name || student_val.phone || student_val.Registration_date );
end loop;
end;|||Yeah I figured it out later and deleted the post coz it was pointless but what I need to know now is how to change it from a cursor to a table based! Im in the process of trying but am getting nowhere!!
Subscribe to:
Posts (Atom)