Index on 4 columns does not return results in expected order

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?
Kenny wrote on Thu, 8 Feb 2007 09:14:04 -0800:

> We have a Full Text Index on 4 columns and it doesnt 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 dont 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?
>
Are you using the RANK value to sort them? If you have no ORDER BY clause in
your query, the row ordering is always indeterminate. If you use an ORDER BY
clause you should always get them in the same order (unless use RANK to
order them and the RANK calculations are different in 2000 and 2005, Hilary
would probably be able to tell you if this is the case).
Dan
|||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
"Daniel Crichton" wrote:

> Kenny wrote on Thu, 8 Feb 2007 09:14:04 -0800:
>
> Are you using the RANK value to sort them? If you have no ORDER BY clause in
> your query, the row ordering is always indeterminate. If you use an ORDER BY
> clause you should always get them in the same order (unless use RANK to
> order them and the RANK calculations are different in 2000 and 2005, Hilary
> would probably be able to tell you if this is the case).
> Dan
>
>