sql 2008 filtered indexes not detected in sql compare 8

liroger

I'm using the sql compare version 8.2.1.2. I have an index created as follows:

CREATE NONCLUSTERED INDEX [IX_ServiceRequest_WebApp_NoSWF] ON [dbo].[ServiceRequest] ([WebAppName]) INCLUDE ([LastObjReadByAgent], [LastObjReadByEndUser], [ServiceRequestId], [StatusId]) where [WebAppName] <> 'swfweb'

The where clause in this index was not detected by the tool, so it was not included in the sync script.

Has this been reported? Will this be fixed soon?

Thanks,
Roger

Eddie D

Thank you for your post into the forum.

Unfortunately I cannot reproduce the reported problem you are experiencing.

Therefore we have created a support call for you. The call reference is F0033905.

So we can try to reproduce the reported problem, can you please create SQL Compare Snapshot files of each database and send to the resulting .snp files to us via e-mail, support@red-gate.com? Please also include the call reference number in the subject field on the e-mail.

To create the snapshot files ->File Menu ->Create Snapshot.

Many Thanks
Eddie

Jason Hannas

I'm having the same issue as the OP. Steps to reproduce are:

CREATE TABLE Test (
     ColA int NOT NULL,
     ColB int NOT NULL,
     IsObsolete bit NOT NULL
    ) ;
CREATE UNIQUE NONCLUSTERED INDEX ixTest_ColAColB_UN ON Test (ColA, ColB)
	WHERE ColB IS NOT NULL AND IsObsolete = 0 ;
GO

The resulting Compare output shows:
-- Columns

CREATE TABLE [dbo].[Test]
(
[ColA] [int] NOT NULL,
[ColB] [int] NOT NULL,
[IsObsolete] [bit] NOT NULL
)
GO
-- Constraints and Indexes

CREATE UNIQUE NONCLUSTERED INDEX [ixTest_ColAColB_UN] ON [dbo].[Test] ([ColA], [ColB])
GO

This will have a major impact on synchronizing our dev and test environments. Please advise when a fix might be expected. If it helps any, SQL Doc catches the WHERE clause without a problem.

Thanks.

Eddie D

Please accept my apologies Jason for the delay in repying back to you.

I still cannot reproduce this error. Using your example:

CREATE TABLE Test ( 
     ColA int NOT NULL, 
     ColB int NOT NULL, 
     IsObsolete bit NOT NULL 
    ) ; 
CREATE UNIQUE NONCLUSTERED INDEX ixTest_ColAColB_UN ON Test (ColA, ColB) 
   WHERE ColB IS NOT NULL AND IsObsolete = 0 ; 
GO

I created two new test SQL 2008 databases. In database 1, I created the table using your code above. In database 2, I created the same table but did not create the index.

The synchronization process generates the following script:

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating index [ixTest_ColAColB_UN] on [dbo].[Test]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [ixTest_ColAColB_UN] ON [dbo].[Test] ([ColA], [ColB]) WHERE ([ColB] IS NOT NULL AND [IsObsolete]=(0))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

However I did notice something in the comparison results. Did you scroll the the SQL Differences view from left to right. The SQL Differences will display

CREATE UNIQUE NONCLUSTERED INDEX [ixTest_ColAColB_UN] ON [dbo].[Test] ([ColA], [ColB]

If you move the scroll bar at the bottom of the view, from left to write it reveals the remain syntax

N] ON [dbo].[Test] ([ColA], [ColB]) WHERE ([ColB] IS NOT NULL AND [IsObsolete]=(0))

.

Can you please confirm if moving the scroll bar from left to right reveals the missing filtered part of the index?

Many Thanks
Eddie

Jason Hannas

You are correct, the synchronization script does show the full where clause for the index in my example. However, the difference display and the comparison routine is where the problem lies.

First, create the table and filtered index in Database1 and do nothing in Database 2. Run your compare and you'll see the display will be incorrect (and not because of scrolling), however the sync script will be correct.

Next, create only the table in Database2 and run another compare. You'll find the same results--display is incorrect, sync script is correct.

Finally, create the index without the filter in Database2. Run a compare. You'll see that the comparison process fails at this point, as it will show those tables as identical. Therefore, a synchronization script will also be incorrect, as that table will be excluded.

Eddie D

Thank you for your reply.

Unfortunately I am still unable to reproduce the reported fault symptoms.

What version of SQL Compare are you using? If you have not already done so, can you please download the latest cumulative patch and upgrade your current installtion of SQL Compare V8, using this LINK. I am using the latest cumulative patch of SQL Compare.

If you are still experiencing the reported issue after appying the latest patch, can you please send screen shots of what you are experiencing along with the SQL script generated from the synchronization wizard. A support call has been created, please send the screen shots and script to support@red-gate.com and include the following call reference number in the subject field of the e-mail: F0033905.

Many Thanks
Eddie

Jason Hannas

I just upgraded using your link and confirmed that the problem is fixed with this update. Like an idiot, I did that before documenting the issue with screen shots, so if you think they'll be useful, I guess I could try to downgrade and reproduce.

The version I was on that exhibited the problem is: 8.1.0.360. Checking for updates doesn't show any available, but I do see the OP was on 8.2 already.

Let me know--if you'd like the screen shots, I'll downgrade and put it together for you. If not, just know that that version has a problem and the latest does not.

Thanks,
Jason

Eddie D

Thank you for your reply and excellent news that the cumulative patch has resolved your problem.

The cumlative patch for both SQL Compare has not yet been added to the Check For Updates mechanism. However this is something the development team are looking into, but I do not have a confirmed date for when this will happen.

Please do not downgrade just to take the screen shots previously requested. As the cumulative patch resolves this particular issue, we will request any future customers to upgrade.

Thank you for bringing this problem to our attention and for providing us with example code to reproduce the problem.

Many Thanks
Eddie