can it be overdone with check statements?

Hi,
I would assume most columns in a db would have a known valid range of values
that it makes sense for the business logics. For example, most counting
columns shouldn't have negative value even though it makes some mathetical
sense but high level application code typically doesn't handle it well. So
I'm wondering if experienced db designers would think it's worth adding a
huge check constraint to make sure all values entered into the table are
within the valid (or expected) range. thank you very much.
I'm a great fan of CHECK constraints!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Zeng" <Zeng5000@.hotmail.com> wrote in message news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl...
> Hi,
> I would assume most columns in a db would have a known valid range of values
> that it makes sense for the business logics. For example, most counting
> columns shouldn't have negative value even though it makes some mathetical
> sense but high level application code typically doesn't handle it well. So
> I'm wondering if experienced db designers would think it's worth adding a
> huge check constraint to make sure all values entered into the table are
> within the valid (or expected) range. thank you very much.
>
|||Just like Tibor, I am a great fan of Check constraints and use them
generously. However you can probably overdo anything...The reason I use so
many check constraints is that it protects the database against faulty code,
and users who bypass the application using Excel, QA, SEM or any of the
other tools easily obtainable.
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
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl...
> Hi,
> I would assume most columns in a db would have a known valid range of
> values
> that it makes sense for the business logics. For example, most counting
> columns shouldn't have negative value even though it makes some mathetical
> sense but high level application code typically doesn't handle it well.
> So
> I'm wondering if experienced db designers would think it's worth adding a
> huge check constraint to make sure all values entered into the table are
> within the valid (or expected) range. thank you very much.
>
|||is there any advantage of having one for each isolated column in a table or
a huge one per table (with the use of AND and OR operator)?
Thanks!
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uKtOgJCfFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Just like Tibor, I am a great fan of Check constraints and use them
> generously. However you can probably overdo anything...The reason I use so
> many check constraints is that it protects the database against faulty
code,[vbcol=seagreen]
> and users who bypass the application using Excel, QA, SEM or any of the
> other tools easily obtainable.
> --
> 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
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl...
mathetical[vbcol=seagreen]
a
>

can it be overdone with check statements?

Hi,
I would assume most columns in a db would have a known valid range of values
that it makes sense for the business logics. For example, most counting
columns shouldn't have negative value even though it makes some mathetical
sense but high level application code typically doesn't handle it well. So
I'm wondering if experienced db designers would think it's worth adding a
huge check constraint to make sure all values entered into the table are
within the valid (or expected) range. thank you very much.I'm a great fan of CHECK constraints!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Zeng" <Zeng5000@.hotmail.com> wrote in message news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl..
.
> Hi,
> I would assume most columns in a db would have a known valid range of valu
es
> that it makes sense for the business logics. For example, most counting
> columns shouldn't have negative value even though it makes some mathetical
> sense but high level application code typically doesn't handle it well. S
o
> I'm wondering if experienced db designers would think it's worth adding a
> huge check constraint to make sure all values entered into the table are
> within the valid (or expected) range. thank you very much.
>|||Just like Tibor, I am a great fan of Check constraints and use them
generously. However you can probably overdo anything...The reason I use so
many check constraints is that it protects the database against faulty code,
and users who bypass the application using Excel, QA, SEM or any of the
other tools easily obtainable.
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
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl...
> Hi,
> I would assume most columns in a db would have a known valid range of
> values
> that it makes sense for the business logics. For example, most counting
> columns shouldn't have negative value even though it makes some mathetical
> sense but high level application code typically doesn't handle it well.
> So
> I'm wondering if experienced db designers would think it's worth adding a
> huge check constraint to make sure all values entered into the table are
> within the valid (or expected) range. thank you very much.
>|||is there any advantage of having one for each isolated column in a table or
a huge one per table (with the use of AND and OR operator)?
Thanks!
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uKtOgJCfFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Just like Tibor, I am a great fan of Check constraints and use them
> generously. However you can probably overdo anything...The reason I use so
> many check constraints is that it protects the database against faulty
code,
> and users who bypass the application using Excel, QA, SEM or any of the
> other tools easily obtainable.
> --
> 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
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl...
mathetical[vbcol=seagreen]
a[vbcol=seagreen]
>

can it be overdone with check statements?

Hi,
I would assume most columns in a db would have a known valid range of values
that it makes sense for the business logics. For example, most counting
columns shouldn't have negative value even though it makes some mathetical
sense but high level application code typically doesn't handle it well. So
I'm wondering if experienced db designers would think it's worth adding a
huge check constraint to make sure all values entered into the table are
within the valid (or expected) range. thank you very much.I'm a great fan of CHECK constraints!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Zeng" <Zeng5000@.hotmail.com> wrote in message news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl...
> Hi,
> I would assume most columns in a db would have a known valid range of values
> that it makes sense for the business logics. For example, most counting
> columns shouldn't have negative value even though it makes some mathetical
> sense but high level application code typically doesn't handle it well. So
> I'm wondering if experienced db designers would think it's worth adding a
> huge check constraint to make sure all values entered into the table are
> within the valid (or expected) range. thank you very much.
>|||Just like Tibor, I am a great fan of Check constraints and use them
generously. However you can probably overdo anything...The reason I use so
many check constraints is that it protects the database against faulty code,
and users who bypass the application using Excel, QA, SEM or any of the
other tools easily obtainable.
--
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
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl...
> Hi,
> I would assume most columns in a db would have a known valid range of
> values
> that it makes sense for the business logics. For example, most counting
> columns shouldn't have negative value even though it makes some mathetical
> sense but high level application code typically doesn't handle it well.
> So
> I'm wondering if experienced db designers would think it's worth adding a
> huge check constraint to make sure all values entered into the table are
> within the valid (or expected) range. thank you very much.
>|||is there any advantage of having one for each isolated column in a table or
a huge one per table (with the use of AND and OR operator)?
Thanks!
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uKtOgJCfFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Just like Tibor, I am a great fan of Check constraints and use them
> generously. However you can probably overdo anything...The reason I use so
> many check constraints is that it protects the database against faulty
code,
> and users who bypass the application using Excel, QA, SEM or any of the
> other tools easily obtainable.
> --
> 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
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I would assume most columns in a db would have a known valid range of
> > values
> > that it makes sense for the business logics. For example, most counting
> > columns shouldn't have negative value even though it makes some
mathetical
> > sense but high level application code typically doesn't handle it well.
> > So
> > I'm wondering if experienced db designers would think it's worth adding
a
> > huge check constraint to make sure all values entered into the table are
> > within the valid (or expected) range. thank you very much.
> >
> >
>

Can I set a default range for the identity values for merge replication?

Hello,

We have a couple of tables that can have quite a bit of data each day prior to replication. Can we increase the default values for a table for each subscription? For example we have a table called table1 and on the sqlexpress client they could enter in 10000 rows a day, on table2 it's just 100 rows a day. How can we increase the values to where we do not get the error for table1 stating that the insert failed because it conflicted with the identity range check constraint. Thanks in advance.

Hi, John

If I understand correctly, you actually want to set different identityrange from different article. For example, table1 identity range is 10,000 and table2 with identity range is 1,000. You can set it through sp_addmergearticle with control values of @.identityrangemanagementoption, @.pub_identity_range, and @.identity_range.

BTW, you are not allowed to set different identity range for different subscriptions which subscribe to the same publication.

Hope these information will help.

Thanks

Yunjing