CODEWITHSUNDEEP
sql-serverazure-sql-database
Peter de Bruijn
Peter de Bruijn

Reputation: 822

Multiple full text indexes on same table

I have a table containing columns in different languages. I want make multiple full-text indexes on this table, one for every language. Is it even possible in MsSQL (Azure)? I have the following code:

IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs  WHERE NAME = 'BlaBlaFullTextCatalog')
    CREATE FULLTEXT CATALOG BlaBlaFullTextCatalog AS DEFAULT;

CREATE FULLTEXT INDEX ON [IDX_c7296e1d-8cea-40f7-9204-8f017b1e14a8_tmp] ([OBJ_FreeText_de-DE] LANGUAGE 1043) KEY INDEX [PK_bbca2bbb-a84e-4bd2-b908-92c5329652a5] ON [BlaBlaFullTextCatalog]
    WITH CHANGE_TRACKING = AUTO;

CREATE FULLTEXT INDEX ON [IDX_c7296e1d-8cea-40f7-9204-8f017b1e14a8_tmp] ([OBJ_FreeText_nl-NL] LANGUAGE 1043) KEY INDEX [PK_bbca2bbb-a84e-4bd2-b908-92c5329652a5] ON [BlaBlaFullTextCatalog]
    WITH CHANGE_TRACKING = AUTO;

ALTER FULLTEXT INDEX ON [IDX_c7296e1d-8cea-40f7-9204-8f017b1e14a8_tmp] ENABLE;

ALTER FULLTEXT INDEX ON [IDX_c7296e1d-8cea-40f7-9204-8f017b1e14a8_tmp] START FULL POPULATION;

But it throws the following error:

A full-text index for table or indexed view 'IDX_c7296e1d-8cea-40f7-9204-8f017b1e14a8_tmp' has already been created.

Upvotes: 7

Views: 2851

Answers (2)

Abhishek
Abhishek

Reputation: 2490

As per MSDN -

As per MSDN - Only one full-text index is allowed per table or indexed view, and each full-text index applies to a single table or indexed view.

So the error that you are getting clearly says that IDX_c7296e1d-8cea-40f7-9204-8f017b1e14a8_tmp object already has one full-text index (which you have created using the second sql statement in your code block ) and another full-text index on the same object can't be created.

Upvotes: 4

Umachandar - Microsoft
Umachandar - Microsoft

Reputation: 484

Index names for a table need to be unique. You are using duplicate names.

Upvotes: 1

Related Questions