Full-Text Search in Different Languages on SQL Server
This SQL Server tutorial shows how to query a text table using full-text search where texts in different languages are stored. Actually using different languages for a text field on a database table requires different word breaker set specific to each language on a SQL Server full-text catalog. SQL Server enables SQL developers and database administrators (DBAs) to define different language for word breaker for full-text search queries in SQL Server.
Assume that as an administrator you have created a single text table in your SQL Server database model. All text for your web site for different languages will be stored in below database table.
CREATE TABLE [dbo].[articles] (
[Id] [int] IDENTITY(1,1) NOT NULL,
[Language] char(2) NOT NULL,
[Text] [nvarchar](max) NULL,
CONSTRAINT [PK_articles] PRIMARY KEY CLUSTERED ( [Id] )
)
SQL developers have to provide the language for the text in the "language" field of the database table "articles"
After text table is created and populated with sample data for different languages, we are ready to create a new full-text catalog which will create a base for full-text search on the database table.
A new full-text catalog can be created on SQL Server 2014 using SSMS by choosing the New Full-Text Catalog... menu option on the context menu displayed by a right click on the Full Text Catalog under Storage node.
Create a new Full-Text Catalog in SQL Server 2014 using SQL Server Management Studio (SSMS)
A fulltext catalog can also be created by running the below T-SQL statement:
CREATE FULLTEXT CATALOG [articles] WITH ACCENT_SENSITIVITY = ON
After full-text catalog is created, we can continue with SQL VIEW creation for different languages. Let's start with creating a SQL view for English.
CREATE VIEW [dbo].[articles_en]
WITH SCHEMABINDING
AS
SELECT [Id], [Language], [Text] FROM dbo.articles WHERE [Language] = 'EN'
GO
CREATE UNIQUE CLUSTERED INDEX PK_Articles_EN ON [dbo].[articles_en](Id)
GO
Then we can continue with other languages like Turkish, German, French, Italian, Russian, or Chinese, etc.
It is important that a unique key should be created associated with the new SQL view for target language. Otherwise, the SQL view created for full-text search on a targeted language will not be listed in the eligible table or view objects in that database.
After you create SQL View objects for all targeted languages on your database, now open SQL Server Management Studio and drill-down database > Storage > Full Text Catalogs named nodes. Double click on the fulltext catalog created for fulltext search on articles database table or right-click on the fulltext catalog node and choose Properties menu option.
Navigate to Tables/Views page or tab on the Full-Text Catalog properties dialog screen.
SQL Server Full-Text Catalog eligible Table and Views
Highlight the SQL view you have created for Turkish for example. Move it to the right screen from eligible table and view list to assign it to the fulltext catalog. You must see the unique index in the selected object properties section
Word breaker language for Full-Text Search on SQL Server
In eligible columns all database table columns that full-text search can be defined is listed. Mark the column for fulltext search and select the language for word breaker sets.
This database model where all text in different languages are stored in a single database table, but different SQL views are created for each language enables full-text search on these SQL views.