Hi all,
I have several columns which store currency values (typically up to 4
integer values, plus two decimal places)
Using Enterprise Manager I can set a column as decimal type, but it doesn't
allow me to specify precision) and any values show as the integer amount plu
s
.00 (ie 123.45 shows as 123.00). I converted these fields to money, but
several stored procedures showed a slow-down.
What is the most efficient datatype for storing very low precision real
numbers? What went wrong with my decimal datatype?
Many thanks in advance!Within EM look at the bottom half of the window. You will see a precision
and scale attribute there.
Keith Kratochvil
"GeorgeBR" <GeorgeBR@.discussions.microsoft.com> wrote in message
news:B8499051-76E7-4F2E-8650-971709333F6D@.microsoft.com...
> Hi all,
> I have several columns which store currency values (typically up to 4
> integer values, plus two decimal places)
> Using Enterprise Manager I can set a column as decimal type, but it
> doesn't
> allow me to specify precision) and any values show as the integer amount
> plus
> .00 (ie 123.45 shows as 123.00). I converted these fields to money, but
> several stored procedures showed a slow-down.
> What is the most efficient datatype for storing very low precision real
> numbers? What went wrong with my decimal datatype?
> Many thanks in advance!|||Also, don't use the money type. In addition to the performance issues you
are seeing, you will get rounding errors with money.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:e$STqrafGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Within EM look at the bottom half of the window. You will see a precision
> and scale attribute there.
>
> --
> Keith Kratochvil
>
> "GeorgeBR" <GeorgeBR@.discussions.microsoft.com> wrote in message
> news:B8499051-76E7-4F2E-8650-971709333F6D@.microsoft.com...
>
Showing posts with label values. Show all posts
Showing posts with label values. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Best practices: changing values
What is the best way to design your tables in cases where field values change?
Example:
CREATE TABLE Product (ProductID INT, Description VARCHAR(32), Price SMALLMONEY...);
CREATE TABLE Purchase (PurchaseID INT, ProductID INT, Quantity INT);
Since price obviously change over time, I was wondering what the is the best table schema to use to reflect these changes, while still remembering previous price values (like for generating reports on previous sales...)
is it better to include a "Price SMALLMONEY" field in the purchases table (which kind of de-normalizes it) or is it better to have a separate ProductPrice table that keeps track of changing prices like so:
CREATE TABLE ProductPrice (ProductID INT, Price SMALLMONEY, CreationDate DATETIME...);
and have the Purchase table reference the ProductPrice table instead of the products table?
I have used both methods in the past, but I was wanted to get other peoples' take on it.
ThanksBecause price can change for many reasons, I always keep it in the actual transaction row. For example, you might have different prices for a given product based on quantity purchased (for example buying 100 units gets a price break). There might be reasons for different prices based on the customer (one price for wholesale, one for sub-contractors, another price for retail). These differences could be either discreet or cumulative. In short, the price in the inventory table might only be a starting point, the price in the transaction table is the authoritive price for a transaction.
-PatP|||If you want to be able to track historical prices, such as how much a price has changed over time, then you need to add a time dimension to your price table.
But for a financial application such as this there is no substitute to storing the actual price paid in the transation table.|||(which kind of de-normalizes it)
No, it doesn't. :) It's an attribute of the purchase.
The purchase table should have the price paid at time of purchase.
There should be a ProductPrice table that holds the price historically for each price. If you want to avoid duplicating data, you can put the ProductPriceID in the Purchase table so you have the exact price at the time purchase was made.
Example:
CREATE TABLE Product (ProductID INT, Description VARCHAR(32), Price SMALLMONEY...);
CREATE TABLE Purchase (PurchaseID INT, ProductID INT, Quantity INT);
Since price obviously change over time, I was wondering what the is the best table schema to use to reflect these changes, while still remembering previous price values (like for generating reports on previous sales...)
is it better to include a "Price SMALLMONEY" field in the purchases table (which kind of de-normalizes it) or is it better to have a separate ProductPrice table that keeps track of changing prices like so:
CREATE TABLE ProductPrice (ProductID INT, Price SMALLMONEY, CreationDate DATETIME...);
and have the Purchase table reference the ProductPrice table instead of the products table?
I have used both methods in the past, but I was wanted to get other peoples' take on it.
ThanksBecause price can change for many reasons, I always keep it in the actual transaction row. For example, you might have different prices for a given product based on quantity purchased (for example buying 100 units gets a price break). There might be reasons for different prices based on the customer (one price for wholesale, one for sub-contractors, another price for retail). These differences could be either discreet or cumulative. In short, the price in the inventory table might only be a starting point, the price in the transaction table is the authoritive price for a transaction.
-PatP|||If you want to be able to track historical prices, such as how much a price has changed over time, then you need to add a time dimension to your price table.
But for a financial application such as this there is no substitute to storing the actual price paid in the transation table.|||(which kind of de-normalizes it)
No, it doesn't. :) It's an attribute of the purchase.
The purchase table should have the price paid at time of purchase.
There should be a ProductPrice table that holds the price historically for each price. If you want to avoid duplicating data, you can put the ProductPriceID in the Purchase table so you have the exact price at the time purchase was made.
Monday, March 19, 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 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.
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.
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
Thursday, March 8, 2012
Best practice BIT or SET('no','yes') ?
Hi,
I know it's quite common to use BIT fields for boolean values.
CREATE TABLE tblTest (
door BIT DEFAULT 0,
accept BIT DEFAULT 0
)
instead of:
CREATE TABLE tblTest (
door SET('close','open') DEFAULT 'close',
accept SET('no','yes') DEFAULT 'no'
)
(By the way, I use MSSQL and MySQL, not sure if I'm using the right
datatypes for MSSQL)
I reckon the first uses less storage space, but the meaning of the values in
the latter is more unanimous.
So what's best practice? I'm tempted to use the latter, but I almost always
see the first used everywhere.
I'm definitely interested in what CELKO has to say about this.
LisaMy opinion, FWIW
-Comarison operators with bit datatypes will be faster
-You don't have to worry about differences in collations when comparing
-More efficient storage on disk
-You could always add the unanimous-ness (which I'm positive is not a word!)
in a select that extracts the data out of these fields like
select case when door = 0 then 'closed' else 'open' end from tblTest
Proper T-SQL syntax for the character-based table would be
CREATE TABLE tblTest (
door varchar(5) CHECK (door IN ('close','open')) DEFAULT 'close',
accept varchar(5) CHECK (accept IN ('no','yes')) DEFAULT 'no'
)
--
"Lisa Pearlson" wrote:
> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
in
> the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost alway
s
> see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa
>
>|||Here we use the latter one cuz too many different programmers working off
the same database and each programmer has their own programs that they are
responsible for.
I prefer to use the first one to save size.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:O1u6wZDKGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
> in the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost
> always see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa
>|||Lisa Pearlson wrote:
> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
in
> the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost alway
s
> see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa
My main objection to BIT is that its behaviour is too strange and
counter-intuitive. For example, can you predict or explain the results
of the following INSERT statement and the SELECT statement?
CREATE TABLE T (x BIT NOT NULL);
INSERT INTO T VALUES (2);
SELECT MAX(x) FROM T;
In most cases BIT's saving on storage is probably modest. I prefer
concise, readable status codes (CHAR(1) for example) and when I need to
add a third status I don't need to change the datatype I just change a
CHECK constraint.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Like David, I usually use a 1 character flag. In my experience, when I
initially think I have a binary status, it is really a multi-varied
flag. In other words, as soon as I code to tell if the door is open or
closed, someone else wants to know if it's locked.
Payson
Lisa Pearlson wrote:
> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
in
> the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost alway
s
> see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa|||> In other words, as soon as I code to tell if the door is open or
> closed, someone else wants to know if it's locked.
I love the analogy. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Payson" <payson_b@.hotmail.com> wrote in message
news:1138914380.158598.148590@.f14g2000cwb.googlegroups.com...
> Like David, I usually use a 1 character flag. In my experience, when I
> initially think I have a binary status, it is really a multi-varied
> flag. In other words, as soon as I code to tell if the door is open or
> closed, someone else wants to know if it's locked.
> Payson
> Lisa Pearlson wrote:
>|||Lisa--
Great question. One way of clearing this up is by naming your columns in a
way where it won't be ambiguous. If you know that a Door only has two
states, Open and Closed, the meaning will be clear with a BIT datatype if yo
u
use a convention like "IsDoorOpen". That way, it's obvious what "1" and "0"
mean. If it's possible that a door will have more than 2 states in the
future, use a single character {[O]pen, [C]losed, [A]jar} to represent the
state of the door. In that case, you might call your column DoorStatus, or
DoorState... Likewise, you can name your "Accept" column "IsAccepted" and
clarify the meaning there. At my company, we use "Is" to prefix just about
all of the boolean values that we put in our code for just this reason.
HTH
-Dave Markle
"Lisa Pearlson" wrote:
> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
in
> the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost alway
s
> see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa
>
>|||>> In other words, as soon as I code to tell if the door is open or closed,
someone else wants to know if it's locked.
I love the analogy. :-) <
Me, too!! I think I will steal it in the next edition of one of my
books! Hey, Imitation is the sincerest form of flattery; Plagiarism is
the sincerest form of imitation!|||Stop writing code as if you were an assembly language programmer in
1957.
Machine level things like a BIT or BYTE datatype have no place in a
high level language like SQL. SQL is a high level language; it is
abstract and defined without regard to PHYSICAL implementation. This
basic principle of data modeling is called data abstraction.
Bits and Bytes are the <i>lowest<i> units of hardware-specific,
physical implementation you can get. Are you on a high-end or low-end
machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
complement or ones complement math? Hey, the standards allow decimal
machines, so bits do not exist at all!! What about NULLs? To be an
SQL datatype, you have to have NULLs, so what is a NULL bit? By
definition a bit, is on or off and has no NULL.
What does the implementation of the host languages do with bits? Did
you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently (look at C# and VB from the same vendor)? That means
<i>all<i> the host languages -- present, future and not-yet-defined.
Surely, no good programmer would ever write non-portable code by
getting to such a low level as bit fiddling!!
There are two situations in practice. Either the bits are individual
attributes or they are used as a vector to represent a single
attribute. In the case of a single attribute, the encoding is limited
to two values, which do not port to host languages or other SQLs,
cannot be easily understood by an end user, and which cannot be
expanded. Use CHAR(1) which will move.
In the second case what some Newbies, who are still thinking in terms
of second and third generation programming languages or even punch
cards, do is build a vector for a series of "yes/no" status codes,
failing to see the status vector as a single attribute. Did you ever
play the children's game "20 Questions" when you were young? Bingo!!
Imagine you have six components for a loan approval, so you allocate
bits in your second generation model of the world. You have 64 possible
vectors, but only 5 of them are valid (i.e. you cannot be rejected for
bankruptcy and still have good credit). For your data integrity, you
can:
1) Ignore the problem. This is actually what <i>most<i> newbies do.
2) Write elaborate CHECK() constraints with user defined functions or
proprietary bit level library functions that cannot port and that run
like cold glue.
Now we add a 7-th condition to the vector -- which end does it go on?
Why? How did you get it in the right place on all the possible
hardware that it will ever use? Did all the code that references a bit
in a word by its position do it right after the change?
You need to sit down and think about how to design an encoding of the
data that is high level, general enough to expand, abstract and
portable. For example, is that loan approval a hierarchical code?
concatenation code? vector code? etc? Did you provide codes for
unknown, missing and N/A values? It is not easy to design such things!
Get a copy of SQL PROGRAMMING STYLE and look at the chapters on design
encoding schemes.|||An excellent post Celko. I wish more of your posts were like this.
i.e. You focused more on the solution and the reasons behind it than
insulting folks who are doing it wrong.
When you make posts like this one we can all learn a little bit, without
being offended in the process.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1139090746.005907.99750@.g14g2000cwa.googlegroups.com...
> Stop writing code as if you were an assembly language programmer in
> 1957.
> Machine level things like a BIT or BYTE datatype have no place in a
> high level language like SQL. SQL is a high level language; it is
> abstract and defined without regard to PHYSICAL implementation. This
> basic principle of data modeling is called data abstraction.
> Bits and Bytes are the <i>lowest<i> units of hardware-specific,
> physical implementation you can get. Are you on a high-end or low-end
> machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
> complement or ones complement math? Hey, the standards allow decimal
> machines, so bits do not exist at all!! What about NULLs? To be an
> SQL datatype, you have to have NULLs, so what is a NULL bit? By
> definition a bit, is on or off and has no NULL.
> What does the implementation of the host languages do with bits? Did
> you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
> consistently (look at C# and VB from the same vendor)? That means
> <i>all<i> the host languages -- present, future and not-yet-defined.
> Surely, no good programmer would ever write non-portable code by
> getting to such a low level as bit fiddling!!
> There are two situations in practice. Either the bits are individual
> attributes or they are used as a vector to represent a single
> attribute. In the case of a single attribute, the encoding is limited
> to two values, which do not port to host languages or other SQLs,
> cannot be easily understood by an end user, and which cannot be
> expanded. Use CHAR(1) which will move.
> In the second case what some Newbies, who are still thinking in terms
> of second and third generation programming languages or even punch
> cards, do is build a vector for a series of "yes/no" status codes,
> failing to see the status vector as a single attribute. Did you ever
> play the children's game "20 Questions" when you were young? Bingo!!
> Imagine you have six components for a loan approval, so you allocate
> bits in your second generation model of the world. You have 64 possible
> vectors, but only 5 of them are valid (i.e. you cannot be rejected for
> bankruptcy and still have good credit). For your data integrity, you
> can:
> 1) Ignore the problem. This is actually what <i>most<i> newbies do.
> 2) Write elaborate CHECK() constraints with user defined functions or
> proprietary bit level library functions that cannot port and that run
> like cold glue.
> Now we add a 7-th condition to the vector -- which end does it go on?
> Why? How did you get it in the right place on all the possible
> hardware that it will ever use? Did all the code that references a bit
> in a word by its position do it right after the change?
> You need to sit down and think about how to design an encoding of the
> data that is high level, general enough to expand, abstract and
> portable. For example, is that loan approval a hierarchical code?
> concatenation code? vector code? etc? Did you provide codes for
> unknown, missing and N/A values? It is not easy to design such things!
> Get a copy of SQL PROGRAMMING STYLE and look at the chapters on design
> encoding schemes.
>
I know it's quite common to use BIT fields for boolean values.
CREATE TABLE tblTest (
door BIT DEFAULT 0,
accept BIT DEFAULT 0
)
instead of:
CREATE TABLE tblTest (
door SET('close','open') DEFAULT 'close',
accept SET('no','yes') DEFAULT 'no'
)
(By the way, I use MSSQL and MySQL, not sure if I'm using the right
datatypes for MSSQL)
I reckon the first uses less storage space, but the meaning of the values in
the latter is more unanimous.
So what's best practice? I'm tempted to use the latter, but I almost always
see the first used everywhere.
I'm definitely interested in what CELKO has to say about this.
LisaMy opinion, FWIW
-Comarison operators with bit datatypes will be faster
-You don't have to worry about differences in collations when comparing
-More efficient storage on disk
-You could always add the unanimous-ness (which I'm positive is not a word!)
in a select that extracts the data out of these fields like
select case when door = 0 then 'closed' else 'open' end from tblTest
Proper T-SQL syntax for the character-based table would be
CREATE TABLE tblTest (
door varchar(5) CHECK (door IN ('close','open')) DEFAULT 'close',
accept varchar(5) CHECK (accept IN ('no','yes')) DEFAULT 'no'
)
--
"Lisa Pearlson" wrote:
> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
in
> the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost alway
s
> see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa
>
>|||Here we use the latter one cuz too many different programmers working off
the same database and each programmer has their own programs that they are
responsible for.
I prefer to use the first one to save size.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:O1u6wZDKGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
> in the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost
> always see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa
>|||Lisa Pearlson wrote:
> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
in
> the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost alway
s
> see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa
My main objection to BIT is that its behaviour is too strange and
counter-intuitive. For example, can you predict or explain the results
of the following INSERT statement and the SELECT statement?
CREATE TABLE T (x BIT NOT NULL);
INSERT INTO T VALUES (2);
SELECT MAX(x) FROM T;
In most cases BIT's saving on storage is probably modest. I prefer
concise, readable status codes (CHAR(1) for example) and when I need to
add a third status I don't need to change the datatype I just change a
CHECK constraint.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Like David, I usually use a 1 character flag. In my experience, when I
initially think I have a binary status, it is really a multi-varied
flag. In other words, as soon as I code to tell if the door is open or
closed, someone else wants to know if it's locked.
Payson
Lisa Pearlson wrote:
> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
in
> the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost alway
s
> see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa|||> In other words, as soon as I code to tell if the door is open or
> closed, someone else wants to know if it's locked.
I love the analogy. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Payson" <payson_b@.hotmail.com> wrote in message
news:1138914380.158598.148590@.f14g2000cwb.googlegroups.com...
> Like David, I usually use a 1 character flag. In my experience, when I
> initially think I have a binary status, it is really a multi-varied
> flag. In other words, as soon as I code to tell if the door is open or
> closed, someone else wants to know if it's locked.
> Payson
> Lisa Pearlson wrote:
>|||Lisa--
Great question. One way of clearing this up is by naming your columns in a
way where it won't be ambiguous. If you know that a Door only has two
states, Open and Closed, the meaning will be clear with a BIT datatype if yo
u
use a convention like "IsDoorOpen". That way, it's obvious what "1" and "0"
mean. If it's possible that a door will have more than 2 states in the
future, use a single character {[O]pen, [C]losed, [A]jar} to represent the
state of the door. In that case, you might call your column DoorStatus, or
DoorState... Likewise, you can name your "Accept" column "IsAccepted" and
clarify the meaning there. At my company, we use "Is" to prefix just about
all of the boolean values that we put in our code for just this reason.
HTH
-Dave Markle
"Lisa Pearlson" wrote:
> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
in
> the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost alway
s
> see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa
>
>|||>> In other words, as soon as I code to tell if the door is open or closed,
someone else wants to know if it's locked.
I love the analogy. :-) <
Me, too!! I think I will steal it in the next edition of one of my
books! Hey, Imitation is the sincerest form of flattery; Plagiarism is
the sincerest form of imitation!|||Stop writing code as if you were an assembly language programmer in
1957.
Machine level things like a BIT or BYTE datatype have no place in a
high level language like SQL. SQL is a high level language; it is
abstract and defined without regard to PHYSICAL implementation. This
basic principle of data modeling is called data abstraction.
Bits and Bytes are the <i>lowest<i> units of hardware-specific,
physical implementation you can get. Are you on a high-end or low-end
machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
complement or ones complement math? Hey, the standards allow decimal
machines, so bits do not exist at all!! What about NULLs? To be an
SQL datatype, you have to have NULLs, so what is a NULL bit? By
definition a bit, is on or off and has no NULL.
What does the implementation of the host languages do with bits? Did
you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently (look at C# and VB from the same vendor)? That means
<i>all<i> the host languages -- present, future and not-yet-defined.
Surely, no good programmer would ever write non-portable code by
getting to such a low level as bit fiddling!!
There are two situations in practice. Either the bits are individual
attributes or they are used as a vector to represent a single
attribute. In the case of a single attribute, the encoding is limited
to two values, which do not port to host languages or other SQLs,
cannot be easily understood by an end user, and which cannot be
expanded. Use CHAR(1) which will move.
In the second case what some Newbies, who are still thinking in terms
of second and third generation programming languages or even punch
cards, do is build a vector for a series of "yes/no" status codes,
failing to see the status vector as a single attribute. Did you ever
play the children's game "20 Questions" when you were young? Bingo!!
Imagine you have six components for a loan approval, so you allocate
bits in your second generation model of the world. You have 64 possible
vectors, but only 5 of them are valid (i.e. you cannot be rejected for
bankruptcy and still have good credit). For your data integrity, you
can:
1) Ignore the problem. This is actually what <i>most<i> newbies do.
2) Write elaborate CHECK() constraints with user defined functions or
proprietary bit level library functions that cannot port and that run
like cold glue.
Now we add a 7-th condition to the vector -- which end does it go on?
Why? How did you get it in the right place on all the possible
hardware that it will ever use? Did all the code that references a bit
in a word by its position do it right after the change?
You need to sit down and think about how to design an encoding of the
data that is high level, general enough to expand, abstract and
portable. For example, is that loan approval a hierarchical code?
concatenation code? vector code? etc? Did you provide codes for
unknown, missing and N/A values? It is not easy to design such things!
Get a copy of SQL PROGRAMMING STYLE and look at the chapters on design
encoding schemes.|||An excellent post Celko. I wish more of your posts were like this.
i.e. You focused more on the solution and the reasons behind it than
insulting folks who are doing it wrong.
When you make posts like this one we can all learn a little bit, without
being offended in the process.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1139090746.005907.99750@.g14g2000cwa.googlegroups.com...
> Stop writing code as if you were an assembly language programmer in
> 1957.
> Machine level things like a BIT or BYTE datatype have no place in a
> high level language like SQL. SQL is a high level language; it is
> abstract and defined without regard to PHYSICAL implementation. This
> basic principle of data modeling is called data abstraction.
> Bits and Bytes are the <i>lowest<i> units of hardware-specific,
> physical implementation you can get. Are you on a high-end or low-end
> machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
> complement or ones complement math? Hey, the standards allow decimal
> machines, so bits do not exist at all!! What about NULLs? To be an
> SQL datatype, you have to have NULLs, so what is a NULL bit? By
> definition a bit, is on or off and has no NULL.
> What does the implementation of the host languages do with bits? Did
> you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
> consistently (look at C# and VB from the same vendor)? That means
> <i>all<i> the host languages -- present, future and not-yet-defined.
> Surely, no good programmer would ever write non-portable code by
> getting to such a low level as bit fiddling!!
> There are two situations in practice. Either the bits are individual
> attributes or they are used as a vector to represent a single
> attribute. In the case of a single attribute, the encoding is limited
> to two values, which do not port to host languages or other SQLs,
> cannot be easily understood by an end user, and which cannot be
> expanded. Use CHAR(1) which will move.
> In the second case what some Newbies, who are still thinking in terms
> of second and third generation programming languages or even punch
> cards, do is build a vector for a series of "yes/no" status codes,
> failing to see the status vector as a single attribute. Did you ever
> play the children's game "20 Questions" when you were young? Bingo!!
> Imagine you have six components for a loan approval, so you allocate
> bits in your second generation model of the world. You have 64 possible
> vectors, but only 5 of them are valid (i.e. you cannot be rejected for
> bankruptcy and still have good credit). For your data integrity, you
> can:
> 1) Ignore the problem. This is actually what <i>most<i> newbies do.
> 2) Write elaborate CHECK() constraints with user defined functions or
> proprietary bit level library functions that cannot port and that run
> like cold glue.
> Now we add a 7-th condition to the vector -- which end does it go on?
> Why? How did you get it in the right place on all the possible
> hardware that it will ever use? Did all the code that references a bit
> in a word by its position do it right after the change?
> You need to sit down and think about how to design an encoding of the
> data that is high level, general enough to expand, abstract and
> portable. For example, is that loan approval a hierarchical code?
> concatenation code? vector code? etc? Did you provide codes for
> unknown, missing and N/A values? It is not easy to design such things!
> Get a copy of SQL PROGRAMMING STYLE and look at the chapters on design
> encoding schemes.
>
Saturday, February 25, 2012
Best GUID Storage
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?If you are storing a GUID, then why not use Uniqueidentifier data type.
In SQL Server Books Online, read the page titled "Using uniqueidentifier
Data". This page discusses the advantages and disadvantages of this
datatype.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?|||TW,
I've used both, and it nearly always comes down to interoperability.
Some systems cannot deal with binary so you have to go varchar(36)/char(36).
Where did you get 40, incidentally?
James Hokes
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?|||Excellent choice to use Guids, imho.
Vyas has already pointed you to a good article on the topic, but here are a
couple of extra things not mentioned in that article:
(a) A benefit of using Guids instead of Identities is that if you ever need
to implement horizontal partitioning on the table, it will be substantially
easier with Guids. With Guids, the partitioning process is virtually
seemless to the application but partitioning tables with Identities nearly
always breaks the application.
(b) On the other hand, a problem with using Guids which is not mentioned in
that article is that T-SQL has no ISGUID() type function which causes minor
coding issues. Of course, it's possible to roll your own though.
Regards,
Greg Linwood
SQL Server MVP
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?If you are storing a GUID, then why not use Uniqueidentifier data type.
In SQL Server Books Online, read the page titled "Using uniqueidentifier
Data". This page discusses the advantages and disadvantages of this
datatype.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?|||TW,
I've used both, and it nearly always comes down to interoperability.
Some systems cannot deal with binary so you have to go varchar(36)/char(36).
Where did you get 40, incidentally?
James Hokes
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?|||Excellent choice to use Guids, imho.
Vyas has already pointed you to a good article on the topic, but here are a
couple of extra things not mentioned in that article:
(a) A benefit of using Guids instead of Identities is that if you ever need
to implement horizontal partitioning on the table, it will be substantially
easier with Guids. With Guids, the partitioning process is virtually
seemless to the application but partitioning tables with Identities nearly
always breaks the application.
(b) On the other hand, a problem with using Guids which is not mentioned in
that article is that T-SQL has no ISGUID() type function which causes minor
coding issues. Of course, it's possible to roll your own though.
Regards,
Greg Linwood
SQL Server MVP
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?
Best GUID Storage
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?If you are storing a GUID, then why not use Uniqueidentifier data type.
In SQL Server Books Online, read the page titled "Using uniqueidentifier
Data". This page discusses the advantages and disadvantages of this
datatype.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?|||TW,
I've used both, and it nearly always comes down to interoperability.
Some systems cannot deal with binary so you have to go varchar(36)/char(36).
Where did you get 40, incidentally?
James Hokes
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?|||Excellent choice to use Guids, imho.
Vyas has already pointed you to a good article on the topic, but here are a
couple of extra things not mentioned in that article:
(a) A benefit of using Guids instead of Identities is that if you ever need
to implement horizontal partitioning on the table, it will be substantially
easier with Guids. With Guids, the partitioning process is virtually
seemless to the application but partitioning tables with Identities nearly
always breaks the application.
(b) On the other hand, a problem with using Guids which is not mentioned in
that article is that T-SQL has no ISGUID() type function which causes minor
coding issues. Of course, it's possible to roll your own though.
Regards,
Greg Linwood
SQL Server MVP
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?If you are storing a GUID, then why not use Uniqueidentifier data type.
In SQL Server Books Online, read the page titled "Using uniqueidentifier
Data". This page discusses the advantages and disadvantages of this
datatype.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?|||TW,
I've used both, and it nearly always comes down to interoperability.
Some systems cannot deal with binary so you have to go varchar(36)/char(36).
Where did you get 40, incidentally?
James Hokes
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?|||Excellent choice to use Guids, imho.
Vyas has already pointed you to a good article on the topic, but here are a
couple of extra things not mentioned in that article:
(a) A benefit of using Guids instead of Identities is that if you ever need
to implement horizontal partitioning on the table, it will be substantially
easier with Guids. With Guids, the partitioning process is virtually
seemless to the application but partitioning tables with Identities nearly
always breaks the application.
(b) On the other hand, a problem with using Guids which is not mentioned in
that article is that T-SQL has no ISGUID() type function which causes minor
coding issues. Of course, it's possible to roll your own though.
Regards,
Greg Linwood
SQL Server MVP
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?
Friday, February 24, 2012
best bulk insert command
I have a 5 million row table that gets truncated and new values get imported
.
The new values are obtained by values that have changed in other tables.
Does anyone know the quickest way this can be acheived.
I have tried this took around 34mins
insert in attritable (attrivalue,attri_id,desc)
exec sp_insert
then
Theres a job that bcps the values out to text file in batches of 5000 ..then
inserts them into the table again in batches of 5000 and this takes around 2
8
mins.
Even though BCP is quicker it seems a waste to do it this way and more prone
to errors....Is BCP definately the quickest way to enter data this way does
anyone know'
Thanks for any help or suggestions
Sammy> insert in attritable (attrivalue,attri_id,desc)
> exec sp_insert
Instead of returning a result set that you insert, consider changing
sp_insert to create the new table with SELECT ... INTO and then create
constraints and indexes.
> Even though BCP is quicker it seems a waste to do it this way and more
> prone
> to errors....Is BCP definately the quickest way to enter data this way
> does
> anyone know'
Bulk Insert methods like command-line BCP, Transact-SQL BULK INSERT, DTS and
bulk copy APIs are the fastest way to get external data into SQL Server.
> Theres a job that bcps the values out to text file in batches of 5000
> ..then
> inserts them into the table again in batches of 5000 and this takes around
> 28
> mins.
This calculates to about 3000 rows per second. Not as fast as I would
expect with a narrow table on modern hardware (10,000+) but a lot depends
the size of your data and the kind of indexes you have on the table. You
may find it faster to drop indexes and recreate afterward. See Optimizing
Data Loads at
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx.[/url
]
Hope this helps.
Dan Guzman
SQL Server MVP
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:0B51C58B-7E10-4905-98CB-74CAA211622B@.microsoft.com...
>I have a 5 million row table that gets truncated and new values get
>imported.
> The new values are obtained by values that have changed in other tables.
>
> Does anyone know the quickest way this can be acheived.
> I have tried this took around 34mins
> insert in attritable (attrivalue,attri_id,desc)
> exec sp_insert
> then
> Theres a job that bcps the values out to text file in batches of 5000
> ..then
> inserts them into the table again in batches of 5000 and this takes around
> 28
> mins.
> Even though BCP is quicker it seems a waste to do it this way and more
> prone
> to errors....Is BCP definately the quickest way to enter data this way
> does
> anyone know'
> Thanks for any help or suggestions
> Sammy
>
>
>
>
>
>
>
>
.
The new values are obtained by values that have changed in other tables.
Does anyone know the quickest way this can be acheived.
I have tried this took around 34mins
insert in attritable (attrivalue,attri_id,desc)
exec sp_insert
then
Theres a job that bcps the values out to text file in batches of 5000 ..then
inserts them into the table again in batches of 5000 and this takes around 2
8
mins.
Even though BCP is quicker it seems a waste to do it this way and more prone
to errors....Is BCP definately the quickest way to enter data this way does
anyone know'
Thanks for any help or suggestions
Sammy> insert in attritable (attrivalue,attri_id,desc)
> exec sp_insert
Instead of returning a result set that you insert, consider changing
sp_insert to create the new table with SELECT ... INTO and then create
constraints and indexes.
> Even though BCP is quicker it seems a waste to do it this way and more
> prone
> to errors....Is BCP definately the quickest way to enter data this way
> does
> anyone know'
Bulk Insert methods like command-line BCP, Transact-SQL BULK INSERT, DTS and
bulk copy APIs are the fastest way to get external data into SQL Server.
> Theres a job that bcps the values out to text file in batches of 5000
> ..then
> inserts them into the table again in batches of 5000 and this takes around
> 28
> mins.
This calculates to about 3000 rows per second. Not as fast as I would
expect with a narrow table on modern hardware (10,000+) but a lot depends
the size of your data and the kind of indexes you have on the table. You
may find it faster to drop indexes and recreate afterward. See Optimizing
Data Loads at
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx.[/url
]
Hope this helps.
Dan Guzman
SQL Server MVP
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:0B51C58B-7E10-4905-98CB-74CAA211622B@.microsoft.com...
>I have a 5 million row table that gets truncated and new values get
>imported.
> The new values are obtained by values that have changed in other tables.
>
> Does anyone know the quickest way this can be acheived.
> I have tried this took around 34mins
> insert in attritable (attrivalue,attri_id,desc)
> exec sp_insert
> then
> Theres a job that bcps the values out to text file in batches of 5000
> ..then
> inserts them into the table again in batches of 5000 and this takes around
> 28
> mins.
> Even though BCP is quicker it seems a waste to do it this way and more
> prone
> to errors....Is BCP definately the quickest way to enter data this way
> does
> anyone know'
> Thanks for any help or suggestions
> Sammy
>
>
>
>
>
>
>
>
Thursday, February 16, 2012
Beginners SqlDataSource SelectCommand Question
Hi folks,
I'm having problems using the SqlDataSource to return certain values from a SQL database. I have two DropDownLists. When the first DDL's selected index is changed, I need to take the value (an int id) from the field, use the value to look up a string in a different colum in the table for that record id, which should then be used to automatically select a value from the second DDL. I think my problem is I'm not sure how to get the SelectCommand to actually return the value, let alone in string form!
Here is my code:
{
string selectedProject = ProjectDDL.SelectedValue;
SqlDataSource temp = new SqlDataSource();
temp.ConnectionString = rootWebConfig.ConnectionStrings.ConnectionStrings["DBConnectionString"].ToString();
temp.SelectParameters.Add("id", selectedProject);
temp.SelectCommand = "SELECT [username] FROM dbo.projects WHERE id = @.id";
//temp.Select(); ?
//AssigneeField.SelectedValue = string returned from Select Statement!
}
I'd appreciate it if somone could point me in the right direction?
Thanks,
Ally
Hi,
you can either use a command object with a dataadaptor or a datareader to retrieve data from your command
assuming you are only returning one String value, using a datareader would be better on your system:
String result = "";
SqlCommand command = New SqlCommand( _
"SELECT CategoryID, CategoryName FROM dbo.Categories;" & _
"SELECT EmployeeID, LastName FROM dbo.Employees", connection);
command.parameters.add(...);
connection.Open();
sqldatareader reader = command.executereader();
Do While reader.Read()
<<<<<Do your processing with the data here>>>>>
result = reader(<columnNum>);
Loop
reader.Close();
return result
i'm sorry about the syntax errors as im not c# trained...
Hope this helps...
Excellent, exactly what I needed, thanks! I was having problems with SqlDataReader.ExecuteReader() constantly returning anException:
System.InvalidOperationException: Invalid attempt to read when no data is present."
I'd accidently omitted the inital SqlDataReader.Read() call, fought with it for ages until I realised the reader doesn't move on to the first record until SqlDataRead.Read() is called for the first time, makes sense I guess! All working now :)
Thanks again,
Ally
Sunday, February 12, 2012
Begining Queries
I am a novice SQL guy so you all take it easy on me.
I am trying to run a query and one of the columns of my has values that is
referenced in another table, i.e. for EmployedStatus I have a bunch a
numbers from aother table. I go to that table to see what the numbers mean
and there you go. What I would like to do is, say I am working with person
profile table and instead of showing the column "EmployedStatus" as a 63
(for example) for a record, I would like it to show what that 63 means from
the "desription" column of the table that the 63 is referenced to. I hope
that make sense and I would appreciate any help anyone can stand to stomach!
Thanks,
Aaron
It's ok Aaron, we all started out somewhere.
For reference, look in Books Online for the topic JOIN TABLES -its a complex set of topics and takes most of us a lot of practice to get it right.
Now about your problem. I'll create an example. (You can copy and paste this code into Query Analyzer and run it.)
SET NOCOUNT ON
CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int
)
CREATE TABLE TableB
( Status int,
Description varchar(50)
)
INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
INSERT INTO TableB VALUES ( 63, 'On Leave' )
--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = b.Description
FROM PersonProfile p
JOIN TableB b
ON p.EmployedStatus = b.Status
--Clean up
DROP TABLE PersonProfile
DROP TABLE TableB
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Aaron" <support@.pinetreeit.com> wrote in message news:F61A75EB-2968-4356-A560-A5C2A9853B7E@.microsoft.com...
>I am a novice SQL guy so you all take it easy on me.
> I am trying to run a query and one of the columns of my has values that is
> referenced in another table, i.e. for EmployedStatus I have a bunch a
> numbers from aother table. I go to that table to see what the numbers mean
> and there you go. What I would like to do is, say I am working with person
> profile table and instead of showing the column "EmployedStatus" as a 63
> (for example) for a record, I would like it to show what that 63 means from
> the "desription" column of the table that the 63 is referenced to. I hope
> that make sense and I would appreciate any help anyone can stand to stomach!
> Thanks,
> Aaron
>
|||Thanks Arnie for the info, worked great.
If I could ask one more; How would I modify this code to to be able to
reference more than one field that uses the same table? For example to
continue with our example, I have a an EmployedStatusID that is referenced
by a number in an ID Table and it corresponding Description of Full-Time,
Part-Tim, etc. I also have a StudentStatusID that is referenced by a number
in the same ID table with it's corresponding Description as Full-Time,
Half-Time, etc.. I tried to use the AND along with the Join and when I
added more than one, no data was displayed. If I run separate queries on
both, they show up correctly. Any suggestions?
Thanks again,
Aaron
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23JTR$oK$GHA.3584@.TK2MSFTNGP05.phx.gbl...
It's ok Aaron, we all started out somewhere.
For reference, look in Books Online for the topic JOIN TABLES -its a complex
set of topics and takes most of us a lot of practice to get it right.
Now about your problem. I'll create an example. (You can copy and paste this
code into Query Analyzer and run it.)
SET NOCOUNT ON
CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int
)
CREATE TABLE TableB
( Status int,
Description varchar(50)
)
INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
INSERT INTO TableB VALUES ( 63, 'On Leave' )
--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = b.Description
FROM PersonProfile p
JOIN TableB b
ON p.EmployedStatus = b.Status
--Clean up
DROP TABLE PersonProfile
DROP TABLE TableB
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Aaron" <support@.pinetreeit.com> wrote in message
news:F61A75EB-2968-4356-A560-A5C2A9853B7E@.microsoft.com...
>I am a novice SQL guy so you all take it easy on me.
> I am trying to run a query and one of the columns of my has values that is
> referenced in another table, i.e. for EmployedStatus I have a bunch a
> numbers from aother table. I go to that table to see what the numbers
> mean
> and there you go. What I would like to do is, say I am working with
> person
> profile table and instead of showing the column "EmployedStatus" as a 63
> (for example) for a record, I would like it to show what that 63 means
> from
> the "desription" column of the table that the 63 is referenced to. I hope
> that make sense and I would appreciate any help anyone can stand to
> stomach!
> Thanks,
> Aaron
>
|||Aaron,
As you noticed, trying to retrieve fields from a JOIN table where the JOIN criteria is different doesn't work.
So there are a couple of different ways to make this work. One involves using a JOIN with a second virtual copy of the table, and the other involves using a sub-SELECT.
First, the JOIN using a second copy of the same table, and then the sub-select.
SET NOCOUNT ON
CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int,
StudentStatus int
)
CREATE TABLE Status
( StatusID int,
Description varchar(50)
)
INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63, 23 )
INSERT INTO Status VALUES ( 63, 'On Leave' )
INSERT INTO Status VALUES ( 21, 'Full Time' )
INSERT INTO Status VALUES ( 21, 'Half Time' )
INSERT INTO Status VALUES ( 23, 'Part Time' )
--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = s1.Description,
StudentStatus = s2.Description
FROM PersonProfile p
JOIN Status s1
ON p.EmployedStatus = s1.StatusID
JOIN Status s2
ON p.StudentStatus = s2.StatusID
--Use a Sub-SELECT
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = ( SELECT Description
FROM Status
WHERE StatusID = p.EmployedStatus
),
StudentStatus = ( SELECT Description
FROM Status
WHERE StatusID = p.StudentStatus
)
FROM PersonProfile p
--Clean up
DROP TABLE PersonProfile
DROP TABLE Status
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Aaron" <support@.pinetreeit.com> wrote in message news:129A1BD8-33A4-40D8-8B41-BC3A33BE3AEC@.microsoft.com...[vbcol=seagreen]
> Thanks Arnie for the info, worked great.
> If I could ask one more; How would I modify this code to to be able to
> reference more than one field that uses the same table? For example to
> continue with our example, I have a an EmployedStatusID that is referenced
> by a number in an ID Table and it corresponding Description of Full-Time,
> Part-Tim, etc. I also have a StudentStatusID that is referenced by a number
> in the same ID table with it's corresponding Description as Full-Time,
> Half-Time, etc.. I tried to use the AND along with the Join and when I
> added more than one, no data was displayed. If I run separate queries on
> both, they show up correctly. Any suggestions?
> Thanks again,
> Aaron
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23JTR$oK$GHA.3584@.TK2MSFTNGP05.phx.gbl...
> It's ok Aaron, we all started out somewhere.
> For reference, look in Books Online for the topic JOIN TABLES -its a complex
> set of topics and takes most of us a lot of practice to get it right.
> Now about your problem. I'll create an example. (You can copy and paste this
> code into Query Analyzer and run it.)
> SET NOCOUNT ON
> CREATE TABLE PersonProfile
> ( EmployeeID int IDENTITY,
> LastName varchar(20),
> FirstName varchar(20),
> EmployedStatus int
> )
> CREATE TABLE TableB
> ( Status int,
> Description varchar(50)
> )
> INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
> INSERT INTO TableB VALUES ( 63, 'On Leave' )
> --Join the two tables
> SELECT
> p.EmployeeID,
> p.LastName,
> p.FirstName,
> CurrentStatus = b.Description
> FROM PersonProfile p
> JOIN TableB b
> ON p.EmployedStatus = b.Status
> --Clean up
> DROP TABLE PersonProfile
> DROP TABLE TableB
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Aaron" <support@.pinetreeit.com> wrote in message
> news:F61A75EB-2968-4356-A560-A5C2A9853B7E@.microsoft.com...
I am trying to run a query and one of the columns of my has values that is
referenced in another table, i.e. for EmployedStatus I have a bunch a
numbers from aother table. I go to that table to see what the numbers mean
and there you go. What I would like to do is, say I am working with person
profile table and instead of showing the column "EmployedStatus" as a 63
(for example) for a record, I would like it to show what that 63 means from
the "desription" column of the table that the 63 is referenced to. I hope
that make sense and I would appreciate any help anyone can stand to stomach!
Thanks,
Aaron
It's ok Aaron, we all started out somewhere.
For reference, look in Books Online for the topic JOIN TABLES -its a complex set of topics and takes most of us a lot of practice to get it right.
Now about your problem. I'll create an example. (You can copy and paste this code into Query Analyzer and run it.)
SET NOCOUNT ON
CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int
)
CREATE TABLE TableB
( Status int,
Description varchar(50)
)
INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
INSERT INTO TableB VALUES ( 63, 'On Leave' )
--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = b.Description
FROM PersonProfile p
JOIN TableB b
ON p.EmployedStatus = b.Status
--Clean up
DROP TABLE PersonProfile
DROP TABLE TableB
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Aaron" <support@.pinetreeit.com> wrote in message news:F61A75EB-2968-4356-A560-A5C2A9853B7E@.microsoft.com...
>I am a novice SQL guy so you all take it easy on me.
> I am trying to run a query and one of the columns of my has values that is
> referenced in another table, i.e. for EmployedStatus I have a bunch a
> numbers from aother table. I go to that table to see what the numbers mean
> and there you go. What I would like to do is, say I am working with person
> profile table and instead of showing the column "EmployedStatus" as a 63
> (for example) for a record, I would like it to show what that 63 means from
> the "desription" column of the table that the 63 is referenced to. I hope
> that make sense and I would appreciate any help anyone can stand to stomach!
> Thanks,
> Aaron
>
|||Thanks Arnie for the info, worked great.
If I could ask one more; How would I modify this code to to be able to
reference more than one field that uses the same table? For example to
continue with our example, I have a an EmployedStatusID that is referenced
by a number in an ID Table and it corresponding Description of Full-Time,
Part-Tim, etc. I also have a StudentStatusID that is referenced by a number
in the same ID table with it's corresponding Description as Full-Time,
Half-Time, etc.. I tried to use the AND along with the Join and when I
added more than one, no data was displayed. If I run separate queries on
both, they show up correctly. Any suggestions?
Thanks again,
Aaron
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23JTR$oK$GHA.3584@.TK2MSFTNGP05.phx.gbl...
It's ok Aaron, we all started out somewhere.
For reference, look in Books Online for the topic JOIN TABLES -its a complex
set of topics and takes most of us a lot of practice to get it right.
Now about your problem. I'll create an example. (You can copy and paste this
code into Query Analyzer and run it.)
SET NOCOUNT ON
CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int
)
CREATE TABLE TableB
( Status int,
Description varchar(50)
)
INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
INSERT INTO TableB VALUES ( 63, 'On Leave' )
--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = b.Description
FROM PersonProfile p
JOIN TableB b
ON p.EmployedStatus = b.Status
--Clean up
DROP TABLE PersonProfile
DROP TABLE TableB
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Aaron" <support@.pinetreeit.com> wrote in message
news:F61A75EB-2968-4356-A560-A5C2A9853B7E@.microsoft.com...
>I am a novice SQL guy so you all take it easy on me.
> I am trying to run a query and one of the columns of my has values that is
> referenced in another table, i.e. for EmployedStatus I have a bunch a
> numbers from aother table. I go to that table to see what the numbers
> mean
> and there you go. What I would like to do is, say I am working with
> person
> profile table and instead of showing the column "EmployedStatus" as a 63
> (for example) for a record, I would like it to show what that 63 means
> from
> the "desription" column of the table that the 63 is referenced to. I hope
> that make sense and I would appreciate any help anyone can stand to
> stomach!
> Thanks,
> Aaron
>
|||Aaron,
As you noticed, trying to retrieve fields from a JOIN table where the JOIN criteria is different doesn't work.
So there are a couple of different ways to make this work. One involves using a JOIN with a second virtual copy of the table, and the other involves using a sub-SELECT.
First, the JOIN using a second copy of the same table, and then the sub-select.
SET NOCOUNT ON
CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int,
StudentStatus int
)
CREATE TABLE Status
( StatusID int,
Description varchar(50)
)
INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63, 23 )
INSERT INTO Status VALUES ( 63, 'On Leave' )
INSERT INTO Status VALUES ( 21, 'Full Time' )
INSERT INTO Status VALUES ( 21, 'Half Time' )
INSERT INTO Status VALUES ( 23, 'Part Time' )
--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = s1.Description,
StudentStatus = s2.Description
FROM PersonProfile p
JOIN Status s1
ON p.EmployedStatus = s1.StatusID
JOIN Status s2
ON p.StudentStatus = s2.StatusID
--Use a Sub-SELECT
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = ( SELECT Description
FROM Status
WHERE StatusID = p.EmployedStatus
),
StudentStatus = ( SELECT Description
FROM Status
WHERE StatusID = p.StudentStatus
)
FROM PersonProfile p
--Clean up
DROP TABLE PersonProfile
DROP TABLE Status
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Aaron" <support@.pinetreeit.com> wrote in message news:129A1BD8-33A4-40D8-8B41-BC3A33BE3AEC@.microsoft.com...[vbcol=seagreen]
> Thanks Arnie for the info, worked great.
> If I could ask one more; How would I modify this code to to be able to
> reference more than one field that uses the same table? For example to
> continue with our example, I have a an EmployedStatusID that is referenced
> by a number in an ID Table and it corresponding Description of Full-Time,
> Part-Tim, etc. I also have a StudentStatusID that is referenced by a number
> in the same ID table with it's corresponding Description as Full-Time,
> Half-Time, etc.. I tried to use the AND along with the Join and when I
> added more than one, no data was displayed. If I run separate queries on
> both, they show up correctly. Any suggestions?
> Thanks again,
> Aaron
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23JTR$oK$GHA.3584@.TK2MSFTNGP05.phx.gbl...
> It's ok Aaron, we all started out somewhere.
> For reference, look in Books Online for the topic JOIN TABLES -its a complex
> set of topics and takes most of us a lot of practice to get it right.
> Now about your problem. I'll create an example. (You can copy and paste this
> code into Query Analyzer and run it.)
> SET NOCOUNT ON
> CREATE TABLE PersonProfile
> ( EmployeeID int IDENTITY,
> LastName varchar(20),
> FirstName varchar(20),
> EmployedStatus int
> )
> CREATE TABLE TableB
> ( Status int,
> Description varchar(50)
> )
> INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
> INSERT INTO TableB VALUES ( 63, 'On Leave' )
> --Join the two tables
> SELECT
> p.EmployeeID,
> p.LastName,
> p.FirstName,
> CurrentStatus = b.Description
> FROM PersonProfile p
> JOIN TableB b
> ON p.EmployedStatus = b.Status
> --Clean up
> DROP TABLE PersonProfile
> DROP TABLE TableB
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Aaron" <support@.pinetreeit.com> wrote in message
> news:F61A75EB-2968-4356-A560-A5C2A9853B7E@.microsoft.com...
Subscribe to:
Posts (Atom)