I posted this message some time ago on
microsoft.public.sqlserver.programming but got no replies.  Today I
found this group so maybe I'll have better luck :)

I have a table with a full-text index on a given column.  If I run the
following:

declare @s varchar(100)
select @s = 'fast'
select * from tblHannahRES where contains(RES_SER_TI, @s)
select * from tblHannahRES where contains(RES_SER_TI, 'fast')

on my SQL2000/SP4 box I get substantially different access times, which

is unlike what I would get with a normal index i.e. performing the SQL
below (where the index is on ResID) yields identical plans and access
times:

declare @i int
select @i = 1463440
select * from tblHannahRES where ResID = @i
select * from tblHannahRES where ResID = 1463440

With the full-text indices I get the plans shown below and the second
query is consistenly 6X faster than the first.

One thing I notice is that the remote scan itself costs 0.36 vs 0.06
though both generate the same number of rows.  the second thing to
notice is that the first case incurs an extra sorting step (which costs

about 0.02 points).  I'm not sure why this should be the case.  Can
anyone elucidate on the issue here?

TIA - e

select * from tblHannahRES where contains(RES_SER_TI, @s)
 |--Nested Loops(Inner Join, OUTER
REFERENCES:([FULLTEXT:tblHannahRES].[KEY]) WITH PREFETCH)
      |--Sort(ORDER BY:([FULLTEXT:tblHannahRES].[KEY] ASC))
      |    |--Remote Scan(OBJECT:(CONTAINS))
      |--Clustered Index
Seek(OBJECT:([Merlin].[dbo].[tblHannahRES].[PK_tblHannahRES]),
SEEK:([tblHannahRES].[ResID]=[FULLTEXT:tblHannahRES].[KEY]) ORDERED
FORWARD)

select * from tblHannahRES where contains(RES_SER_TI, 'fast')
 |--Nested Loops(Inner Join, OUTER
REFERENCES:([FULLTEXT:tblHannahRES].[KEY]) WITH PREFETCH)
      |--Remote Scan(OBJECT:(CONTAINS))
      |--Clustered Index
Seek(OBJECT:([Merlin].[dbo].[tblHannahRES].[PK_tblHannahRES]),
SEEK:([tblHannahRES].[ResID]=[FULLTEXT:tblHannahRES].[KEY]) ORDERED
FORWARD)