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
>
>

Index Computed Column?

Can I create an index on a variation of a column that isn't actually in
the table?

I have a ParcelNumber column, with values like

123 AB-670
12345ABC 000-00-040
12-345-67
AP34567890

The blanks and non-alphanumeric characters cause problems with users,
because sometimes they're there, and sometimes they aren't. So I would
like to create an index based on this column, with the non-alphanumeric
characters squeezed out. Of course I can add such a column to the
table and index it, but I'm wondering if it can be done without
actually adding the column.

Thanks,
JimSure, google up "indexes on computed columns"|||(jim_geissman@.countrywide.com) writes:

Quote:

Originally Posted by

Can I create an index on a variation of a column that isn't actually in
the table?
>
I have a ParcelNumber column, with values like
>
123 AB-670
12345ABC 000-00-040
12-345-67
AP34567890
>
The blanks and non-alphanumeric characters cause problems with users,
because sometimes they're there, and sometimes they aren't. So I would
like to create an index based on this column, with the non-alphanumeric
characters squeezed out. Of course I can add such a column to the
table and index it, but I'm wondering if it can be done without
actually adding the column.

Yes, you can, provided that your computation is deterministic.

First define the computed column:

ALTER TABLE tbl ADD computedcol AS <expression>

Then just create an index on the column. You will be told if the column
is not good for this. Reading the topic on CREATE INDEX is a good idea.

In SQL 2005 you can add PERSISTED after the column definition. This
permits you to persist a computed column without indexing it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks, Alexander and Erland.

Jim|||Oops, I forgot the follow-up...

If I were to create such a computed column index based on the column
with the non-alphanumerics squeezed out, and were to query against
@.variable that also had the non-alphanumerics squeezed out, I suppose I
would need to use an index hint to ensure that index was utilised?

jim_geissman@.countrywide.com wrote:

Quote:

Originally Posted by

Thanks, Alexander and Erland.
>
Jim

|||(jim_geissman@.countrywide.com) writes:

Quote:

Originally Posted by

Oops, I forgot the follow-up...
>
If I were to create such a computed column index based on the column
with the non-alphanumerics squeezed out, and were to query against
@.variable that also had the non-alphanumerics squeezed out, I suppose I
would need to use an index hint to ensure that index was utilised?

No, you should not have to. But you have to ensure that a number
of SET commands are in the right position. Most important, beware of
SET ARITHABORT which must be ON for SQL 2000, but which is never ON
by default, except in Query Analyzer.

-
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Jim,

In addition to Erland's remark, note that the settings during a stored
procedure's execution are the settings in effect during its creation:

SET ANSI_NULLS OFF
GO

CREATE PROCEDURE T1
@.C1 CHAR(1), @.C2 CHAR(1)
AS
SELECT CASE WHEN @.C1 = @.C2 THEN 'Inside SP: Equal' ELSE 'Inside SP: Not
Equal' END
GO

SET ANSI_NULLS ON
GO

GO

DECLARE @.C1 CHAR(1), @.C2 CHAR(1)
SELECT CASE WHEN @.C1 = @.C2 THEN 'Outside SP: Equal' ELSE 'Outside SP:
Not Equal' END

EXEC T1 @.c1, @.c2
GO

DROP PROCEDURE T1
GO

-------
Outside SP: Not Equal

(1 row(s) affected)

-------
Inside SP: Equal

(1 row(s) affected)