Index Question

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 PanoshRob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> 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?

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/techin.../2000/books.asp|||Thanks ...

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns940E15C48E6CYazorman@.127.0.0.1...
> Rob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> > 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?
> 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/techin.../2000/books.asp|||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:
> Rob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> > 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?
> 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/techin.../2000/books.asp|||Gert-Jan Strik (sorry@.toomuchspamalready.nl) writes:
> Unfortunately, SQL-Server does not automatically check the existing
> table data after turning a constraint back on.

You're right, Gert-Jan. Thanks for correcting my mistake.

> IMO it is a Microsoft mistake to allow a database to get corrupted this
> way.

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_IDENTIFIER 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('salary_cap'), 'CnstIsNotTrusted')

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"select objectproperty(object_id('salary_cap'), 'CnstIsNotTrusted')"

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

Gert-Jan

Erland Sommarskog wrote:
> Gert-Jan Strik (sorry@.toomuchspamalready.nl) writes:
> > Unfortunately, SQL-Server does not automatically check the existing
> > table data after turning a constraint back on.
> You're right, Gert-Jan. Thanks for correcting my mistake.
> > IMO it is a Microsoft mistake to allow a database to get corrupted this
> > way.
> 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_IDENTIFIER 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('salary_cap'), 'CnstIsNotTrusted')
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Index creation in SQL Server 2005 Management Studio - Page locks disabled by default

When I create an index on a table using SQL Server Management Studio, the index has page locks disabled by default. I'd prefer to have page locks enabled by default so neither I nor any other developer has to remember to manually modify. How can I accomplish this?

Thanks,

Pat Brickson

exec sp_indexoption '<TableName>', 'DisAllowPageLocks','FALSE'|||

Thank you, that solves my problem for existing tables. How can I ensure that this is the default for all new tables as well?

The CREATE INDEX statement in T-SQL enables page level locks by default but if I create via Management Studio, they're disabled by default. Why is there a discrepency?

Thanks, Pat

|||

After testing, I find that this only affects indexes that already exist on the table. I'd like to ensure that any new indexes on this table (or any existing or new table for that matter) have page locks enabled. Unfortunately this stored procedure doesn't really help me any more than just manually altering the index's options via the index properties dialog in Management Studio.

Again, I appreciate any assistance. Any other ideas?

Thanks, Pat

|||

The default settings for new indexes are hard coded in the dialog to match the defaults in SQL Server if you don't specify any options. There is no way for end users to change these defaults in the dialog.

Thanks,
Steve

|||

Maybe I misundertand you, but isn't the default to allow page locks when accessing the database? That's the default when issuing a T-SQL CREATE INDEX statement when no options are specified.

I'm really not concerned with end users changing the default. I'm more interested in ensuring that an any index created via Management Studio by a developer or DBA will have page locks enabled. Since that doesn't seem to be the default when creating in management studio, is there a way for me to specify which defaults the indexes should take?

Thanks, Pat

|||

If the dialog defaults aren't matching the default behavior in T-SQL, that's not intentional. Please file a defect report for this on http://connect.microsoft.com. Defects reported by customers via the connect site carry extra weight when the development team is prioritizing future work, including service pack work.

Be sure to mention the version of management studio you are working with and that the dialog is not defaulting to the engine default.

Thanks,
Steve

Index creation in SQL Server 2005 Management Studio - Page locks disabled by default

When I create an index on a table using SQL Server Management Studio, the index has page locks disabled by default. I'd prefer to have page locks enabled by default so neither I nor any other developer has to remember to manually modify. How can I accomplish this?

Thanks,

Pat Brickson

exec sp_indexoption '<TableName>', 'DisAllowPageLocks','FALSE'|||

Thank you, that solves my problem for existing tables. How can I ensure that this is the default for all new tables as well?

The CREATE INDEX statement in T-SQL enables page level locks by default but if I create via Management Studio, they're disabled by default. Why is there a discrepency?

Thanks, Pat

|||

After testing, I find that this only affects indexes that already exist on the table. I'd like to ensure that any new indexes on this table (or any existing or new table for that matter) have page locks enabled. Unfortunately this stored procedure doesn't really help me any more than just manually altering the index's options via the index properties dialog in Management Studio.

Again, I appreciate any assistance. Any other ideas?

Thanks, Pat

|||

The default settings for new indexes are hard coded in the dialog to match the defaults in SQL Server if you don't specify any options. There is no way for end users to change these defaults in the dialog.

Thanks,
Steve

|||

Maybe I misundertand you, but isn't the default to allow page locks when accessing the database? That's the default when issuing a T-SQL CREATE INDEX statement when no options are specified.

I'm really not concerned with end users changing the default. I'm more interested in ensuring that an any index created via Management Studio by a developer or DBA will have page locks enabled. Since that doesn't seem to be the default when creating in management studio, is there a way for me to specify which defaults the indexes should take?

Thanks, Pat

|||

If the dialog defaults aren't matching the default behavior in T-SQL, that's not intentional. Please file a defect report for this on http://connect.microsoft.com. Defects reported by customers via the connect site carry extra weight when the development team is prioritizing future work, including service pack work.

Be sure to mention the version of management studio you are working with and that the dialog is not defaulting to the engine default.

Thanks,
Steve