Index Question

[Rob Panosh]

Hello,

In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys be
disabled? If Indexes (PK's and UN Constraints) can be disabled what happens
if data is inserted while disable? Will the index be rebuilt when enabled?

Thanks,
Rob Panosh

[Erland Sommarskog]

Re: Index Question

Rob Panosh (rob_!!!NO!!!SP AM!!!_panosh@as dsoftadfdware.c om) writes:[color=blue]
> In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> happens if data is inserted while disable? Will the index be rebuilt
> when enabled?[/color]

You can disable foreign-key constraints. When you re-enable them, SQL
Server verifies that the data comply to the constraint.

You cannot disable primary-key or unique constraints, nor indexes.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Rob Panosh]

Re: Index Question

Thanks ....

"Erland Sommarskog" <sommar@algonet .se> wrote in message
news:Xns940E15C 48E6CYazorman@1 27.0.0.1...[color=blue]
> Rob Panosh (rob_!!!NO!!!SP AM!!!_panosh@as dsoftadfdware.c om) writes:[color=green]
> > In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> > be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> > happens if data is inserted while disable? Will the index be rebuilt
> > when enabled?[/color]
>
> You can disable foreign-key constraints. When you re-enable them, SQL
> Server verifies that the data comply to the constraint.
>
> You cannot disable primary-key or unique constraints, nor indexes.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet. se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Gert-Jan Strik]

Re: Index Question

Unfortunately, SQL-Server does not automatically check the existing
table data after turning a constraint back on. IMO it is a Microsoft
mistake to allow a database to get corrupted this way.

Gert-Jan


Erland Sommarskog wrote:[color=blue]
>
> Rob Panosh (rob_!!!NO!!!SP AM!!!_panosh@as dsoftadfdware.c om) writes:[color=green]
> > In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> > be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> > happens if data is inserted while disable? Will the index be rebuilt
> > when enabled?[/color]
>
> You can disable foreign-key constraints. When you re-enable them, SQL
> Server verifies that the data comply to the constraint.
>
> You cannot disable primary-key or unique constraints, nor indexes.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet. se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Erland Sommarskog]

Re: Index Question

Gert-Jan Strik (sorry@toomuchs pamalready.nl) writes:[color=blue]
> Unfortunately, SQL-Server does not automatically check the existing
> table data after turning a constraint back on.[/color]

You're right, Gert-Jan. Thanks for correcting my mistake.
[color=blue]
> IMO it is a Microsoft mistake to allow a database to get corrupted this
> way.[/color]

I can see situations where you may want this, but its deceivable that
the constraint is not rechecked. Not only it makes you think that you
have a sound table. If you use the column with a CHECK constraint in a
partitioned view, you will scratch your hair, trying to find out why
SQL Server accesses all tables after this operation.

It is possible to identify this situation though. The example is
augmented script from Books Online:

SET QUOTED_IDENTIFI ER OFF
go
CREATE TABLE cnst_example
(id INT NOT NULL,
name VARCHAR(10) NOT NULL,
salary MONEY NOT NULL
CONSTRAINT salary_cap CHECK (salary < 100000)
)
go
-- Valid inserts
INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)
go
-- This insert violates the constraint.
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
go
-- Disable the constraint and try again.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
go
-- Reenable the constraint and try another insert, will fail.
ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (4,"Eric James",110000)
go
-- Returns 1, because constraint has been disabled.
select objectproperty( object_id('sala ry_cap'), 'CnstIsNotTrust ed')



--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Gert-Jan Strik]

Re: Index Question

"select objectproperty( object_id('sala ry_cap'), 'CnstIsNotTrust ed')"

Interesting, I did not know this yet. Something for a standard script to
check the database...

Gert-Jan



Erland Sommarskog wrote:[color=blue]
>
> Gert-Jan Strik (sorry@toomuchs pamalready.nl) writes:[color=green]
> > Unfortunately, SQL-Server does not automatically check the existing
> > table data after turning a constraint back on.[/color]
>
> You're right, Gert-Jan. Thanks for correcting my mistake.
>[color=green]
> > IMO it is a Microsoft mistake to allow a database to get corrupted this
> > way.[/color]
>
> I can see situations where you may want this, but its deceivable that
> the constraint is not rechecked. Not only it makes you think that you
> have a sound table. If you use the column with a CHECK constraint in a
> partitioned view, you will scratch your hair, trying to find out why
> SQL Server accesses all tables after this operation.
>
> It is possible to identify this situation though. The example is
> augmented script from Books Online:
>
> SET QUOTED_IDENTIFI ER OFF
> go
> CREATE TABLE cnst_example
> (id INT NOT NULL,
> name VARCHAR(10) NOT NULL,
> salary MONEY NOT NULL
> CONSTRAINT salary_cap CHECK (salary < 100000)
> )
> go
> -- Valid inserts
> INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
> INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)
> go
> -- This insert violates the constraint.
> INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
> go
> -- Disable the constraint and try again.
> ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
> INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
> go
> -- Reenable the constraint and try another insert, will fail.
> ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
> INSERT INTO cnst_example VALUES (4,"Eric James",110000)
> go
> -- Returns 1, because constraint has been disabled.
> select objectproperty( object_id('sala ry_cap'), 'CnstIsNotTrust ed')
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet. se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]