Disabled index issue

jmeyer

It looks like the disabled flag inon indexes is not taken into account when comarign tables in v12.0.39.3632.

CREATE TABLE dbo.RGTest (ID INT IDENTITY(1, 1)
                                        NOT NULL
                           , IndexedFiled CHAR(5) NOT NULL
                           , CONSTRAINT PK_RGTest PRIMARY KEY CLUSTERED (ID ASC) ON [PRIMARY])
ON
  [PRIMARY];

GO

CREATE NONCLUSTERED INDEX IX_RGTest_IndexedField ON dbo.RGTest 
(
IndexedFiled ASC
) ON [PRIMARY];
GO

--ALTER INDEX IX_RGTest_IndexedField ON dbo.RGTest DISABLE;

If I create the same table on both the source and target database and then run the commented out "disable" line of code on either the source or the target then SQL compare will not flag the index as different.

Rob C

Hi,

Sorry that you're experiencing this problem. This is a known issue, with reference SC-4600. As it's a non-critical issue, it will most likely have a fairly low priority so I can't give you a timescale as to when it might be fixed. I'll try and update you as soon as I hear anything about a possible bugfix.

Apologies.

jmeyer

I would respectfully disagree that a disabled index is a non-critical issue when comparing two databases. If I am replicating a database to another system, e.g. for performance tuning, and Red-Gate tells me the databases are identical then I would not assume I have to check whether a specific index may be disabled on one system or the other. I now have to question what else has been deemed non-critical and thus hides database structure differences from me.

Rob C

Apologies, I think "non-critical" was the wrong phrase for me to use as it's clearly not a trivial problem. Having said that, it's going to be fairly far down the list of priorities for the development team at the moment I'm afraid.

As I said, I'll try and keep you updated whenever I hear any new information.

Rob C

Hi,

We've just released a version of SQL Compare to the frequent updates channel that fixes this problem. You can get this version from the Help > Check for updates menu or from this link - SQL Compare 12.1.4.3873.

kevriley

I've noticed that SQL Compare does now detect disabled indexes, however nothing highlighted is shown in the comparison window - the disabled index is still scripted as 'create index'. Only when you go through to deploy, and you see the deployment script that it shows a disabled index.

I'm running SQL Compare 12.3.3.4490