USE AdventureWorks
GO
CREATE FULLTEXT INDEX ON Production.ProductDescription(Description)
    KEY INDEX PK_ProductDescription_ProductDescriptionID
    WITH CHANGE_TRACKING = AUTO
GO

--Freetext
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE FREETEXT(Description,N'safety component')
GO
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE FREETEXT(Description,N'bike')
GO

SELECT a.ProductDescriptionID, a.Description, b.*
FROM Production.ProductDescription a
    INNER JOIN FREETEXTTABLE(Production.ProductDescription, Description,
        N'bike') b ON a.ProductDescriptionID = b.[Key]
ORDER BY b.[Rank]
GO

--Contains
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N'bike')
GO

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N'"bike*"')
GO

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N' FORMSOF (INFLECTIONAL,ride) ')
GO

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N' FORMSOF (THESAURUS,metal) ')
GO

SELECT a.ProductDescriptionID, a.Description, b.*
FROM Production.ProductDescription a INNER JOIN
    CONTAINSTABLE(Production.ProductDescription, Description,
        N'bike NEAR performance') b ON a.ProductDescriptionID = b.[Key]
ORDER BY b.[Rank]        
GO

SELECT a.ProductDescriptionID, a.Description, b.*
FROM Production.ProductDescription a INNER JOIN
    CONTAINSTABLE(Production.ProductDescription, Description,
        N'ISABOUT (performance WEIGHT (.8), comfortable WEIGHT (.6), 
        smooth WEIGHT (.2) , safe WEIGHT (.5), competition WEIGHT (.5))', 10) 
        b ON a.ProductDescriptionID = b.[Key]
ORDER BY b.[Rank] DESC        
GO
