SQL Compare does not detect disabled indexes

shawnC

Hi


tl;dr: SQL Compare does not detect disabled indexes

More...

I was playing with a bulk load script for test data.
In this, I disable constraints (CHECK and FK) and triggers.

However I wanted to disable some indexes temporarily. It failed and I needed to reset the database schema.

I thought I re-enabled all indexes (and constraints and triggers) and this appeared to be all OK when I used SQL Compare.
However, my overnight maintenance jobs failed because an index on an indexed view was still disabled. I had used sp_MSForEachTable which skipped the view

Repro script:

/*
Run on 2 databasee
*/
DROP VIEW dbo.FooView; DROP TABLE dbo.Foo; 
GO
CREATE TABLE dbo.Foo (FooID int NOT NULL PRIMARY KEY, Other char(20) NOT NULL CHECK (Other <> 'Seven'));
GO
INSERT dbo.Foo VALUES (1, 'one'),(2, 'two'),(3, 'three'),(4, 'four'),(5, 'five'),(6, 'six');
GO
CREATE NONCLUSTERED INDEX IX_Other ON dbo.Foo (Other);
GO
CREATE TRIGGER TRG_Foo ON dbo.Foo FOR DELETE AS SET NOCOUNT ON;
GO
CREATE VIEW dbo.FooView
WITH SCHEMABINDING
AS
SELECT F.FooID, F.Other FROM dbo.Foo F WHERE FooID >= 4;
GO
CREATE UNIQUE CLUSTERED INDEX IXCU_PK ON dbo.FooView (FooID);
GO
SELECT * FROM dbo.Foo;SELECT * FROM dbo.FooView;
GO


/*
A SQL Compare now shows PK and UQ have different system generated name

Now disable stuff in one database only
*/


GO
SELECT * FROM sys.indexes WHERE sys.indexes.is_disabled = 1;SELECT * FROM sys.check_constraints CC WHERE CC.is_not_trusted = 1;SELECT * FROM sys.triggers T WHERE T.is_disabled = 1;
GO
ALTER INDEX ALL ON dbo.Foo DISABLE;
ALTER TABLE dbo.Foo NOCHECK CONSTRAINT ALL; 
ALTER TABLE dbo.Foo DISABLE TRIGGER ALL;
GO
ALTER INDEX IXCU_PK ON dbo.FooView DISABLE;
GO
SELECT * FROM sys.indexes WHERE sys.indexes.is_disabled = 1;SELECT * FROM sys.check_constraints CC WHERE CC.is_not_trusted = 1;SELECT * FROM sys.triggers T WHERE T.is_disabled = 1;
GO

/*
A SQL Compare now does not show differences in the indexes (expected: 2 on dbo.Foo, 1 on dbo.FooView).. but it does show the CHECK constraint differences
*/

DROP VIEW dbo.FooView; DROP TABLE dbo.Foo; 
GO

Brian Donahue

I could not reproduce your issue because I ran across another bug. But SQL Compare does recognize that the constraint needs to be enabled/disabled - it's just that the command fails because SQL Compare is not respecting the "ignore system-generated names" option when enabling/disabling constraints.

However, this would explain the problem if you did not see the error message when the sync script ran and just went by the end-result, which was that the constraint was not disabled or re-enabled (depending on the direction you ran the sync).

I filed a bug SC-6104 about this, but not with your problem description (change not detected) but rather that the system-generated name is not ignored; the difference is you can visually confirm SQL Compare finds the difference, it is just not synchronized correctly.

shawnC

Thanks Brian

My reported bug is about disabled indexes (sys.indexes.is_disabled), not constraints as such

Obviously a unique constraint and a primary key are indexes, but explicit indexes (CREATE INDEX) that are disabled are not detected: neither are indexes (from constraints) from CREATE TABLE

So, neither the PK nor IX_Other or IXCU_PK are detected. Only the table PK has a system generated name, the others are explicitly named

shawnC

any update please?

shawnC

One year later... any update please?

mjswart

I can reproduce this too.
http://www.red-gate.com/MessageBoard/vi ... hp?t=20479

[edit] Actually, my reproduction was about disabled constraints, not disabled indexes. But my symptoms seem similar to these symptoms.

shawnC

They don't bother fixing much these days.

I'm going right off SQL Compare because of continual low level unfixed bugs.