Primary Key difference when comparing Full Text Indexes

samjudson

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:

CREATE TABLE Table1
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	TextField NVARCHAR(MAX)
)

SQL to create the full text index:

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

Brian Donahue

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?

samjudson

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.

Brian Donahue

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.

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.

samjudson

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.

iudux

I had the same problem.

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

Thanks

samjudson

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.