Multi column primary keys

I have used full-text indexing on some of my past jobs and just recently convinced my supervisor to try it out. I had forgotten that one can only index single column primary key tables. At my current work environment we have several identical databases on
several servers. Those databases are being consolidated into another database via dts. For this reason multi-column PK's had to be set up to avoid duplication.
To rewrite the application and redesign the database is not an option. Is there a workaround to be able to index tables with multiple column PK's or do I have to give up on trying to use the FTI feature?
Ralph Schwehr
Programmer / Analyst
can you add another int column to the tables you want to FTI which has a
unique index on it?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Ralph Schwehr" <RalphSchwehr@.discussions.microsoft.com> wrote in message
news:76928EFC-6552-4AAC-9869-95DD1BAC21BF@.microsoft.com...
> I have used full-text indexing on some of my past jobs and just recently
convinced my supervisor to try it out. I had forgotten that one can only
index single column primary key tables. At my current work environment we
have several identical databases on several servers. Those databases are
being consolidated into another database via dts. For this reason
multi-column PK's had to be set up to avoid duplication.
> To rewrite the application and redesign the database is not an option. Is
there a workaround to be able to index tables with multiple column PK's or
do I have to give up on trying to use the FTI feature?
> --
> Ralph Schwehr
> Programmer / Analyst
|||Thanks Hilary,
that is what I did, although this could be propblematic when consolidating the databases into one. But I think we will just not use this new key in the consolidated database.
Ralph Schwehr
Programmer / Analyst
"Hilary Cotter" wrote:

> can you add another int column to the tables you want to FTI which has a
> unique index on it?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Ralph Schwehr" <RalphSchwehr@.discussions.microsoft.com> wrote in message
> news:76928EFC-6552-4AAC-9869-95DD1BAC21BF@.microsoft.com...
> convinced my supervisor to try it out. I had forgotten that one can only
> index single column primary key tables. At my current work environment we
> have several identical databases on several servers. Those databases are
> being consolidated into another database via dts. For this reason
> multi-column PK's had to be set up to avoid duplication.
> there a workaround to be able to index tables with multiple column PK's or
> do I have to give up on trying to use the FTI feature?
>
>

Multi Column full-text search question

I would like to select rows containing some text which is more than one word.
The problem is that the text can appear in different colums, meaning, one
word in this column and another in that. moreove, I need to select only the
rows containing all the words.
The fulltext search is on one table only (multi columns)
The text size in words is variable
Can someone help?
Thanks in advance
This is a difficult question. Basically Contains will only see the words in
your search phrase if they occur in the same column, no matter what type of
search you are doing; single column, multiple column, or all columns.
For FreeText the hit will come if the word or one of the generations of the
word occurs in any of the columns.
create database multicolumn
go
use multicolumn
go
sp_fulltext_database 'enable'
go
create table fulltexttable
(pk int not null identity constraint fulltexttablepk primary key,
charcol1 char(30),
charcol2 char(30),
charcol3 char(30))
GO
insert into fulltexttable(charcol1, charcol2) values('james','bond')
insert into fulltexttable(charcol1, charcol2) values('james bond','mike')
GO
create fulltext catalog test as default
GO
create fulltext index on fulltexttable(charcol1, charcol2)
key index fulltexttablepk
GO
select * from containstable(fulltexttable,*,'"james bond"') -- 1 row, 2nd
one
select * from containstable(fulltexttable,charcol1,'"james bond"') -- 1 row,
2nd one
select * from containstable(fulltexttable,(charcol1,charcol2),'" james
bond"') -- 1 row, 2nd one
select * from freetexttable(fulltexttable,*,'james bond') -- 2 rows, 1 and 2
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dedi" <dedi@.discussions.microsoft.com> wrote in message
news:CFE85410-5AFF-4F84-B376-3E864DFDB623@.microsoft.com...
>I would like to select rows containing some text which is more than one
>word.
> The problem is that the text can appear in different colums, meaning, one
> word in this column and another in that. moreove, I need to select only
> the
> rows containing all the words.
> The fulltext search is on one table only (multi columns)
> The text size in words is variable
> Can someone help?
> Thanks in advance
sql