locked
SQL Server Full Text Index with Joins RRS feed

  • Question

  • Hello All,

    My Greetings for the day!!!

    I am trying to implement Full Text Index for the 1st time and facing a issue.

    Following is the scenario: 

    1 - Table 1 (Keys) :  It has 2 columns t2 (PK), key_word (Varchar)

    2 - Table 2 (summary) :  It has 2 columns t1 (PK), summary (Varchar)

    3 - Create - Full Text Catalog

    I am trying to match the data from table 1 and table 2.

    Can some one please propose the structure of the query using Full text index.

    The expected result is to join tables and pass column from Table 1 (t2) in Table 2 (summary) and match the records.

    I have achieved the same using PATINDEX, however it take a lot of time as my records are in millions.

    Below is the sample code for quick understanding:

    GO
    /****** Object:  FullTextCatalog [Summary]    Script Date: 13-Mar-2018 7:16:50 PM ******/
    CREATE FULLTEXT CATALOG [Summary]WITH ACCENT_SENSITIVITY = OFF
    
    GO
    /****** Object:  Table [dbo].[keys]    Script Date: 13-Mar-2018 7:16:50 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[keys](
    	[t2] [int] NOT NULL,
    	[key_word] [varchar](50) NULL,
     CONSTRAINT [PK_key_word] PRIMARY KEY CLUSTERED 
    (
    	[t2] 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
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[summary]    Script Date: 13-Mar-2018 7:16:50 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[summary](
    	[t1] [int] NOT NULL,
    	[summary] [nvarchar](max) NULL,
     CONSTRAINT [PK_summary] PRIMARY KEY CLUSTERED 
    (
    	[t1] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    INSERT [dbo].[keys] ([t2], [key_word]) VALUES (1, N'abc')
    INSERT [dbo].[keys] ([t2], [key_word]) VALUES (2, N'bcd')
    INSERT [dbo].[keys] ([t2], [key_word]) VALUES (3, N'cde')
    INSERT [dbo].[keys] ([t2], [key_word]) VALUES (4, N'def')
    INSERT [dbo].[keys] ([t2], [key_word]) VALUES (5, N'efg')
    INSERT [dbo].[keys] ([t2], [key_word]) VALUES (6, N'fgh')
    INSERT [dbo].[keys] ([t2], [key_word]) VALUES (7, N'ghi')
    INSERT [dbo].[summary] ([t1], [summary]) VALUES (1, N'bcd testing efg the data for the xml abc')
    
    -- Expected Results through PATINDEX
    Select * from 
    [summary] s
    inner join keys k on 
    PATINDEX ( '%'+k.key_word +'%', (s.summary )) >0

    In the above example: Table 1 & Table 2 are not in relations.

    Please revert with your valued suggestions.

    Regards,


    Hiren Parikh


    • Edited by contacthirenparikh Tuesday, March 13, 2018 2:22 PM In the above example: Table 1 & Table 2 are not in relations.
    Tuesday, March 13, 2018 2:10 PM

Answers

  • I am sure Full Text Index shall be faster as compared to above operators. Please provide syntax with implementing  Full text Index i.e. CONTAINS, FREETEXTTABLE Etc.

    Regards,

     


    Hiren Parikh

    Hi Hiren Parikh,

    Thanks for your reply.

    Per your reply, you may follow the examples of these links for better understanding.

    CONTAINSTABLE (Transact-SQL)

    FREETEXTTABLE (Transact-SQL)

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 14, 2018 6:13 AM

All replies

  • Hi,

    You can use just the LIKE :

    Select * from 
    [summary] s
    inner join keys k on 
    s.summary LIKE '%'+k.key_word +'%' 


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Tuesday, March 13, 2018 2:44 PM
  • Hello Ousama,

    Thanks for your valued suggestion.

    As mentioned above, I have millions of records in the table and hence, I am avoiding Like & PATINDEX.

    I am sure Full Text Index shall be faster as compared to above operators. Please provide syntax with implementing  Full text Index i.e. CONTAINS, FREETEXTTABLE Etc.

    Regards,

     


    Hiren Parikh

    Tuesday, March 13, 2018 4:22 PM
  • I am sure Full Text Index shall be faster as compared to above operators. Please provide syntax with implementing  Full text Index i.e. CONTAINS, FREETEXTTABLE Etc.

    Regards,

     


    Hiren Parikh

    Hi Hiren Parikh,

    Thanks for your reply.

    Per your reply, you may follow the examples of these links for better understanding.

    CONTAINSTABLE (Transact-SQL)

    FREETEXTTABLE (Transact-SQL)

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 14, 2018 6:13 AM
  • Thanks Will,

    I was able to understand CONTAINSTABLE more in detail and achieved the solution with the link shared by you.

    Regards,


    Hiren Parikh

    Wednesday, March 21, 2018 7:40 AM