Red Gate forums :: View topic - SQL Compare does not detect disabled indexes
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

SQL Compare does not detect disabled indexes

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
shawnC



Joined: 22 Nov 2011
Posts: 13

PostPosted: Thu Nov 08, 2012 10:18 am    Post subject: SQL Compare does not detect disabled indexes Reply with quote

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:

Code:
/*
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

_________________
http://stackexchange.com/users/13638/gbn
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6670

PostPosted: Mon Nov 12, 2012 12:28 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
shawnC



Joined: 22 Nov 2011
Posts: 13

PostPosted: Wed Nov 14, 2012 8:53 am    Post subject: Reply with quote

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
_________________
http://stackexchange.com/users/13638/gbn
Back to top
View user's profile Send private message
shawnC



Joined: 22 Nov 2011
Posts: 13

PostPosted: Mon Nov 19, 2012 11:46 am    Post subject: Reply with quote

any update please?
_________________
http://stackexchange.com/users/13638/gbn
Back to top
View user's profile Send private message
shawnC



Joined: 22 Nov 2011
Posts: 13

PostPosted: Thu Nov 14, 2013 12:29 pm    Post subject: Reply with quote

One year later... any update please?
_________________
http://stackexchange.com/users/13638/gbn
Back to top
View user's profile Send private message
mjswart



Joined: 30 Apr 2014
Posts: 3

PostPosted: Thu May 01, 2014 6:56 pm    Post subject: Reply with quote

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

[edit] Actually, my reproduction was about disabled constraints, not disabled indexes. But my symptoms seem similar to these symptoms.
Back to top
View user's profile Send private message
shawnC



Joined: 22 Nov 2011
Posts: 13

PostPosted: Tue May 06, 2014 7:54 am    Post subject: Reply with quote

They don't bother fixing much these days.

I'm going right off SQL Compare because of continual low level unfixed bugs.
_________________
http://stackexchange.com/users/13638/gbn
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group