How can we help you today? How can we help you today?

SQL Compare does not detect disabled indexes

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
shawnC
0

Comments

6 comments

Sort by
  • 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.
    Brian Donahue
    0
  • 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
    0
  • shawnC
    any update please?
    shawnC
    0
  • shawnC
    One year later... any update please?
    shawnC
    0
  • 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.
    mjswart
    0
  • shawnC
    They don't bother fixing much these days.

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

Add comment

Please sign in to leave a comment.