'CONTAINS cannot be used in a JOIN clause when using a FREE TEXT Index?
I am looking at tables like this (I did not design them):
--drop table person
CREATE TABLE EventIDsToSearch (id int not null)
insert into EventIDsToSearch values (1)
insert into EventIDsToSearch values (92)
insert into EventIDsToSearch values (106)
--etc
CREATE TABLE Person (ID INT IDENTITY NOT NULL, [type] int, Notes nvarchar(1000), CONSTRAINT [PersonPK] PRIMARY KEY CLUSTERED (ID,[TYPE]))
insert into Person ([type], notes) values (1, 'This person is linked to Event ID 92')
insert into Person ([type], notes) values (2, 'Look at ID 67!')
insert into Person ([type], notes) values (3, 'ID 87(3/10/15)')
insert into Person ([type], notes) values (4, '!!!187(this is the event id)')--Notice the exclamation marks at the start - I don't believe WHERE CONTAINS (Notes,67) would find this.
--etc
I have tried this:
CREATE UNIQUE INDEX PersonIndex ON Person(ID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON Person(Notes)
KEY INDEX PersonIndex
WITH STOPLIST = SYSTEM;
and I can find all the IDs except 187. For example this finds one row:
SELECT *
FROM Person
WHERE CONTAINS(Notes,'92') ;
but this does not:
SELECT *
FROM Person
WHERE CONTAINS(Notes,'187') ;
I don't think a free text index is suitable for me here because:
- Entity.Notes sometimes contains characters e.g. exclamation marks before the ID.
- EventIDsToSearch contains millions of rows and it would be too slow to loop through them in a cursor. This question tells me that a cursor is the only option i.e. EventIDsToSearch and Person cannot be joined by a CONTAINS clause: Using JOIN statement with CONTAINS function
I am using SQL Server 2019.
Have I understood this correctly?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
