Change Full-Text Index Language

This archived community thread from the APG vNext support forum discusses: Change Full-Text Index Language.

About This Topic

By default, the software sets up Full Text Indexes using the Neutral Language (LCID = 0). This helps the server recognize all languages in the index. If...

Getting Help with APG vNext

APG vNext is a powerful ASP.NET forum and community platform. For questions related to this topic:

Browse the Knowledge Base for documented solutions
Search the community forum for similar threads
Check the FAQ for common questions and answers
Review the Installation and Upgrade guides

If you need direct assistance, the support team is active in the community forum.

Changing the Full-Text Index Language in APG vNext

APG vNext's search relies on SQL Server Full-Text Search. The index language determines which word-breaking algorithm and stopword list are used. For non-English forums, changing the language dramatically improves search relevance.

Check Current Language

SELECT c.name, l.name AS FTSLanguage, l.lcid
FROM   sys.fulltext_index_columns fc
JOIN   sys.columns c ON c.object_id = fc.object_id AND c.column_id = fc.column_id
JOIN   sys.fulltext_languages l ON l.lcid = fc.language_id
WHERE  OBJECT_NAME(fc.object_id) = 'apg_Posts';

Rebuild with New Language (e.g. French = 1036)

DROP FULLTEXT INDEX ON apg_Posts;

CREATE FULLTEXT INDEX ON apg_Posts
    (PostContent LANGUAGE 1036, PostTitle LANGUAGE 1036)
KEY INDEX PK_apg_Posts ON apg_FTSCatalog
WITH CHANGE_TRACKING AUTO;

-- Check progress (0 = done):
SELECT FULLTEXTCATALOGPROPERTY('apg_FTSCatalog','PopulateStatus');

Custom Stopword List

CREATE FULLTEXT STOPLIST apg_Stoplist FROM SYSTEM STOPLIST;
ALTER  FULLTEXT STOPLIST apg_Stoplist ADD 'apg'   LANGUAGE 'English';
ALTER  FULLTEXT STOPLIST apg_Stoplist ADD 'vnext' LANGUAGE 'English';

Related Resources

Changing the Full-Text Index Language in APG vNext

SQL Server Full-Text Search uses a language setting to determine how text is tokenised (broken into searchable words) and which stopwords are excluded. By default, APG vNext configures Full-Text indexing in English (LCID 1033). If your forum is primarily in a different language, changing the Full-Text index language significantly improves search relevance for your members.

Checking Current Full-Text Index Language

-- Find the current language for the apg_Posts full-text index:
SELECT c.name AS ColumnName,
       l.name AS Language,
       l.lcid AS LCID
FROM   sys.fulltext_index_columns c
JOIN   sys.fulltext_languages l ON l.lcid = c.language_id
WHERE  c.object_id = OBJECT_ID('apg_Posts');

Changing to a Different Language

-- Drop and recreate the full-text index with the new language:
-- Step 1: Drop existing index
DROP FULLTEXT INDEX ON apg_Posts;

-- Step 2: Recreate with desired language (e.g., French = 1036, Arabic = 1025):
CREATE FULLTEXT INDEX ON apg_Posts
  (Content LANGUAGE 1036,      -- French
   Subject  LANGUAGE 1036)
KEY INDEX PK_apg_Posts
ON apg_catalog
WITH STOPLIST = OFF;           -- Use no stopword list for multilingual content

Supported Languages and LCIDs

-- List all installed Full-Text languages on your SQL Server:
SELECT name, lcid, alias FROM sys.fulltext_languages ORDER BY name;

-- Common LCIDs:
-- English:   1033
-- French:    1036
-- German:    1031
-- Spanish:   3082
-- Arabic:    1025
-- Chinese (Simplified): 2052
-- Japanese:  1041

Multilingual Forum Configuration

For forums that serve members in multiple languages, APG vNext supports per-forum language configuration. Each forum section can have its own Full-Text index language, allowing different language communities to coexist in a single APG vNext installation with correct search behaviour for each language group.

Rebuilding the Full-Text Index After Language Change

-- Trigger a full population to rebuild the index with the new language:
ALTER FULLTEXT INDEX ON apg_Posts START FULL POPULATION;

-- Monitor population progress:
SELECT FULLTEXTCATALOGPROPERTY('apg_catalog', 'PopulateStatus') AS Status;
-- 0 = Idle (complete), 1 = Full population in progress

Impact of Language Setting on Search Quality

The Full-Text index language setting directly affects search quality for your members. With the wrong language setting, common words that should be excluded as stopwords remain in the index (increasing noise in search results), and words are not stemmed correctly (so searching for "running" won't match posts containing "ran" or "runs"). For English-language forums, the default LCID 1033 setting is correct. For multilingual forums, consider configuring separate forum sections with different language settings, or use the neutral language (LCID 0) which disables language-specific processing entirely — a good fallback for technical content where exact keyword matching is more important than linguistic stemming.

Testing Search Quality After Language Change

-- Test that Full-Text search returns relevant results:
SELECT TOP 10 PostID, ThreadID,
       CONTAINS(Content, 'your test query') AS HasMatch
FROM   apg_Posts
WHERE  CONTAINS(Content, 'your test query')
ORDER  BY PostID DESC;

-- If CONTAINS returns no results after index rebuild,
-- check that the index population completed:
SELECT FULLTEXTCATALOGPROPERTY('apg_catalog', 'ItemCount') AS IndexedItems;

Custom Stopword Lists for Specialised Forums

Technical forums often have domain-specific common words that should be treated as stopwords (excluded from search indexes) because they appear in almost every post and add no search value. For example, a forum about ASP.NET would have "aspnet", "asp", and "iis" as near-universal terms. Create a custom stopword list to exclude these terms from Full-Text indexing, improving the precision of search results:

-- Create a custom stopword list:
CREATE FULLTEXT STOPLIST apg_custom_stoplist FROM SYSTEM STOPLIST;
ALTER FULLTEXT STOPLIST apg_custom_stoplist ADD 'aspnet'  LANGUAGE 1033;
ALTER FULLTEXT STOPLIST apg_custom_stoplist ADD 'iis'     LANGUAGE 1033;
ALTER FULLTEXT STOPLIST apg_custom_stoplist ADD 'forum'   LANGUAGE 1033;

-- Apply to Full-Text index:
ALTER FULLTEXT INDEX ON apg_Posts SET STOPLIST = apg_custom_stoplist;
ALTER FULLTEXT INDEX ON apg_Posts START UPDATE POPULATION;

Related Resources

Looking for more help? Browse the support forum or check the Knowledge Base.