We have a Full Text Index on 4 columns and it doesn’t return the results in
the order we think it should in 2000 or 2005. In 2005 I created a persisted
calculated column of these 4 columns and created the Full Text Index on it
and I returned the results in the order I expected them. In my mind both of
these methods should return the results in the same order. If not then I
don’t see how anyone could use Full Text Indexes on multiple columns.

I'm I doing something wrong or is this just the way it is?

I am ordering by Rank. I was trying to ask the question in a generic way but
maybe it would be better to see the code.

I’ve done some testing with FT Search and I’m not sure if it works like I
think it should. In my mind these two Indexes should return the same results
for the table below:

CREATE FULLTEXT INDEX ON dbo.wrkTestFTIndex
(cFirstName,cLastName,cTitle,vcEmailAddr)
KEY INDEX PK_wrkTestFTIndex
ON [TestFCCatalog]

CREATE FULLTEXT INDEX ON dbo.wrkTestFTIndex
(ccFullName)
KEY INDEX PK_wrkTestFTIndex
ON [TestFCCatalog]

The FT Index on ccFullName the persisted calculated column seems to return
better results than the other one (example below: Gary Walker is returned
with the highest ranking). Am I doing something wrong querying the other
index?

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[wrkTestFTIndex](
[intFaclNbr] [int] NOT NULL,
[cFirstName] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cLastName] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cTitle] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[vcEmailAddr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FCidentity] [int] IDENTITY(1,1) NOT NULL,
[ccFullName] AS ((((((rtrim(isnull([cFirstname],''))+'
')+rtrim(isnull([cLastName],'')))+' ')+rtrim(isnull([cTitle],'')))+'
')+rtrim(isnull([vcEmailAddr],''))) PERSISTED,

CONSTRAINT [PK_wrkTestFTIndex] PRIMARY KEY CLUSTERED
( [FCidentity] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

CREATE FULLTEXT CATALOG [TestFCCatalog]
ON FILEGROUP [FTIndex]
IN PATH 'd:\sql2000ftindex'
AS DEFAULT

Select Rank,fc.* from wrkTestFTIndex fc inner join
FREETEXTTABLE(wrkTestFTIndex, *,'Gary Walker') AS KEY_TBL
ON fc.FCidentity = KEY_TBL.[KEY]
order by Rank DESC


[quoted text, click to view]