Primary Key difference when comparing Full Text Indexes

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Primary Key difference when comparing Full Text Indexes

Postby samjudson » Tue Jun 03, 2014 3:36 pm

Hi

I have two databases, both of which have the same table, with the same primary key on them (an ID identity column, nothing unusual).

They both have a full text index on them, which includes a text field (for example).

SQL to create the table:

Code: Select all
CREATE TABLE Table1
(
   ID INT IDENTITY(1,1) PRIMARY KEY,
   TextField NVARCHAR(MAX)
)


SQL to create the full text index:

Code: Select all
CREATE FULLTEXT CATALOG FCat1 AS DEFAULT
GO

CREATE    FULLTEXT INDEX ON dbo.Table1 (TextField) KEY INDEX PK__Table1__3214EC27F0EE08A2
GO


Note: The name of the primary key index will be DIFFERENT in each database.

Now, SQL Compare will quite happily not complain about the difference in the names of the indexes, especially if you tick the "Ignore System named constraints and index names" checkbox.

However SQL Compare considers the two full text indexes to be different, as they are based on a different 'index' even though both indexes are identical.

The sync script then does the following things:

1 - drop full text index (outside of transaction).
2 - any other synchronisations (inside transaction)
2 - create new full text index based on the name of the index in DB 1, which falls over as the index has a different name in DB 2.

In this scenarios SQL Compare should try to understand that the two indexes are both the same (much like it does when actually comparing the table indexes), and not use that as an indicator that the full text indexes are different.

Obviously if they are based on different indexes then it should highlight this fact - but not by deleting the full text index and then falling over later on.

Sam
samjudson
 
Posts: 8
Joined: Tue Jan 14, 2014 10:30 am

Postby Brian Donahue » Fri Jun 06, 2014 9:23 am

Hi Sam,

I am trying to understand your issue. Is it that you want SQL Compare to ignore differences in full-text indexes when they are linked to different full-text filegroups or key indexes?
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby samjudson » Fri Jun 06, 2014 11:26 am

The primary key indexes are identical, apart from the system generated names, but SQL Compare is thinking the full text indexes are different because they are based on key indexes with different names.

It then drops the full text index and tries to recreate it based on the name of the primary key index in DB1, even though there is no index in DB2 with that name, so it falls over.
samjudson
 
Posts: 8
Joined: Tue Jan 14, 2014 10:30 am

Postby Brian Donahue » Fri Jun 06, 2014 2:46 pm

Thanks.

I can reproduce the error message from SQL by migrating a table with a full-text index on one side and none on the other.

Code: Select all
CREATE TABLE [dbo].[Table1](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [TextField] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED
(
   [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE FULLTEXT CATALOG FCat1 AS DEFAULT
 GO
/* get the key name from SSMS and then run the next line */
 CREATE FULLTEXT INDEX ON dbo.Table1 (TextField) KEY INDEX PK__Table1__3214EC27164452B1
 GO

Provided the system-named primary key is not the same name, you will have this problem.
I have logged a bug number SC-7238 so someone will be looking into this problem.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby samjudson » Fri Jun 06, 2014 4:31 pm

Yes, that is the same kind of issue.

The only difference when you do have a full text index on both sides is that it drops the index, and the tries to create it as you have illustrated.

My original point however is that it doesn't need to drop and re-create the full text index, as the key index they are both based on are the same core index (i.e. same columns etc) but simply has a different name.

As a work around to this I've taken to renaming all my primary key indexes on all databases I am trying to sync so they are the same, which solves the main problem for me.
samjudson
 
Posts: 8
Joined: Tue Jan 14, 2014 10:30 am

Postby iudux » Sun Jul 06, 2014 12:16 pm

I had the same problem.

Finally I renamed all the primary key indexes and it worked properly.

Thanks
iudux
 
Posts: 1
Joined: Sun Jul 06, 2014 12:12 pm

Postby samjudson » Mon Jul 07, 2014 8:42 am

Yes, in the end I did the same, although this was a pain as I had about 30 databases to keep in sync and the indexes where all different in each on.
samjudson
 
Posts: 8
Joined: Tue Jan 14, 2014 10:30 am


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests