If we alter the table by disabling the constraints (use NOCHECK CONSTRAINT
ALL). Is there any command to verify that the constraints are disabled or
don't exist.

Check out sp_helpconstraint in the BOL.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"SQL_Learner" <SQL_Learner (AT) discussions (DOT) microsoft.com> wrote

If we alter the table by disabling the constraints (use NOCHECK CONSTRAINT
ALL). Is there any command to verify that the constraints are disabled or
don't exist.

Thanks Tom, this command is what I am looking for.

In SQL server, without checking if we enable all constraints (irrespective
of they are enabled or disabled at that point) are the any issues? What are
your thoughts on this.

Thanks in Advance

"Tom Moreau" wrote:

That depends. If you attempt to enable the constraints WITH CHECK, and
there are existing violations of those constraints, then re-enabling will
fail. However, if you re-enable WITH NOCHECK, then it will succeed.

That said, if you have a partitioned view, then you'd want to use WITH
CHECK, so as to take advantage of the performance benefits that having such
constraints will give you.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"SQL_Learner" <SQLLearner (AT) discussions (DOT) microsoft.com> wrote

Thanks Tom, this command is what I am looking for.

In SQL server, without checking if we enable all constraints (irrespective
of they are enabled or disabled at that point) are the any issues? What are
your thoughts on this.

Thanks in Advance

"Tom Moreau" wrote:

I am enabling constraint in the following way:

STMT1: ALTER TABLE Customers CHECK CONSTRAINT ALL

Disabling as:

STMT2: ALTER TABLE Customers NOCHECK CONSTRAINT ALL

In disabled or enabled state, if I am running STMT1 again and again in the
Query Analyser, I am not getting any error. Does that mean in disabled state
the SQL server will enable the constraints and in enabled state it will
ignore to reenable the constraints?

What is the performance issue in this case where I don't have a partitioned
view.

Thanks!

Yes, it will ignore things if they're already enabled.

In some cases, you could get a performance hit if you're doing a query like:

select
*
from
MyTable m
where exists
(
select
*
from
OtherTable o
where
o.FK = m.PK
)

.... and you've disabled the foreign key from OtherTable to MyTable or
re-enabled it with NOCHECK. The optimizer can take advantage of the fact
that it knows something about the data in OtherTable, due to the constraint.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"SQL_Learner" <SQLLearner (AT) discussions (DOT) microsoft.com> wrote

I am enabling constraint in the following way:

STMT1: ALTER TABLE Customers CHECK CONSTRAINT ALL

Disabling as:

STMT2: ALTER TABLE Customers NOCHECK CONSTRAINT ALL

In disabled or enabled state, if I am running STMT1 again and again in the
Query Analyser, I am not getting any error. Does that mean in disabled state
the SQL server will enable the constraints and in enabled state it will
ignore to reenable the constraints?

What is the performance issue in this case where I don't have a partitioned
view.

Thanks!

Thanks Tom for your post!