locked
SQL Server FullText Search not returning expected rows (complete code to reproduce) RRS feed

  • Question

  • I am practicing using FullText Search on SQL Server 2016. Below is a complete code of the steps to reproduce my problem. I don't understand why none of my queries return any results (if you run these steps, don't forget to drop the FTSEARCH database when you are done).

    Note that I set STOPLIST=OFF on purpose because I want to search numeric values and numbers are included in the system stoplist

    --1. Confirm FullText is installed
    SELECT 
        CASE FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')
            WHEN 1 THEN 'Full-Text installed.' 
            ELSE 'Full-Text is NOT installed.' 
        END;
    
    --2. Create DB for testing
    CREATE DATABASE FTSEARCH
    
    --3. Enable FullText Search
    USE FTSEARCH
    GO
    
    EXEC sp_fulltext_database 'enable'; 
    
    -- Confirm that FullTextSearch is enabled
    SELECT is_fulltext_enabled
    FROM sys.databases
    WHERE database_id = DB_ID()
    
    --4. Create fulltext catalog
    CREATE FULLTEXT CATALOG FullTextCatalog AS DEFAULT;
    
    -- Verify catalog exists 
    SELECT *
    FROM sys.fulltext_catalogs
    
    --5. Create sample table and populate some data
    IF OBJECT_ID('[dbo].[Test_FT_Search]') IS NOT NULL DROP TABLE [dbo].[Test_FT_Search] 
    CREATE TABLE [dbo].[Test_FT_Search]
    (
    [PKID] [int] NOT NULL IDENTITY(1, 1),
    [Account_Number] [nvarchar] (16)  NULL,
    [Code] [nvarchar] (6)
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[Test_FT_Search] ADD CONSTRAINT [PK_PKID] PRIMARY KEY CLUSTERED ([PKID]) ON [PRIMARY]
    GO
    
    INSERT [dbo].[Test_FT_Search]
    (
        [Account_Number],
        [Code]
    )
    VALUES
    ( N'4445002020875',  N'0A6133' ),
    ( N'4445002020883',  N'0B6133' ),
    ( N'4445002020891',  N'0C6134' ),
    ( N'4445002020909',  N'0F6135' ),
    ( N'4445002020917',  N'0F6136' )
    
    --6. Create FullText Index
    --   NOTE: the STOPLIST is OFF on purpose because I want to search on numbers
    CREATE FULLTEXT INDEX ON  [dbo].[Test_FT_Search] (
        [Account_Number],
        [Code]
    ) 
    KEY INDEX  [PK_PKID]
    WITH 
        CHANGE_TRACKING = AUTO, 
        STOPLIST=OFF
    ;
    
    --7. Force FullText index population
    ALTER FULLTEXT INDEX ON [dbo].[Test_FT_Search] START FULL POPULATION
    
    --8. Perform some searches
    
    -- None of these queries return any resuls
    SELECT *
    FROM [dbo].[Test_FT_Search]
    WHERE CONTAINS([Account_Number],'20208') ;
    
    SELECT *
    FROM [dbo].[Test_FT_Search]
    WHERE CONTAINS([Account_Number],'444500') ;
    
    SELECT *
    FROM [dbo].[Test_FT_Search]
    WHERE CONTAINS([Code],'F6') ;
    
    SELECT *
    FROM [dbo].[Test_FT_Search]
    WHERE CONTAINS([Code],'3') ;



    Monday, May 18, 2020 4:15 AM