Red Gate forums :: View topic - Primary Key difference when comparing Full Text 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

Primary Key difference when comparing Full Text Indexes

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



Joined: 14 Jan 2014
Posts: 5

PostPosted: Tue Jun 03, 2014 3:36 pm    Post subject: Primary Key difference when comparing Full Text Indexes Reply with quote

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:
CREATE TABLE Table1
(
   ID INT IDENTITY(1,1) PRIMARY KEY,
   TextField NVARCHAR(MAX)
)


SQL to create the full text index:

Code:

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



Joined: 23 Aug 2004
Posts: 6645

PostPosted: Fri Jun 06, 2014 9:23 am    Post subject: Reply with quote

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



Joined: 14 Jan 2014
Posts: 5

PostPosted: Fri Jun 06, 2014 11:26 am    Post subject: Reply with quote

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



Joined: 23 Aug 2004
Posts: 6645

PostPosted: Fri Jun 06, 2014 2:46 pm    Post subject: Reply with quote

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



Joined: 14 Jan 2014
Posts: 5

PostPosted: Fri Jun 06, 2014 4:31 pm    Post subject: Reply with quote

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



Joined: 06 Jul 2014
Posts: 1

PostPosted: Sun Jul 06, 2014 12:16 pm    Post subject: Reply with quote

I had the same problem.

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

Thanks
Back to top
View user's profile Send private message
samjudson



Joined: 14 Jan 2014
Posts: 5

PostPosted: Mon Jul 07, 2014 8:42 am    Post subject: Reply with quote

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.
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