SQL Server CONTAINS Predicate - using a column value as the <contains_condition>

Question

I'm having a problem rewriting an existing process that uses CONTAINS predicate. The existing process is working using a CURSOR, but it's really slow, and getting slower as more data is inserted.

I have made up a simple example (code below) containing two tables - one is Full Text Indexed, and the other has a column which identifies a CONTAINS condition to choose rows from the first.

The current stored procedure uses a CURSOR to loop through the second table, setting a @filter variable, then uses CONTAINS to locate the rows from the first table that match. The problem is that it runs for hours, and it's getting worse.

To speed the process up, I tried to use the CONTAINS predicate directly on the column value rather than going through the pain of a cursor... but I'm getting a syntax error. My example is below.

I've tried to implement a CROSS APPLY and I've also tried to write a User-Defined Function (fnCONTAINS) with no luck.

IF ( object_id('Players') IS NOT NULL )
  DROP TABLE Players

go

IF ( object_id('TeamNeeds') IS NOT NULL )
  DROP TABLE TeamNeeds

go

-- create fulltext catalog ft as default
go

CREATE TABLE Players
  (
     PlayerID        INT IDENTITY(1, 1),
     PlayerName      VARCHAR(20),
     PlayerPositions VARCHAR(60)
  )

go

CREATE UNIQUE INDEX IXPlayerID
  ON Players( PlayerID )

go

CREATE fulltext INDEX ON Players(PlayerPositions) KEY INDEX IXPlayerID

go

INSERT Players
       (PlayerName,
        PlayerPositions)
VALUES( 'Patrick Travers',
        'Pitcher,Left Field,Center Field,Right Field,Shortstop' )

go

CREATE TABLE TeamNeeds
  (
     TeamID   INT,
     Keywords VARCHAR(50)
  )

go

INSERT TeamNeeds
       (TeamID,
        Keywords)
VALUES( 1,
        '"Center Field" and "Shortstop" and "Pitcher"' )

go

WAITFOR delay '00:00:05'

go -- Give the Full Text Index process time to populate the catalog
SELECT PlayerID,
       PlayerName,
       PlayerPositions
FROM   Player,
       TeamNeeds
WHERE  CONTAINS(PlayerPositions, Keywords)

go -- Syntax error on Keywords...
SELECT PlayerID,
       PlayerName,
       PlayerPositions
FROM   Players,
       TeamNeeds
WHERE  CONTAINS(PlayerPositions, '"Center Field" and "Shortstop" and "Pitcher"')

go -- Works just fine, but requires setting an explicit search expression for every search, which is terribly slow

Answer 1

This is only possible with a multi statement TVF as far as I can see.

Creating the following function

CREATE FUNCTION [dbo].[ft_test] (@Keywords VARCHAR(50))
RETURNS @ReturnTable TABLE (
  PlayerID        INT,
  PlayerName      VARCHAR(20),
  PlayerPositions VARCHAR(60))
AS
  BEGIN
      INSERT INTO @ReturnTable
      SELECT PlayerID,
             PlayerName,
             PlayerPositions
      FROM   Players
      WHERE  CONTAINS(PlayerPositions, @Keywords)

      RETURN
  END

Then the following works fine

SELECT *
FROM   TeamNeeds
       CROSS APPLY [dbo].[ft_test] (Keywords) CA 

Though the inline version fails with "The inline function "x" cannot take correlated parameters or subqueries because it uses a full-text operator."